[Top][All Lists]

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

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

From: Karsten Hilbert
Subject: Re: [Gnumed-devel] ? top down faster for emr browser
Date: Mon, 1 May 2006 00:07:20 +0200
User-agent: Mutt/1.5.11+cvs20060403

On Sun, Apr 30, 2006 at 04:43:08PM +0800, Syan Tan wrote:

> 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 ;
Which is a scenario we *must* be able to handle with very
acceptable response times.

> 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
There will be other readers, too, during normal GNUmed use.

> narratives in a duplicate database. ( this
> takes a long time, about 12 hours or more for  360,000 rows). 
Thanks for hammering the schema. Is most of this time spent
reading/updating the database ? Does it use a lot of new

>  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.
That would be due to caching, both query level result
caching (PostgreSQL) and table level disk data caching (OS).

> 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)
I would present this case to the pgsql performance list as I
am not convinced it is a query issue (unless it really is a
bug which does not show up when there are only a few rows).

> So maybe if there is enough memory on a server, it doesn't
> make much difference what the query is.
That is definitely true. It also requires, however,
PostgreSQL to be configured appropriately (postgresql.conf
-> work_mem, sort_mem and friends).

GPG key ID E4071346 @
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

reply via email to

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