From: | James Coleman <jtc331(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Teach predtest about IS [NOT] <boolean> proofs |
Date: | 2023-12-11 19:59:46 |
Message-ID: | CAAaqYe8Bo4bf_i6qKj8KBsmHMYXhe3Xt6vOe3OBQnOaf3_XBWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hello,
I recently encountered a case where partial indexes were surprisingly not
being used. The issue is that predtest doesn't understand how boolean
values and IS <boolean> expressions relate.
For example if I have:
create table foo(i int, bar boolean);
create index on foo(i) where bar is true;
then this query:
select * from foo where i = 1 and bar;
doesn't use the partial index.
Attached is a patch that solves that issue. It also teaches predtest about
quite a few more cases involving BooleanTest expressions (e.g., how they
relate to NullTest expressions). One thing I could imagine being an
objection is that not all of these warrant cycles in planning. If that
turns out to be the case there's not a particularly clear line in my mind
about where to draw that line.
As noted in a TODO in the patch itself, I think it may be worth refactoring
the test_predtest module to run the "x, y" case as well as the "y, x" case
with a single call so as to eliminate a lot of repetition in
clause/expression test cases. If reviewers agree that's desirable, then I
could do that as a precursor.
Regards,
James Coleman
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Teach-predtest-about-IS-NOT-bool-proofs.patch | application/octet-stream | 26.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jehan-Guillaume de Rorthais | 2023-12-11 20:09:51 | Sorting regression of text function result since commit 586b98fdf1aae |
Previous Message | Alexander Korotkov | 2023-12-11 18:59:32 | Re: Bug in nbtree optimization to skip > operator comparisons (or < comparisons in backwards scans) |