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 connections. > 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. interesting > 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). Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 _______________________________________________ Gnumed-devel mailing list [email protected] http://lists.gnu.org/mailman/listinfo/gnumed-devel
