Re: Sequencial scan instead of using index - Mailing list pgsql-performance

From Ragnar
Subject Re: Sequencial scan instead of using index
Date
Msg-id 1141678306.18656.152.camel@localhost.localdomain
Whole thread Raw
In response to Sequencial scan instead of using index  ("Harry Hehl" <Harry.Hehl@diskstream.com>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: "Harry Hehl"
Date:
Subject: Sequencial scan instead of using index
Next
From: Guido Neitzer
Date:
Subject: Re: t1000/t2000 sun-servers