Re: Planning of sub partitions

[Matt Hughes <hughes.matt@xxxxxxxxx>]

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@xxxxxxxxx> 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 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');

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.