Message-ID: <5137CDFC.1060902@agliodbs.com>
Date: Wed, 06 Mar 2013 15:15:08 -0800
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130221 Thunderbird/17.0.3
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Poor plan when joining against a union containing a
 join
References: 
 <CALc3eMXjdWDMRr+48R2LH9fimKHD4JG9aWHDfqmmG=SQPTBVeA@mail.gmail.com>
In-Reply-To: 
 <CALc3eMXjdWDMRr+48R2LH9fimKHD4JG9aWHDfqmmG=SQPTBVeA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 03/06/2013 06:54 AM, David Leverton wrote:
> Hi all,
> 
> I'm encountering very poor query plans when joining against a union,
> where one branch of the union is itself a join: specifically, Postgres
> does the entire inside join and then filters the result, rather than
> pushing the filters down to the joined tables.  I've provided a
> standalone test case below, including some variations that don't show
> the problem for comparison.  The fourth query is the problematic one -
> I would have expected the Append portion of the plan to be essentially
> the same as the one in the second query.

Thanks for the test case!

Actually, in case #4, Postgres *is* pushing down the join qual into the
segments of the Union.  It's just that that's not helping performance
any; it's causing a really slow join on bundle, which is actually where
you're spending most of your time:

               ->  Hash Join  (cost=27.50..12970.50 rows=1000000
width=8) (actual time=0.617..344.127 rows=1000000 loops=1)
                     Hash Cond: (bundle.bundle_type =
bundle_contents.bundle_type)
                     Buffers: shared hit=448
                     ->  Seq Scan on bundle  (cost=0.00..1443.00
rows=100000 width=8) (actual time=0.009..22.066 rows=100000 loops=1)
                           Buffers: shared hit=443
                     ->  Hash  (cost=15.00..15.00 rows=1000 width=8)
(actual time=0.594..0.594 rows=1000 loops=1)
                           Buckets: 1024  Batches: 1  Memory Usage: 40kB
                           Buffers: shared hit=5
                           ->  Seq Scan on bundle_contents
(cost=0.00..15.00 rows=1000 width=8) (actual time=0.008..0.207
rows=1000 loops=1)
                                 Buffers: shared hit=5

Clearly this is the wrong strategy; Postgres should be letting the
filter on item_reference be the driver instead of hashing the whole
bundle + bundle_contents join.  I suspect that the qual pushdown into
the union is hitting an inability to transverse multiple joins, which
wouldn't surprise me; in fact, I'd be surprised if it could do so.

On a pragmatic basis, joining against complex UNION expressions is
liable to be a bit of a minefield for the next few generations of the
Postgres planner; it's just really hard to optimize.  You might think of
using outer joins instead of a UNION.


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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