Re: Sequencial scan instead of using index

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