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