Re: Correct way of using complex expressions as partitioning key - Mailing 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