Thread: Index Ignored Due To Use Of View

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.

"David Johnston" <polobo@yahoo.com> writes:
> Now, if I simply replace the original FROM clause with the view definition
> (i.e., SELECT * FROM (SELECT ... ) alias WHERE ) I get:
> [ a different plan ]
> 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.

If you really just manually plugged in the view definition, then yeah
you should have gotten the same results.  Could we see a complete test
case?

            regards, tom lane

I cannot get the problem to appear using generated data and cannot provide
the real database.

Given these facts, and the fact I can use the "expanded" query to get the
necessary results, I am going to move on.

If you want me to provide any additional information with respect to this
behavior using my live data just let me know and I'll try and do what I can.

BTW, for the test case I used all the same tables that are referenced in the
two views but with many of the non-key attributes removed.  I then loaded
10,000+ records into the relevant tables and ran the explains.

David J.


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, February 25, 2011 12:33 AM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index Ignored Due To Use Of View

"David Johnston" <polobo@yahoo.com> writes:
> Now, if I simply replace the original FROM clause with the view
> definition (i.e., SELECT * FROM (SELECT ... ) alias WHERE ) I get:
> [ a different plan ]
> 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.

If you really just manually plugged in the view definition, then yeah you
should have gotten the same results.  Could we see a complete test case?

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general