Re: Self contradictory examining on rel's baserestrictinfo

Thank you for your advice and guidance.
I didn't know the constraint_exclusion switch existed.
As your advice i need to make what i am doing to be clear.

> 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.

The path i committed not just test contradictory but also do the
simplification. The simplification is limited in the BTREE.
Could you interpret the case in a little more detail.

Best regards

________________________________
From: Robert Haas <robertmhaas(at)gmail(dot)com>
Sent: Tuesday, November 26, 2024 04:55
To: ro b <bigbro_wq(at)hotmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Self contradictory examining on rel's baserestrictinfo

On Mon, Nov 25, 2024 at 3:58 AM ro b <bigbro_wq(at)hotmail(dot)com> wrote:
> 1. Background
> A few months ago, when i read source codes of B-tree in routine
> _bt_preprocess_keys, i found that there are more contradictory
> checking case we can add. I sent email to pgsql-hackers and
> then community contributor replied me and told me someone had
> already proposed this question. Thanks for taking the time
> to address my question. After serveral conversations, i found
> that we can do something more. We can place these jobs at planning time.

When you're referring to things that happened in the past, you should
provide links to specific messages and names of specific contributors.
It will be difficult for anyone to find the previous discussion based
on your description of "a few months ago" and a "community
contributor".

I'm a little confused because it seems like you think we don't do any
of this kind of thing already. But:

robert.haas=# explain select * from foo where a < 1 and a > 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

robert.haas=# explain select * from foo where a < 1 and a = 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

robert.haas=# explain select * from foo where a <> 1 and a = 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

robert.haas=# explain select * from foo where a in (1,2,3) and a is null;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

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.

> 7) Scalar array comparison expression
> First we need to deconstruct the const array, figure out the null and non-null
> elements.
> If ALL flag is set and the Const contain NULL. we will get nothing (eg. x <=
> ALL(array[56, null])), it's contradictory.

True, but that already seems to be working:

robert.haas=# explain select * from foo where a <= all(array[56, null]);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

I'm not saying there is no room for improvement here, but I think you
will need to (1) be more clear about exactly which cases we are
already handling vs. which ones you want to handle, (2) possibly split
the patch into smaller patches each of which handles one specific case
instead of bundling many improvements together, and (3) improve the
comments and commit message in the patch(es).

--
Robert Haas
EDB: http://www.enterprisedb.com