On Wed, 15 Feb 2023 at 11:39, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
> OTOH it could also be argued that the optimizer should be able to
> perform the same simplifications as I did above and produce the same
> code for WHERE (("id" > ? OR "id" IS NULL)) AND (("id" <= ?))
> as for WHERE (("id" > ?)) AND (("id" <= ?)).
You're right, and it has been brought up quite a few times in the
past. To make it work, it's a fairly trivial change. We'd just need
to record all the attnotnull columns during something like
get_relation_info() then when adding baserestrictinfos to the base
relations, we could look to see if the qual is a NullTest and skip
that if we deem the qual as constantly true.
The problem with that is that doing that has an above zero cost and
since it likely only applies to nearly zero real-world cases, it just
does not seem like useful cycles to add to the planner. That might be
different if this was some optimisation that there was no other way to
make work, but that's not the case. All you need to do is remove the
redundant null check. In the planner, if we had some other reason to
record which columns are NOT NULL then the additional overhead of just
looking at the NullTest quals would likely be cheap enough to be
worthwhile. I imagine we'd need to find some other reason to record
attnotnull columns before we'd consider doing this.
David
