Thread

1. Correct way of using complex expressions as partitioning key

   Alexander Rumyantsev <alexander@rumyantsev.com> — 2023-10-27T04:27:54Z

   Hello!
   
   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=# create table test_1 partition of test for values from (0) to (1);
   CREATE TABLE
   
   test=# create table test_2 partition of test for values from (1) to (2);
   CREATE TABLE
   
   test=# insert into test values (1, 100, 101);
   INSERT 0 1
   
   test=# insert into test values (1, 100, 100);
   INSERT 0 1
   
   test=# select * from test_1;
    id | v1  | v2
   ----+-----+-----
    1  | 100 | 100
   (1 row)
   
   test=# select * from test_2;
    id | v1  | v2
   ----+-----+-----
    1  | 100 | 101
   (1 row)
   
   test=# explain analyze select * from test where v1 = 100 and v2 = 100;
                                                  QUERY PLAN
   --------------------------------------------------------------------------------------------------------
    Append  (cost=0.00..52.11 rows=2 width=48) (actual time=0.011..0.017 rows=1 loops=1)
      ->  Seq Scan on test_1  (cost=0.00..26.05 rows=1 width=48) (actual time=0.011..0.011 rows=1 loops=1)
            Filter: ((v1 = 100) AND (v2 = 100))
      ->  Seq Scan on test_2  (cost=0.00..26.05 rows=1 width=48) (actual time=0.004..0.004 rows=0 loops=1)
            Filter: ((v1 = 100) AND (v2 = 100))
            Rows Removed by Filter: 1
    Planning Time: 0.457 ms
    Execution Time: 0.036 ms
   (8 rows)
   
   test=# explain analyze select * from test where ((v1 + v2) % 10) = 0 and v1 = 100 and v2 = 100;
                                                 QUERY PLAN
   -------------------------------------------------------------------------------------------------------
    Seq Scan on test_1 test  (cost=0.00..34.08 rows=1 width=48) (actual time=0.010..0.011 rows=1 loops=1)
      Filter: ((v1 = 100) AND (v2 = 100) AND (((v1 + v2) % '10'::bigint) = 0))
    Planning Time: 0.131 ms
    Execution Time: 0.031 ms
   (4 rows)
   
   
   
   
   

2. Re: Correct way of using complex expressions as partitioning key

   Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-27T08:33:48Z

   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
   
   
   
   

3. Re: Correct way of using complex expressions as partitioning key

   shammat@gmx.net — 2023-10-27T09:08:39Z

   
   Alexander Rumyantsev schrieb am 27.10.2023 um 06:27:
   > Hello!
   >
   > 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=# create table test_1 partition of test for values from (0) to (1);
   > CREATE TABLE
   >
   > test=# create table test_2 partition of test for values from (1) to (2);
   > CREATE TABLE
   >
   > test=# insert into test values (1, 100, 101);
   > INSERT 0 1
   >
   > test=# insert into test values (1, 100, 100);
   > INSERT 0 1
   >
   > test=# select * from test_1;
   >  id | v1  | v2
   > ----+-----+-----
   >  1  | 100 | 100
   > (1 row)
   >
   > test=# select * from test_2;
   >  id | v1  | v2
   > ----+-----+-----
   >  1  | 100 | 101
   > (1 row)
   
   It seems you are trying to simulate hash partitioning using that expression.
   Why not use hash partitioning directly then?
   
   create table test (
     id text,
     v1 bigint,
     v2 bigint
   )
   partition by hash (v1, v2);
   
   create table test_1
     partition of test
     for values with (modulus 2, remainder 0);
   create table test_2
     partition of test
     for values with (modulus 2, remainder 1);
   
   Then the select will result in:
   
   QUERY PLAN
   -----------------------------------------------------------------------------------------------------
   Seq Scan on test_1 test  (cost=0.00..17.49 rows=1 width=48) (actual time=0.015..0.016 rows=1 loops=1)
     Filter: ((v1 = 100) AND (v2 = 100))
   Planning Time: 0.159 ms
   Execution Time: 0.037 ms
   
   
   Demo: https://dbfiddle.uk/wuopLYeQ