Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On Fri, 3 Mar 2023 at 22:35, cen <cen(dot)is(dot)imba(at)gmail(dot)com> wrote:
> Does equivalency only work for constants as in the sample you provided
> or will it also be found in b1."number" and t0."block_number" in my
> sample query?

It works for more than constants, but in this case, it's the presence
of the constant that would allow the qual to be pushed down into the
scan level of the other relation.

For cases such as t1 INNER JOIN t2 ON t1.x = t2.y INNER JOIN t3 ON
t2.y = t3.z the equivalence classes could allow t1 to be joined to t3
using t1.x = t3.z before t2 is joined in, so certainly it still does
things with classes not containing constants. No derived quals will
get pushed down to the scan level without constants, however.

> Meaning the columns could be used interchangeably in all the WHERE
> clauses and the ORDER clause, then it is a matter of figuring out what
> costs less.

The problem is you have: AND (t0."block_number" >= 30926000) AND
(t0."block_number" <= 31957494). The >= and <= operators are not used
to help build the equivalence class. You'd see a very different plan
had you just been asking for block_number = 30926000.

I think your best solution will be to just also include the seemingly
surplus: AND (b1."number" >= 30926000) AND (b1."number" <= 31957494)
quals.

David