Date: Tue, 07 Mar 2006 18:04:13 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Sequencial scan instead of using index
In-reply-to: 
 <E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net>
To: Harry Hehl <Harry.Hehl@diskstream.com>
Cc: pgsql-performance@postgresql.org
Message-id: <440D144D.9000106@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: 
 <E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net>

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