Re: WHERE condition not being pushed down to union parts

On Tue, Apr 21, 2009 at 3:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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