Re: Self contradictory examining on rel's baserestrictinfo

Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> On Mon, Nov 25, 2024 at 3:55 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> There are cases where we don't already draw the necessary conclusions,
>> such as a>1 and a>2, which could be simplified to a>2. But those cases
>> aren't necessarily that common.

> Actually, we do use the more restrictive operator with cases like "a>1
> and a>2" -- but only in contexts that happen to involve a B-Tree index
> scan, where _bt_preprocess_keys gets called. So it's a bit hit or
> miss.

Worth noting also is that _bt_preprocess_keys can detect cases that
the planner cannot because the relevant values are not constants.

I'm a little skeptical that we should expend a lot more effort on
the sorts of cases discussed here. Basically, this sort of patch
improves matters for people who write crummy queries while penalizing
everybody else. We need to be very careful about adding cycles to
planner runtime in pursuit of optimizations that are only rarely
successful.

I'm not trying to say that we should reject all of this out-of-hand,
but I want to see some analysis of how much each check costs and
how likely it is to win in real-world queries.

BTW, it's also worth pointing out the constraint_exclusion GUC.
If that's turned up to "on" from its default setting of "partition",
it authorizes the planner to spend more effort looking for
contradictory quals. It might be reasonable to gate some of these
checks with that. (I also can't help wondering if any of them are
already implemented but are hidden behind that. I think Robert
must have had constraint_exclusion = on for his examples a
couple messages back.)

regards, tom lane