Re: Missing constant propagation in planner on hash quals causes join slowdown

On Fri, Oct 18, 2019 at 03:40:34PM +0000, Hans Buschmann wrote:
>
> ...
>
>Both queries are logically equivalent. The planner correctly identifies
>the Index Cond: (tfact.t2_season = 3) for selecting from the index
>uk_fact_season_id.
>

Are those queries actually equivalent? I've been repeatedly bitten by
nullability in left join queries, when playing with optimizations like
this, so maybe this is one of such cases?

This seems to be happening because distribute_qual_to_rels() does this:

...
else if (bms_overlap(relids, outerjoin_nonnullable))
{
/*
* The qual is attached to an outer join and mentions (some of the)
* rels on the nonnullable side, so it's not degenerate.
*
* We can't use such a clause to deduce equivalence (the left and
* right sides might be unequal above the join because one of them has
* gone to NULL) ... but we might be able to use it for more limited
* deductions, if it is mergejoinable. So consider adding it to the
* lists of set-aside outer-join clauses.
*/
is_pushed_down = false;
...
}
...

and the clause does indeed reference the nullable side of the join,
preventing us from marking the clause as pushed-down.

I haven't managed to construct a query that would break this, though.
I.e. a case where the two queries would give different results. So maybe
those queries actually are redundant. Or maybe the example would need to
be more complicated (requiring more joins, or something like that).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services