Re: Boolean partitions syntax

> On Dec 12, 2017, at 10:32 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>
> On 2017/12/12 15:39, Amit Langote wrote:
>> On 2017/12/12 15:35, Amit Langote wrote:
>>> Works for me, updated patch attached.
>>
>> Oops, attached the old one with the last email.
>>
>> Updated one really attached this time.
>
> Added to CF: https://commitfest.postgresql.org/16/1410/

This compiles and passes the regression tests for me.

I extended your test a bit to check whether partitions over booleans are useful.
Note specifically the 'explain' output, which does not seem to restrict the scan
to just the relevant partitions. You could easily argue that this is beyond the scope
of your patch (and therefore not your problem), but I doubt it makes much sense
to have boolean partitions without planner support for skipping partitions like is
done for tables partitioned over other data types.

mark

-- boolean partitions
create table boolspart (a bool, b text) partition by list (a);
create table boolspart_t partition of boolspart for values in (true);
create table boolspart_f partition of boolspart for values in (false);
\d+ boolspart
Table "public.boolspart"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | boolean | | | | plain | |
b | text | | | | extended | |
Partition key: LIST (a)
Partitions: boolspart_f FOR VALUES IN (false),
boolspart_t FOR VALUES IN (true)

insert into boolspart (a, b) values (false, 'false');
insert into boolspart (a, b) values (true, 'true');
explain select * from boolspart where a is true;
QUERY PLAN
---------------------------------------------------------------------
Append (cost=0.00..46.60 rows=1330 width=33)
-> Seq Scan on boolspart_f (cost=0.00..23.30 rows=665 width=33)
Filter: (a IS TRUE)
-> Seq Scan on boolspart_t (cost=0.00..23.30 rows=665 width=33)
Filter: (a IS TRUE)
(5 rows)

explain select * from boolspart where a is false;
QUERY PLAN
---------------------------------------------------------------------
Append (cost=0.00..46.60 rows=1330 width=33)
-> Seq Scan on boolspart_f (cost=0.00..23.30 rows=665 width=33)
Filter: (a IS FALSE)
-> Seq Scan on boolspart_t (cost=0.00..23.30 rows=665 width=33)
Filter: (a IS FALSE)
(5 rows)

drop table boolspart;
create table multiboolspart (a bool, b bool, c bool, d float, e text) partition by range (a, b, c);
create table multiboolspart_fff partition of multiboolspart for values from (minvalue, minvalue, minvalue) to (false, false, false);
create table multiboolspart_fft partition of multiboolspart for values from (false, false, false) to (false, false, true);
create table multiboolspart_ftf partition of multiboolspart for values from (false, false, true) to (false, true, false);
create table multiboolspart_ftt partition of multiboolspart for values from (false, true, false) to (false, true, true);
create table multiboolspart_tff partition of multiboolspart for values from (false, true, true) to (true, false, false);
create table multiboolspart_tft partition of multiboolspart for values from (true, false, false) to (true, false, true);
create table multiboolspart_ttf partition of multiboolspart for values from (true, false, true) to (true, true, false);
create table multiboolspart_ttt partition of multiboolspart for values from (true, true, false) to (true, true, true);
create table multiboolspart_max partition of multiboolspart for values from (true, true, true) to (maxvalue, maxvalue, maxvalue);
\d+ multiboolspart;
Table "public.multiboolspart"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------+-----------+----------+---------+----------+--------------+-------------
a | boolean | | | | plain | |
b | boolean | | | | plain | |
c | boolean | | | | plain | |
d | double precision | | | | plain | |
e | text | | | | extended | |
Partition key: RANGE (a, b, c)
Partitions: multiboolspart_fff FOR VALUES FROM (MINVALUE, MINVALUE, MINVALUE) TO (false, false, false),
multiboolspart_fft FOR VALUES FROM (false, false, false) TO (false, false, true),
multiboolspart_ftf FOR VALUES FROM (false, false, true) TO (false, true, false),
multiboolspart_ftt FOR VALUES FROM (false, true, false) TO (false, true, true),
multiboolspart_max FOR VALUES FROM (true, true, true) TO (MAXVALUE, MAXVALUE, MAXVALUE),
multiboolspart_tff FOR VALUES FROM (false, true, true) TO (true, false, false),
multiboolspart_tft FOR VALUES FROM (true, false, false) TO (true, false, true),
multiboolspart_ttf FOR VALUES FROM (true, false, true) TO (true, true, false),
multiboolspart_ttt FOR VALUES FROM (true, true, false) TO (true, true, true)

insert into multiboolspart (a, b, c, d, e) values (true, false, true, 1.7, 'hello');
explain select * from multiboolspart where a is true and b is false and c is true;
QUERY PLAN
----------------------------------------------------------------------------
Append (cost=0.00..150.50 rows=1008 width=43)
-> Seq Scan on multiboolspart_fff (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_fft (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ftf (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_tff (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_tft (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ttf (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_max (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS FALSE) AND (c IS TRUE))
(15 rows)

explain select * from multiboolspart where a is true and b is true and c is true;
QUERY PLAN
----------------------------------------------------------------------------
Append (cost=0.00..193.50 rows=1296 width=43)
-> Seq Scan on multiboolspart_fff (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_fft (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ftf (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ftt (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_tff (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_tft (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ttf (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_ttt (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
-> Seq Scan on multiboolspart_max (cost=0.00..21.50 rows=144 width=43)
Filter: ((a IS TRUE) AND (b IS TRUE) AND (c IS TRUE))
(19 rows)

select * from multiboolspart;
a | b | c | d | e
---+---+---+-----+-------
t | f | t | 1.7 | hello
(1 row)

drop table multiboolspart;