Re: BUG #18344: Pruning tables partitioned by bool range fails with invalid strategy

Hi David,

On Mon, Feb 19, 2024 at 8:49 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Mon, 19 Feb 2024 at 05:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > > We can do the same for BooleanTests. Given a clause such as: "partkey
> > > IS NOT false", we can just generate the clause "partkey IS true OR
> > > partkey IS NULL" and recursively generate steps for that.
> >
> > +1 ... sounds clean and clearly correct.
>
> Here's a more complete patch for this.

Thanks for working on this.

Overall, I too like this idea.

Though I noticed that this approach will effectively disable pruning
with a clause on the 2nd key column, if any, present in the query:

CREATE TABLE t (b bool, i int) PARTITION BY RANGE (b, i);
CREATE TABLE tp PARTITION OF t FOR VALUES FROM (false, 0) TO (false, 1);
CREATE TABLE tp2 PARTITION OF t FOR VALUES FROM (false, 1) TO (false, 2);
CREATE TABLE tp3 PARTITION OF t FOR VALUES FROM (true, 0) TO (true, 1);
CREATE TABLE tp4 PARTITION OF t FOR VALUES FROM (true, 1) TO (true, 2);

-- tp2 should be pruned, but is not.
explain SELECT * FROM t WHERE b IS NOT true and i = 0;
QUERY PLAN
--------------------------------------------------------------
Append (cost=0.00..81.81 rows=12 width=5)
-> Seq Scan on tp t_1 (cost=0.00..40.88 rows=6 width=5)
Filter: ((b IS NOT TRUE) AND (i = 0))
-> Seq Scan on tp2 t_2 (cost=0.00..40.88 rows=6 width=5)
Filter: ((b IS NOT TRUE) AND (i = 0))
(5 rows)

-- like it is in this case
explain SELECT * FROM t WHERE b IS false and i = 0;
QUERY PLAN
-----------------------------------------------------
Seq Scan on tp t (cost=0.00..40.88 rows=6 width=5)
Filter: ((b IS FALSE) AND (i = 0))
(2 rows)

I guess we'll have to live with that, because the generate_opsteps
code that generates multi-column pruning steps only supports scenarios
where each key's matched clause is a simple comparison, not, for
example, where it is an OR expression.

--
Thanks, Amit Langote