Thread: Planning of sub partitions
I am trying to model a stateful `event` table using partitions. An event has date_raised (not null) and date_cleared (nullable) columns. An event is in the "open" state when it has a null date_cleared; it is in the "closed" state when date_cleared is set. Once date_cleared is set, it won't change.
While most events close after a short period of time, a few stragglers stay open for weeks or months. I'm trying to optimize my system for the following things:
- I need to drop events older than N days; I want to use partitions so I can just drop the table rather than an expensive DELETE
- Users want to see all open events OR closed events within the past N days
To do this, I came up with the following schema:
create table event (
id uuid not null,
cleared boolean not null,
date_raised timestamp without time zone not null,
date_cleared timestamp without time zone,
primary key (id, date_raised, cleared)
) PARTITION BY LIST (cleared);
CREATE TABLE event_open PARTITION OF event FOR VALUES IN (false);
CREATE TABLE event_closed PARTITION OF event FOR VALUES IN (true) partition by range(date_raised);
CREATE TABLE event_closed_y2024_m01 PARTITION OF event_closed FOR VALUES FROM ('2024-01-01') to ('2024-02-01');
CREATE TABLE event_closed_y2024_m02 PARTITION OF event_closed FOR VALUES FROM ('2024-02-01') to ('2024-03-01');
id uuid not null,
cleared boolean not null,
date_raised timestamp without time zone not null,
date_cleared timestamp without time zone,
primary key (id, date_raised, cleared)
) PARTITION BY LIST (cleared);
CREATE TABLE event_open PARTITION OF event FOR VALUES IN (false);
CREATE TABLE event_closed PARTITION OF event FOR VALUES IN (true) partition by range(date_raised);
CREATE TABLE event_closed_y2024_m01 PARTITION OF event_closed FOR VALUES FROM ('2024-01-01') to ('2024-02-01');
CREATE TABLE event_closed_y2024_m02 PARTITION OF event_closed FOR VALUES FROM ('2024-02-01') to ('2024-03-01');
This works for the most part but the plan for my compound query does not behave as I would expect:
-- 1. correctly only picks event_open partition
explain select * from event where cleared is false;
-- 2. correctly picks all event_closed_* partitions
explain select * from event where cleared is true;
-- 3. correctly picks just the event_closed_y2024_m01 partition
explain select * from event where cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02';
-- 4. uses all partitions; should exclude event_closed_y2024_m02
explain select * from event
where
cleared is false OR
(cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..110.91 rows=2322 width=25)
-> Seq Scan on event_open event_1 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m01 event_2 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m02 event_3 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
explain select * from event where cleared is false;
-- 2. correctly picks all event_closed_* partitions
explain select * from event where cleared is true;
-- 3. correctly picks just the event_closed_y2024_m01 partition
explain select * from event where cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02';
-- 4. uses all partitions; should exclude event_closed_y2024_m02
explain select * from event
where
cleared is false OR
(cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..110.91 rows=2322 width=25)
-> Seq Scan on event_open event_1 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m01 event_2 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m02 event_3 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
Is this a bug? Query 4 is just a union of queries 2/3 which pick the right partitions. Do you see anything else wrong with this approach?
Note that `closed` column is somewhat of a hack as it is completely computeable from `date_cleared is not null`. However, as I understand it, partitions can only be declared on values that are part of the primary key.
One workaround seems to be using a CTE with a union:
with openAndRecentlyRaisedEvents as (
select * from event where cleared is false
union all
select * from event
where cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02';
)
select * from openAndREcentlyRaisedEvents
On Fri, Jul 5, 2024 at 11:24 AM Matt Hughes <hughes.matt@gmail.com> wrote:
I am trying to model a stateful `event` table using partitions. An event has date_raised (not null) and date_cleared (nullable) columns. An event is in the "open" state when it has a null date_cleared; it is in the "closed" state when date_cleared is set. Once date_cleared is set, it won't change.While most events close after a short period of time, a few stragglers stay open for weeks or months. I'm trying to optimize my system for the following things:- I need to drop events older than N days; I want to use partitions so I can just drop the table rather than an expensive DELETE- Users want to see all open events OR closed events within the past N daysTo do this, I came up with the following schema:create table event (
id uuid not null,
cleared boolean not null,
date_raised timestamp without time zone not null,
date_cleared timestamp without time zone,
primary key (id, date_raised, cleared)
) PARTITION BY LIST (cleared);
CREATE TABLE event_open PARTITION OF event FOR VALUES IN (false);
CREATE TABLE event_closed PARTITION OF event FOR VALUES IN (true) partition by range(date_raised);
CREATE TABLE event_closed_y2024_m01 PARTITION OF event_closed FOR VALUES FROM ('2024-01-01') to ('2024-02-01');
CREATE TABLE event_closed_y2024_m02 PARTITION OF event_closed FOR VALUES FROM ('2024-02-01') to ('2024-03-01');This works for the most part but the plan for my compound query does not behave as I would expect:-- 1. correctly only picks event_open partition
explain select * from event where cleared is false;
-- 2. correctly picks all event_closed_* partitions
explain select * from event where cleared is true;
-- 3. correctly picks just the event_closed_y2024_m01 partition
explain select * from event where cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02';
-- 4. uses all partitions; should exclude event_closed_y2024_m02
explain select * from event
where
cleared is false OR
(cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..110.91 rows=2322 width=25)
-> Seq Scan on event_open event_1 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m01 event_2 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))
-> Seq Scan on event_closed_y2024_m02 event_3 (cost=0.00..33.10 rows=774 width=25)
Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised < '2024-01-02 00:00:00'::timestamp without time zone)))Is this a bug? Query 4 is just a union of queries 2/3 which pick the right partitions. Do you see anything else wrong with this approach?Note that `closed` column is somewhat of a hack as it is completely computeable from `date_cleared is not null`. However, as I understand it, partitions can only be declared on values that are part of the primary key.
On Sat, 6 Jul 2024 at 03:24, Matt Hughes <hughes.matt@gmail.com> wrote: > -- 4. uses all partitions; should exclude event_closed_y2024_m02 > explain select * from event > where > cleared is false OR > (cleared is true and date_raised > '2024-01-01' AND date_raised < '2024-01-02'); > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Append (cost=0.00..110.91 rows=2322 width=25) > -> Seq Scan on event_open event_1 (cost=0.00..33.10 rows=774 width=25) > Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised< '2024-01-02 00:00:00'::timestamp without time zone))) > -> Seq Scan on event_closed_y2024_m01 event_2 (cost=0.00..33.10 rows=774 width=25) > Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised< '2024-01-02 00:00:00'::timestamp without time zone))) > -> Seq Scan on event_closed_y2024_m02 event_3 (cost=0.00..33.10 rows=774 width=25) > Filter: ((cleared IS FALSE) OR ((date_raised > '2024-01-01 00:00:00'::timestamp without time zone) AND (date_raised< '2024-01-02 00:00:00'::timestamp without time zone))) > > > Is this a bug? Query 4 is just a union of queries 2/3 which pick the right partitions. Do you see anything else wrongwith this approach? I'm not sure I'd want to class it as a bug. I think we could call it a limitation of pruning with multiple levels of partitioned tables. The problem is that pruning for the "event" table matches both of its partitions; event_open and event_closed. Since event_closed is also a partitioned table, pruning is also executed on that partitioned table. When that pruning is done, it processes the WHERE clause conditions and only finds date_raised conditions in an OR branch, therefore it cannot prune since the other OR does not contain the same conditions. There is some code in gen_partprune_steps() that makes use of the partitioning qual of the partitioned table, but the comment there explains that it's only intended for DEFAULT partitions in cases when partition keys are shared between a partitioned table and its child partitioned table. Adding the partitioning qual in this case wouldn't help since the partition keys are different at both levels. You could get what you want using two columns in a RANGE partitioned table, such as: create table event ( id uuid not null, cleared boolean not null, date_raised timestamp without time zone not null, date_cleared timestamp without time zone, primary key (id, date_raised, cleared) ) PARTITION BY RANGE (cleared, date_raised); CREATE TABLE event_open PARTITION OF event FOR VALUES FROM (false, MINVALUE) TO (false, MAXVALUE); CREATE TABLE event_closed_y2024_m01 PARTITION OF event FOR VALUES FROM (true,'2024-01-01') to (true,'2024-02-01'); CREATE TABLE event_closed_y2024_m02 PARTITION OF event FOR VALUES FROM (true,'2024-02-01') to (true,'2024-03-01'); explain select * from event where not cleared OR (cleared and date_raised > '2024-01-01' AND date_raised < '2024-01-02'); David