Re: Poor plan when joining against a union containing a join - Mailing list pgsql-performance

On 7 March 2013 05:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> On 03/06/2013 06:54 AM, David Leverton wrote:
>>> I'm encountering very poor query plans when joining against a union,
>
>> Actually, in case #4, Postgres *is* pushing down the join qual into the
>> segments of the Union.
>
> Yeah, but not further.  I believe the core issue here (as of 9.2) is
> that we're not willing to generate parameterized paths for subquery
> relations.  We could do that without a huge amount of new code,
> I think, but the scary thing is how much time it might take to generate
> (and then discard most of the) plans for assorted parameterizations of
> complicated subqueries.

Thanks for looking at this, both of you.

Does "as of 9.2" mean it's better in 9.3?  I do intend to upgrade once
it's released, so if it can handle this better (or if there's anything
that can be done to improve it between now and then without making
other things worse) that would be great.  Otherwise, I'm wondering if
the addition of LATERAL will help persuade the planner to do what I
want, something like this, perhaps? (please excuse any syntax
misunderstandings):

    SELECT *
    FROM item_reference,
        LATERAL (
            SELECT *
            FROM item
            WHERE (item.item_id_a, item.item_id_b)
                = (item_reference.item_id_a, item_reference.item_id_b)
        ) item
    WHERE reference_id = 1;

I'm hoping this might help as the query in the test case where the
desired item_id_a and item_id_b were supplied literally rather than
from a join was fast, and this version has a similar structure,
although naturally it'll only work if the planner doesn't notice that
it's really equivalent to the slow version and treat it the same way.

If not though, and in the meantime in any case, I suppose I'm looking
for a workaround.  In the real application the queries involved are
generated by code rather than hand-written, so it's not a disaster if
they have to be uglified a bit more than they are already.  I'll see
if I can figure something out, but if anyone has any suggestions they
would be much appreciated.

I'm afraid I don't really see how Josh's outer join suggestion would
help here, though, unless it was more of a general principle than
something specific to this case.  The two branches of the union don't
have any tables in common, so I don't see what I could be joining to.

Ideally any alternative would keep the semantics the same as the
existing version, or at least as similar as possible, as the
application does need (or at least very much wants) to be able to work
with items, including using them in further joins, without caring
whether they're loose or part of a bundle.  (And yes, it is a rather
scary design in places, but it's the best thing I could come up with
to achieve the requirements.  Not sure if that says more about the
requirements or me....)