Search Postgresql Archives

Correct way of using complex expressions as partitioning key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)







[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux