From: Ragnar <gnari@hive.is>
To: Harry Hehl <Harry.Hehl@diskstream.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequencial scan instead of using index
Date: Mon, 06 Mar 2006 20:51:46 +0000
Message-ID: <1141678306.18656.152.camel@localhost.localdomain> (raw)
In-Reply-To: <E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net>
References: <E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net>
On mán, 2006-03-06 at 13:46 -0500, Harry Hehl wrote:
> 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.
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop IN Join (cost=486.19..101533.99 rows=33989 width=177) (actual time=5.493..90.682 rows=1 loops=1)
> Join Filter: ("outer".dstobj = "inner".objectid)
> -> Seq Scan on ommemberrelation (cost=0.00..2394.72 rows=33989 width=177) (actual time=0.078..70.887 rows=100 loops=1)
> Filter: (srcobj = '3197a4e6-abf1-11da-a0f9-000fb05ab829'::text)
> -> Materialize (cost=486.19..487.48 rows=129 width=16) (actual time=0.004..0.101 rows=26 loops=100)
Looks like the planner is expecting 33989 rows, making
an index scan a ppor choice, but in fact only 100 rows
actually match your srcobj value.
Could we see the explain analyze with enable_seqscan
= false please ?
Possibly you might want totry to increase the statistics
target for this columns , as in:
ALTER TABLE ommemberrelation ALTER COLUMN srcobj
SET STATISTICS 1000;
ANALYZE;
and try again (with enable_seqscan=true)
A target of 1000 ismost probably overkill, but
start with this value, and if it improves matters,
you can experiment with lower settings.
gnari
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: gnari@hive.is, Harry.Hehl@diskstream.com
Subject: Re: Sequencial scan instead of using index
In-Reply-To: <1141678306.18656.152.camel@localhost.localdomain>
* 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