MIME-Version: 1.0
References: <CANi6QLzHH1QySGK3r_WWu+FKxvCRXNBzvEY2dhFAA3XGuShg_g@mail.gmail.com>
In-Reply-To: <CANi6QLzHH1QySGK3r_WWu+FKxvCRXNBzvEY2dhFAA3XGuShg_g@mail.gmail.com>
From: Matt Hughes <hughes.matt@gmail.com>
Date: Fri, 5 Jul 2024 12:06:13 -0400
Message-ID: <CANi6QLwZ-caHoe=6=1vyg5ULaZGh0XqW9i=HEYOsE0kKsqUnag@mail.gmail.com>
Subject: Re: Planning of sub partitions
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bf67ef061c823e6c"
Archived-At: <https://www.postgresql.org/message-id/CANi6QLwZ-caHoe%3D6%3D1vyg5ULaZGh0XqW9i%3DHEYOsE0kKsqUnag%40mail.gmail.com>
Precedence: bulk

--000000000000bf67ef061c823e6c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=E2=80=AFAM 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 da=
ys
>
> 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=3D0.00..110.91 rows=3D2322 width=3D25)
>    ->  Seq Scan on event_open event_1  (cost=3D0.00..33.10 rows=3D774 wid=
th=3D25)
>          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=3D0.00..33.10
> rows=3D774 width=3D25)
>          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=3D0.00..33.10
> rows=3D774 width=3D25)
>          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.
>
>

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

<div dir=3D"ltr"><div>One workaround seems to be using a CTE with a union:<=
/div><div><br></div><div>with openAndRecentlyRaisedEvents as (</div><div>=
=C2=A0 select * from event where cleared is false</div><div>=C2=A0 union al=
l</div><div>=C2=A0 select * from event</div><div>=C2=A0 where cleared is tr=
ue and date_raised &gt; &#39;2024-01-01&#39; AND date_raised &lt; &#39;2024=
-01-02&#39;;</div><div>)</div><div>select * from openAndREcentlyRaisedEvent=
s</div><div><br></div><div><br></div></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jul 5, 2024 at 11:24=E2=80=AFA=
M Matt Hughes &lt;<a href=3D"mailto:hughes.matt@gmail.com">hughes.matt@gmai=
l.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:=
1ex"><div dir=3D"ltr"><div>I am trying to model a stateful `event` table us=
ing partitions.=C2=A0 An event has date_raised (not null) and date_cleared =
(nullable) 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_c=
leared is set.=C2=A0 Once date_cleared is set, it won&#39;t change.</div><d=
iv><br></div><div>While most events close after a short period of time, a f=
ew stragglers stay open for weeks or months.=C2=A0 I&#39;m trying to optimi=
ze 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 al=
l open events OR closed events within the past N days</div><div><br></div><=
div>To do this, I came up with the following schema:</div><div><br></div><d=
iv>create table event (<br>=C2=A0 id uuid not null, <br>=C2=A0 cleared bool=
ean not null,<br>=C2=A0 date_raised timestamp without time zone not null,<b=
r>=C2=A0 date_cleared timestamp without time zone,<br>=C2=A0 primary key (i=
d, date_raised, cleared)<br>) PARTITION BY LIST (cleared); <br><br>CREATE T=
ABLE event_open PARTITION OF event FOR VALUES IN (false);<br>CREATE TABLE e=
vent_closed PARTITION OF event FOR VALUES IN (true) partition by range(date=
_raised);<br>CREATE TABLE event_closed_y2024_m01 PARTITION OF event_closed =
FOR VALUES FROM (&#39;2024-01-01&#39;) to (&#39;2024-02-01&#39;);<br>CREATE=
 TABLE event_closed_y2024_m02 PARTITION OF event_closed FOR VALUES FROM (&#=
39;2024-02-01&#39;) to (&#39;2024-03-01&#39;);</div><div><br></div><div>Thi=
s works for the most part but the plan for my compound query does not behav=
e as I would expect:</div><div><br></div><div>-- 1. correctly only picks ev=
ent_open partition<br>explain select * from event where cleared is false;<b=
r><br>-- 2. correctly picks all event_closed_* partitions<br>explain select=
 * from event where cleared is true;<br><br>-- 3. correctly picks just the =
event_closed_y2024_m01 partition =C2=A0<br>explain select * from event wher=
e cleared 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 exclud=
e event_closed_y2024_m02 <br>explain select * from event <br>where <br>=C2=
=A0cleared 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 eve=
nt_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_rai=
sed &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=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_m02 event_3 =C2=A0(cost=3D0.00..33.10 row=
s=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 wi=
thout time zone) AND (date_raised &lt; &#39;2024-01-02 00:00:00&#39;::times=
tamp without 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 par=
titions.=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 hac=
k as it is completely computeable from `date_cleared is not null`.=C2=A0 Ho=
wever, as I understand it, partitions can only be declared on values that a=
re part of the primary key.=C2=A0 <br></div><div><br></div></div>
</blockquote></div>

--000000000000bf67ef061c823e6c--