From: | Mark Kirkwood <markir(at)paradise(dot)net(dot)nz> |
---|---|
To: | Harry Hehl <Harry(dot)Hehl(at)diskstream(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Sequencial scan instead of using index |
Date: | 2006-03-07 05:04:13 |
Message-ID: | 440D144D.9000106@paradise.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Harry Hehl wrote:
> There seems to be many posts on this issue but I not yet found an answer to the seq scan issue.
>
> I am having an issue with a joins. I am using 8.0.3 on FC4
>
> Query: select * from ommemberrelation where srcobj='somevalue' and dstobj in (select objectid from omfilesysentry where name='dir15_file80');
>
> Columns srcobj, dstobj & name are all indexed.
>
>
The planner is over-estimating the number of rows here (33989 vs 100):
-> Seq Scan on ommemberrelation (cost=0.00..2394.72 rows=33989
width=177) (actual time=0.078..70.887 rows=100 loops=1)
The usual way to attack this is to up the sample size for ANALYZE:
ALTER TABLE ommemberrelation ALTER COLUMN srcobj SET STATISTICS 100;
ALTER TABLE ommemberrelation ALTER COLUMN dstobj SET STATISTICS 100;
-- or even 1000.
ANALYZE ommemberrelation;
Then try EXPLAIN ANALYZE again.
If you can upgrade to 8.1.(3), then the planner can consider paths that
use *both* the indexes on srcobj and dstobj (which would probably be the
business!).
Cheers
Mark
From | Date | Subject | |
---|---|---|---|
Next Message | Joost Kraaijeveld | 2006-03-07 10:34:18 | Re: Can anyone explain this pgbench results? |
Previous Message | Michael Fuhr | 2006-03-07 03:22:09 | Re: Can anyone explain this pgbench results? |