Re: Correct way of using complex expressions as partitioning key - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Correct way of using complex expressions as partitioning key
Date
Msg-id 0ce0470f4727ea3f5253999640f3fdc633459c33.camel@cybertec.at
Whole thread Raw
In response to Correct way of using complex expressions as partitioning key  (Alexander Rumyantsev <alexander@rumyantsev.com>)
List pgsql-general
On Fri, 2023-10-27 at 07:27 +0300, Alexander Rumyantsev wrote:
> Is there some correct way to use complex expressions as a key for partitioned table?
> Inserting works as expected, but select runs over all partitions until use complete
> partition key expression as predicate
>
> test=# create table test (
>         id text,
>         v1 bigint,
>         v2 bigint
> )
> partition by range (((v1 + v2) % 10));
> CREATE TABLE
>
> test=# explain analyze select * from test where v1 = 100 and v2 = 100;
> [no partition pruning]
>
> test=# explain analyze select * from test where ((v1 + v2) % 10) = 0 and v1 = 100 and v2 = 100;
> [partition pruning]

Yes, you only get partition pruning if the WHERE clause contains a comparison with
the partitioning key.  There is no way around that.

Yours,
Laurenz Albe



pgsql-general by date:

Previous
From: Justin Clift
Date:
Subject: Re: Disk wait problem... may not be hardware...
Next
From: Y_esteembsv-forum
Date:
Subject: need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly