public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Jonathan Reis <[email protected]>
Cc: [email protected]
Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
Date: Thu, 23 Oct 2025 08:55:45 +1300
Message-ID: <CAApHDvrD7TgbgVp0ab91Zp-99YQSkwXhE9S0H8jeMNZw7SgGRQ@mail.gmail.com> (raw)
In-Reply-To: <CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>
References: <CAE_7N37MopcS6SYmsphE7UxQ9bJTyvgsmVb-NyCncy7fzkiC4Q@mail.gmail.com>
	<CAE_7N37Amq_G6bZDpv9tbxcZJNGVhSv8mt2MJgdZTqGO5Pdbnw@mail.gmail.com>

On Wed, 22 Oct 2025 at 23:53, Jonathan Reis <[email protected]> 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

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Performance implications of partitioning by UUIDv7 range in PostgreSQL v18
  In-Reply-To: <CAApHDvrD7TgbgVp0ab91Zp-99YQSkwXhE9S0H8jeMNZw7SgGRQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox