From: | Alexander Rumyantsev <alexander(at)rumyantsev(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Correct way of using complex expressions as partitioning key |
Date: | 2023-10-27 04:27:54 |
Message-ID: | 20D8B5BB-DB0B-400D-8560-2F5765361CB1@rumyantsev.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Clift | 2023-10-27 06:22:16 | Re: Disk wait problem... may not be hardware... |
Previous Message | pf | 2023-10-27 02:03:25 | Re: Disk wait problem... may not be hardware... |