MIME-Version: 1.0
References: <CANi6QLzHH1QySGK3r_WWu+FKxvCRXNBzvEY2dhFAA3XGuShg_g@mail.gmail.com>
In-Reply-To: <CANi6QLzHH1QySGK3r_WWu+FKxvCRXNBzvEY2dhFAA3XGuShg_g@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Sat, 6 Jul 2024 10:26:01 +1200
Message-ID: <CAApHDvqaO5xCBm_MPSE7Y44BBxJLrs_Z16EyhX5vm3KRE+Eo8g@mail.gmail.com>
Subject: Re: Planning of sub partitions
To: Matt Hughes <hughes.matt@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvqaO5xCBm_MPSE7Y44BBxJLrs_Z16EyhX5vm3KRE%2BEo8g%40mail.gmail.com>
Precedence: bulk

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 wrong with 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