Re: Poor plan when joining against a union containing a join

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