Sequencial scan instead of using index

From: Harry Hehl
Subject: Sequencial scan instead of using index
Date: ,
Msg-id: E06DEE0DEBD67F41B35E8988D438079BF2127C@EXCHSRV.waterloonetworking.net
(view: Whole thread, Raw)
Responses: Re: Sequencial scan instead of using index  (Ragnar)
Re: Sequencial scan instead of using index  (Mark Kirkwood)
List: pgsql-performance

Tree view

Sequencial scan instead of using index  ("Harry Hehl", )
 Re: Sequencial scan instead of using index  (Ragnar, )
 Re: Sequencial scan instead of using index  (Mark Kirkwood, )
 Re: Sequencial scan instead of using index  (, )
  Re: Sequencial scan instead of using index  (, )
 Re: Sequencial scan instead of using index  ("Harry Hehl", )
  Re: Sequencial scan instead of using index  (Tom Lane, )
 Re: Sequencial scan instead of using index  ("Harry Hehl", )

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.

I ran test adding records to ommemberrelation and omfilesysentry up to 32K in each to simulate and measured query
times. The graph is O(n²) like.  i.e sequencial scan   

The columns in the where clauses are indexed, and yes I did VACUUM ANALYZE FULL. I even tried backup restore of the
entiredb. No difference.  

Turning sequencial scan off results in a O(n log n) like graph,

Explain analyze confirms sequencial scan. A majority (70ms) of the 91ms query is as a result of  ->  Seq Scan on
ommemberrelationTiming is on. 
                                                                                      QUERY PLAN
                                                              

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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)
         ->  Append  (cost=0.00..486.06 rows=129 width=16) (actual time=0.063..1.419 rows=26 loops=1)
               ->  Index Scan using omfilesysentry_name_idx on omfilesysentry  (cost=0.00..8.30 rows=2 width=16)
(actualtime=0.019..0.019 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omfile_name_idx on omfile omfilesysentry  (cost=0.00..393.85 rows=101 width=16)
(actualtime=0.033..0.291 rows=26 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Seq Scan on omdirectory omfilesysentry  (cost=0.00..24.77 rows=11 width=16) (actual
time=0.831..0.831rows=0 loops=1) 
                     Filter: (name = 'dir15_file80'::text)
               ->  Index Scan using omfilesequence_name_idx on omfilesequence omfilesysentry  (cost=0.00..8.30 rows=2
width=16)(actual time=0.014..0.014 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omclipfile_name_idx on omclipfile omfilesysentry  (cost=0.00..8.30 rows=2 width=16)
(actualtime=0.008..0.008 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omimagefile_name_idx on omimagefile omfilesysentry  (cost=0.00..8.30 rows=2
width=16)(actual time=0.008..0.008 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omcollection_name_idx on omcollection omfilesysentry  (cost=0.00..8.30 rows=2
width=16)(actual time=0.008..0.008 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omhomedirectory_name_idx on omhomedirectory omfilesysentry  (cost=0.00..8.30 rows=2
width=16)(actual time=0.007..0.007 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Seq Scan on omrootdirectory omfilesysentry  (cost=0.00..1.05 rows=1 width=16) (actual
time=0.013..0.013rows=0 loops=1) 
                     Filter: (name = 'dir15_file80'::text)
               ->  Index Scan using omwarehousedirectory_name_idx on omwarehousedirectory omfilesysentry
(cost=0.00..8.30rows=2 width=16) (actual time=0.007..0.007 rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)
               ->  Index Scan using omtask_name_idx on omtask omfilesysentry  (cost=0.00..8.30 rows=2 width=16) (actual
time=0.009..0.009rows=0 loops=1) 
                     Index Cond: (name = 'dir15_file80'::text)  Total runtime: 91.019 ms
(29 rows)

So why is the planner not using the index?  Everything I have read indicates sequencial scanning should be left on and
theplanner should do the right thing.  

This is a quote from 1 web site:

"These options are pretty much only for use in query testing; frequently one sets "enable_seqscan = false" in order to
determineif the planner is unnecessarily discarding an index, for example. However, it would require very unusual
circumstancesto change any of them to false in the .conf file." 

So how do I determine why the planner is unnecessarily discarding the index?

Thanks





pgsql-performance by date:

From: "i.v.r."
Date:
Subject: Re: Help understanding indexes, explain, and optimizing
From: Michael Fuhr
Date:
Subject: Re: Can anyone explain this pgbench results?