Re: [Gnumed-devel] enable_seqscan

[Syan Tan]

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.

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 ).  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)



On Sun Mar 12 17:00 , Karsten Hilbert sent:

> On Sun, Mar 12, 2006 at 02:16:50PM +0800, Syan Tan wrote:
>
> > I did try that , but in postgresql.conf and it didn't seem to work. perhaps if I
> > insert set enable_seqscan=off in the session , it will be the same, in which case , you
> > do not have to change the sql. I'll verify it saves time as well.
> Yes, please do. Note, however, that this is a better fix
> (because it is closer to the real solution) but far from the
> proper one.
>
> One thing you should definitely try, too, is to run "vacuum
> analyze" on the database after insertion of your large test
> dataset.
>
> See, the real reason (unless there is a bug) why the query
> planner chooses a seq scan over an index scan is that the
> planner thinks the seq scan is going to be faster ! Which is
> very true in the initial database state where most tables
> are nearly empty. The planner uses table statistics to
> decide whether seq or idx scans should be better. These
> statistics need to be updated after significant changes to
> table data. Else the planner will work with false
> assumptions leading it to choose seq scans over index scans.
>
> I do believe updating the statistics will make the planner
> use the indexes without any need for changing the sql or
> disallowing seq scans entirely...
>
> The test I did with "set enable_seqscan to off" was simply to
> prove that the query planner technically *can* use indexes
> on child tables.
>
> Karsten
> -- 
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> _______________________________________________
> Gnumed-devel mailing list
> address@hidden
> http://lists.gnu.org/mailman/listinfo/gnumed-devel

example.out
Description: Binary data