MIME-Version: 1.0
References: 
 <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
In-Reply-To: 
 <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 23 Oct 2025 08:55:45 +1300
Message-ID: 
 <CAApHDvrD7TgbgVp0ab91Zp-99YQSkwXhE9S0H8jeMNZw7SgGRQ@mail.gmail.com>
Subject: Re: Performance implications of partitioning by UUIDv7 range in
 PostgreSQL v18
To: Jonathan Reis <jon.reis@conevity.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrD7TgbgVp0ab91Zp-99YQSkwXhE9S0H8jeMNZw7SgGRQ%40mail.gmail.com>
Precedence: bulk

On Wed, 22 Oct 2025 at 23:53, Jonathan Reis <jon.reis@conevity.com> wrote:
> Will the planner efficiently prune partitions when queries filter by UUIDv7 ranges (e.g., WHERE id BETWEEN uuidv7_floor(timestamp1) AND uuidv7_floor(timestamp2) that align with time periods?

It depends. What are timestamp1 and timestamp2? If they're constants,
then the planner can prune away tables outside of the given range as
you're partitioning by range (id). Don't expect any pruning if you
were to do something like uuid_extract_timestamp(id) when partitioned
by "id". The function is just a blackbox to the pruning code. If
timestamp1 and timestamp2 are columns from another table, then the
planner won't do any pruning (the executor might).

It should be trivial to mock up some tables and look at EXPLAIN to see
what the planner can and can't do for pruning. Just keep in mind the
executor also can prune both during executor startup, which is the
first opportunity to know the results of stable functions (such as
NOW()) and external parameter values (for PREPARE statements).
Pruning can also occur during execution as parameter values being used
in clauses containing the partition key in Append/MergeAppend change.

More details about that in:
https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITION-PRUNING

David