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
