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

[Syan Tan]

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

testtopdown.sql
Description: Binary data