From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Alexander Rumyantsev <alexander(at)rumyantsev(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Correct way of using complex expressions as partitioning key |
Date: | 2023-10-27 08:33:48 |
Message-ID: | 0ce0470f4727ea3f5253999640f3fdc633459c33.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Y_esteembsv-forum | 2023-10-27 08:56:07 | need SQL logic to find out way's to Implement check sum to validate table data is migrated correctly |
Previous Message | Justin Clift | 2023-10-27 06:22:16 | Re: Disk wait problem... may not be hardware... |