[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[Gnumed-devel] ? top down faster for emr browser

From: Syan Tan
Subject: [Gnumed-devel] ? top down faster for emr browser
Date: Sun, 30 Apr 2006 16:43:08 +0800

the attached script was used to see what the unix "time" command would return

e.g. time psql -f test.sql gnumed_v3

joe bloggs ( pseudoname) ,  has 150 rows in encounters,150 rows in episodes, 204 in narratives ,

and < 20 rows in health issues ;

there was another process running which read the narratives and was updating them in an unrelated

job of openssl encoding certain words ( e.g. names)  and updating the narratives in a duplicate database. ( this

takes a long time, about 12 hours or more for  360,000 rows). 

 the clin_items  select was used in one run, and the individual clin_item child table selects commented out.

In another run , the clin_items was commented out, and vici versa child table selects.

the initial run time was slow for both ; clin_root_items ranged from 30seconds to 60 seconds,

and the individual child selects was between 10 and 15 seconds.

the second time they ran, the clin_root_items select fell to about 20 seconds, and even got to 12 seconds several times.

 the individual child selects run at about 2.5 seconds , on second running.

 it seems postgresql  does some optimization to make second runnings of a query faster.

after vacuuming, and then closing a few windows and terminals , the clin_root_items can reach 5 seconds.

explain analyse shows that even at 5 seconds, the query is still doing sequential scanning of all child tables

including the 360000 row clin_narrative. (whereas the child tables select is doing index scan)

So maybe if there is enough memory on a server, it doesn't

make much difference what the query is.














Attachment: testtopdown.sql
Description: Binary data

reply via email to

[Prev in Thread] Current Thread [Next in Thread]