[Top][All Lists]

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

Re: [Gnumed-devel] enable_seqscan

From: Syan Tan
Subject: Re: [Gnumed-devel] enable_seqscan
Date: Mon, 13 Mar 2006 14:39:17 +0800

I've run vacuum analyze too, that didn't help.  The weird thing is that if selecting on

pk_episode,  it runs with index_scan, but if selecting on pk_health_issue, it won't

(this is for clin.v_pat_items).  still trying to figure out why. I'm suspecting still

it's something to do with the indirection of an extra join.

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

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.

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

Gnumed-devel mailing list

reply via email to

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