Re: WHERE condition not being pushed down to union parts

On Tue, Apr 21, 2009 at 3:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ah.  The problem is that your view contains constants in the UNION arms:

> In 8.2 and 8.3, the planner is only smart enough to generate
> inner-indexscan nestloop plans on UNIONs if all the elements of the
> SELECT lists are simple variables (that is, table columns).
> 8.4 will be smarter about this.

Ah, and so it is!  I installed 8.4beta1 and have loaded it with the
big database; it is pushing the index condition down to the parts of
the UNION, and my queries are now running MUCH faster.  Here's the new
query plan for the query involving the UNION-constructed view:

<query-plan>

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..53.32 rows=1083 width=80)
   Join Filter: (component_0_statements.subject = literalproperties.subject)
   ->  Index Scan using relations_poscindex on relations
component_0_statements  (cost=0.00..13.97 rows=2 width=40)
         Index Cond: ((predicate = (-2875059751320018987)::bigint) AND
(object = (-2827607394936393903)::bigint))
   ->  Append  (cost=0.00..19.65 rows=2 width=60)
         ->  Index Scan using literalproperties_subjectindex on
literalproperties  (cost=0.00..10.05 rows=1 width=57)
               Index Cond: (literalproperties.subject =
component_0_statements.subject)
               Filter: (literalproperties.predicate =
(-2875059751320018987)::bigint)
         ->  Index Scan using relations_subjectindex on relations
(cost=0.00..9.59 rows=1 width=64)
               Index Cond: (relations.subject = component_0_statements.subject)
               Filter: (relations.predicate = (-2875059751320018987)::bigint)
(11 rows)
</query-plan>

Thanks for your help, Tom.  I am certainly amused and pleased that my
exact use case is handled in the very next PostgreSQL release.

Take care,

    John L. Clark