pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Mark Kirkwood <markir@paradise.net.nz>
To: Harry Hehl <Harry.Hehl@diskstream.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequencial scan instead of using index
Date: Tue, 07 Mar 2006 18:04:13 +1300
Message-ID: <440D144D.9000106@paradise.net.nz> (raw)
In-Reply-To: <E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net>
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

view thread (8+ messages)  latest in thread

Message-ID: <440D144D.9000106@paradise.net.nz>
Permalink:  ../440D144D.9000106@paradise.net.nz/
Also on:    postgresql.org/message-id/440D144D.9000106@paradise.net.nz

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: markir@paradise.net.nz, Harry.Hehl@diskstream.com
  Subject: Re: Sequencial scan instead of using index
  In-Reply-To: <440D144D.9000106@paradise.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox