Skip site navigation (1) Skip section navigation (2)

Re: WHERE condition not being pushed down to union parts

From: "John L(dot) Clark" <jlc6(at)po(dot)cwru(dot)edu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: WHERE condition not being pushed down to union parts
Date: 2009-04-23 16:09:04
Message-ID: 4fb69e5d0904230909p5c05b32bx94a7979e82a925c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-04-23 16:14:59
Subject: Re: WHERE condition not being pushed down to union parts
Previous:From: Stephen FrostDate: 2009-04-23 11:11:32
Subject: Re: performance for high-volume log insertion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group