MIME-Version: 1.0
From: Matt Hughes <hughes.matt@gmail.com>
Date: Fri, 5 Jul 2024 11:24:23 -0400
Message-ID: <CANi6QLzHH1QySGK3r_WWu+FKxvCRXNBzvEY2dhFAA3XGuShg_g@mail.gmail.com>
Subject: Planning of sub partitions
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002c6515061c81a937"
Archived-At: <https://www.postgresql.org/message-id/CANi6QLzHH1QySGK3r_WWu%2BFKxvCRXNBzvEY2dhFAA3XGuShg_g%40mail.gmail.com>
Precedence: bulk

--0000000000002c6515061c81a937
Content-Type: text/plain; charset="UTF-8"

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.

--0000000000002c6515061c81a937
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>I am trying to model a stateful `event` table using p=
artitions.=C2=A0 An event has date_raised (not null) and date_cleared (null=
able) columns.=C2=A0 An event is in the &quot;open&quot; state when it has =
a null date_cleared; it is in the &quot;closed&quot; state when date_cleare=
d is set.=C2=A0 Once date_cleared is set, it won&#39;t change.</div><div><b=
r></div><div>While most events close after a short period of time, a few st=
ragglers stay open for weeks or months.=C2=A0 I&#39;m trying to optimize my=
 system for the following things:</div><div><br></div><div>- 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</div><div>- Users want to see all ope=
n events OR closed events within the past N days</div><div><br></div><div>T=
o do this, I came up with the following schema:</div><div><br></div><div>cr=
eate table event (<br>=C2=A0 id uuid not null, <br>=C2=A0 cleared boolean n=
ot null,<br>=C2=A0 date_raised timestamp without time zone not null,<br>=C2=
=A0 date_cleared timestamp without time zone,<br>=C2=A0 primary key (id, da=
te_raised, cleared)<br>) PARTITION BY LIST (cleared); <br><br>CREATE TABLE =
event_open PARTITION OF event FOR VALUES IN (false);<br>CREATE TABLE event_=
closed PARTITION OF event FOR VALUES IN (true) partition by range(date_rais=
ed);<br>CREATE TABLE event_closed_y2024_m01 PARTITION OF event_closed FOR V=
ALUES FROM (&#39;2024-01-01&#39;) to (&#39;2024-02-01&#39;);<br>CREATE TABL=
E event_closed_y2024_m02 PARTITION OF event_closed FOR VALUES FROM (&#39;20=
24-02-01&#39;) to (&#39;2024-03-01&#39;);</div><div><br></div><div>This wor=
ks for the most part but the plan for my compound query does not behave as =
I would expect:</div><div><br></div><div>-- 1. correctly only picks event_o=
pen partition<br>explain select * from event where cleared is false;<br><br=
>-- 2. correctly picks all event_closed_* partitions<br>explain select * fr=
om event where cleared is true;<br><br>-- 3. correctly picks just the event=
_closed_y2024_m01 partition =C2=A0<br>explain select * from event where cle=
ared is true and date_raised &gt; &#39;2024-01-01&#39; AND date_raised &lt;=
 &#39;2024-01-02&#39;;<br><br>-- 4. uses all partitions; should exclude eve=
nt_closed_y2024_m02 <br>explain select * from event <br>where <br>=C2=A0cle=
ared is false OR <br>=C2=A0(cleared is true and date_raised &gt; &#39;2024-=
01-01&#39; AND date_raised &lt; &#39;2024-01-02&#39;);<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 QUERY PLAN<br=
>--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------<br>=C2=A0Append =C2=A0(cost=3D0.00..110.91=
 rows=3D2322 width=3D25)<br>=C2=A0 =C2=A0-&gt; =C2=A0Seq Scan on event_open=
 event_1 =C2=A0(cost=3D0.00..33.10 rows=3D774 width=3D25)<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0Filter: ((cleared IS FALSE) OR ((date_raised &gt; &#39;=
2024-01-01 00:00:00&#39;::timestamp without time zone) AND (date_raised &lt=
; &#39;2024-01-02 00:00:00&#39;::timestamp without time zone)))<br>=C2=A0 =
=C2=A0-&gt; =C2=A0Seq Scan on event_closed_y2024_m01 event_2 =C2=A0(cost=3D=
0.00..33.10 rows=3D774 width=3D25)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Fil=
ter: ((cleared IS FALSE) OR ((date_raised &gt; &#39;2024-01-01 00:00:00&#39=
;::timestamp without time zone) AND (date_raised &lt; &#39;2024-01-02 00:00=
:00&#39;::timestamp without time zone)))<br>=C2=A0 =C2=A0-&gt; =C2=A0Seq Sc=
an on event_closed_y2024_m02 event_3 =C2=A0(cost=3D0.00..33.10 rows=3D774 w=
idth=3D25)<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((cleared IS FALSE)=
 OR ((date_raised &gt; &#39;2024-01-01 00:00:00&#39;::timestamp without tim=
e zone) AND (date_raised &lt; &#39;2024-01-02 00:00:00&#39;::timestamp with=
out time zone)))</div><div><br></div><div><br></div><div>Is this a bug?=C2=
=A0 Query 4 is just a union of queries 2/3 which pick the right partitions.=
=C2=A0 Do you see anything else wrong with this approach?=C2=A0=C2=A0</div>=
<div><br></div><div>Note that `closed` column is somewhat of a hack as it i=
s completely computeable from `date_cleared is not null`.=C2=A0 However, as=
 I understand it, partitions can only be declared on values that are part o=
f the primary key.=C2=A0 <br></div><div><br></div></div>

--0000000000002c6515061c81a937--