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 entire db. 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 ommemberrelation
Timing 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) (actual time=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) (actual time=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.831 rows=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) (actual time=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.013 rows=0 loops=1)
Filter: (name = 'dir15_file80'::text)
-> Index Scan using omwarehousedirectory_name_idx on
omwarehousedirectory 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)
-> Index Scan using omtask_name_idx on omtask omfilesysentry
(cost=0.00..8.30 rows=2 width=16) (actual time=0.009..0.009 rows=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 the planner 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 determine if the planner is
unnecessarily discarding an index, for example. However, it would require very
unusual circumstances to change any of them to false in the .conf file."
So how do I determine why the planner is unnecessarily discarding the index?
Thanks
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
