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: (view raw or whole 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:


 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 =
               Filter: (literalproperties.predicate =
         ->  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)

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


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-2015 The PostgreSQL Global Development Group