Hi,
I have a query using a view such as:
SELECT *
FROM taskretrievalwithfiles
WHERE ti_id='ti_000000000000000000000000000000000r0w2';
The view taskretrievalwithfiles is defined as:
SELECT taskinstance.ti_id, lotsofotherstuff
FROM taskinstance
JOIN store ON taskinstance.s_id=store.s_id
JOIN storegroup ON store.sg_id=storegroup.sg_id
JOIN tasktemplate ON taskinstance.tt_id=tasktemplate.tt_id
JOIN taskclass ON tasktemplate.tc_id=taskclass.tc_id
LEFT OUTER JOIN process ON (taskinstance.p_id=process.p_id)
LEFT OUTER JOIN genericprocess ON (process.gp_id=genericprocess.gp_id)
LEFT OUTER JOIN filetaskinstancelookup ON
taskinstance.ti_id=filetaskinstancelookup.ti_id
;
A partial EXPLAIN shows a sequential scan on both "filetaskinstance" and
"filereference"
-> Hash Join (cost=42219.70..140099.85 rows=734905 width=853)
Hash Cond: ((filetaskinstance.fr_name)::text =
(filereference.fr_name)::text)
-> Seq Scan on filetaskinstance (cost=0.00..35377.05 rows=734905
width=136)
-> Hash (cost=20772.31..20772.31 rows=188031 width=808)
-> Seq Scan on filereference (cost=0.00..20772.31
rows=188031 width=808)
Now, if I simply replace the original FROM clause with the view definition
(i.e., SELECT * FROM (SELECT ... ) alias WHERE ) I get:
-> Hash (cost=25.32..25.32 rows=2 width=853)
-> Nested Loop (cost=0.00..25.32 rows=2 width=853)
-> Index Scan using idx_fti_ti_id on filetaskinstance
(cost=0.00..8.53 rows=2 width=136)
Index Cond: ((ti_id)::text =
'ti_000000000000000000000000000000000r0w2'::text)
-> Index Scan using filereference_pkey on filereference
(cost=0.00..8.38 rows=1 width=808)
Index Cond: ((filereference.fr_name)::text =
(filetaskinstance.fr_name)::text)
I now have index scans on both "filetaskinstance" and "filereference" - but
all I appeared to do is the same as what rule re-writing should have done.
I can provide additional information but figured I'd start here.
Running 9.0.3; seeing the behavior on both 64bit versions (Ubuntu and
Windows 7). Using the same data on 8.2 the query using the view does using
the indexes as expected.
All query related settings have been left as defaults:
from_collapse_limit = 8
geqo_threshold = 12
join_collapse_limit = 8
The query plans up to the sections listed are identical. Basically, all the
INNER JOINs and the "process" LEFT OUTER JOIN are using indexes/seqscan as
expected but the last two LEFT OUTER JOINS are switching between seqscan and
index depending on whether the SELECT query is referenced via a view or if
it is embedded directly into the outer "WHERE clause" query. Note that
"filetaskinstancelookup" itself is a view (it is where the filereference
table gets joined).
One additional note: I noticed as I was doing this that the first explain
was checking a condition: ((filetaskinstance.fr_name)::text =
(filereference.fr_name)::text); but the filetaskinstance.fr_name column (a
foreign key) had not been indexed. I added the index thinking the first
query may choose to use that index (on fr_name instead of ti_id) but it did
not - it still wanted to do the sequential scan.
I appreciate any help or direction that can be provided.
David J.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
