bt Scankey in another contradictory case

From: "bigbro_wq(at)hotmail(dot)com" <bigbro_wq(at)hotmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: bt Scankey in another contradictory case
Date: 2024-08-29 16:56:23
Message-ID: TYWPR01MB9278A5F8CC56AB507066D25485962@TYWPR01MB9278.jpnprd01.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,
when i read source code of the part of nbtree, i detected another kind of contradictory case postgresql has not eliminated
(eg. x >4 and x <3) in the function _bt_preprocess_keys in nbtutils.c. this cause next unnecessary index scan and qual evaluation.
it seems no one will write SQL like that, but maybe it generated from the ec or degenerate qual.
the function just invoked in function _bt_first during initializing index scan, so it’s cheap to eliminate this case in the function.
we just pay attention on the opposite operator, so there are four detailed cases (ie. x >4 and x <3 , x >4 and x <=3, x >=4 and x <3, x >=4 and x <=3).
when test the case is contradictory or not, it's need to use the more restrictive operator when less and more restrictive operator both appeared,
case like x >4 and x <=4, if we choose <=, 4 <= 4 is satisfied, actually x >4 and x <=4 is contradictory.
when >= with <= or > with <, it's ok to pick any one of them.
i have added this feature in my local environment and tested. but i am not sure it's worth to spend more effort on it.

bigbro_wq(at)hotmail(dot)com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Murawski 2024-08-29 17:01:18 Re: pl/pgperl Patch for adding $_FN detail just like triggers have for $_TD
Previous Message Ayush Vatsa 2024-08-29 16:47:57 Re: Pgstattuple on Sequences: Seeking Community Feedback on Potential Patch