pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCorrect way of using complex expressions as partitioning key
3+ messages / 3 participants
[nested] [flat]
* Correct way of using complex expressions as partitioning key
@ 2023-10-27 04:27 Alexander Rumyantsev <alexander@rumyantsev.com>
0 siblings, 2 replies; 3+ messages in thread
From: Alexander Rumyantsev @ 2023-10-27 04:27 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Correct way of using complex expressions as partitioning key
@ 2023-10-27 08:33 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Alexander Rumyantsev <alexander@rumyantsev.com>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2023-10-27 08:33 UTC (permalink / raw)
To: Alexander Rumyantsev <alexander@rumyantsev.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Correct way of using complex expressions as partitioning key
@ 2023-10-27 09:08 Thomas Kellerer <shammat@gmx.net>
parent: Alexander Rumyantsev <alexander@rumyantsev.com>
1 sibling, 0 replies; 3+ messages in thread
From: Thomas Kellerer @ 2023-10-27 09:08 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-27 09:08 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-27 04:27 Correct way of using complex expressions as partitioning key Alexander Rumyantsev <alexander@rumyantsev.com>
2023-10-27 08:33 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-27 09:08 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox