Re: [Gnumed-devel] enable_seqscan

[Karsten Hilbert]

On Mon, Mar 13, 2006 at 03:51:19PM +0800, Syan Tan wrote:

> here's the output of 2 fairly similiar queries, except one will use index_scan
> and one will use seq_scan.  The seq_scan takes about 800-900 msec, and is 
> called about
> 40 times when constructing a large emr browse tree , so it takes about 30
> seconds.
One thing we need to improve in the emr browse tree data
retrieval code is that the query gets called 40 times. It shouldn't.

> when the problem sql statement is broken into the union select of it's child
> tables, index scan is used by the query planner,  and the statement takes 
> about 1- 2
> msec, making the total  time taken for this statement less than half a second 
> ( if
> say 10msec, then 40 x 10 is 400msec ).
This is acceptable.

> I can't see why the query planner is taking much
> longer with pk_health_issue than pk_episode, except that pk_episode happens 
> to be a
> primary key of clin.episode , one of the base tables of clin.v_pat_items, 
> whereas
> pk_health_issue is a foreign key ( and also the index is not unique in 
> pk_health_issue)
Well, this *could* make a difference but should not make a *large* one.

Syan, please run the attached script like so:

        psql -d gnumed_v2 -U gm-dbo -f testing.sql &> testing.log

and post the output. It works as expected on my machine with
very little data. Wondering how it fares with large data
sets. Please make sure to rebootstrap to get the benefit of
the removed "order by" in clin.v_pat_items.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346