multicolumn partitioning help

From: James Robertson <james(at)jsrobertson(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: multicolumn partitioning help
Date: 2023-03-12 17:59:32
Message-ID: CAOA7M6tY5moDThZH=V2iFJ1ahxz6K2JRqGzDgu-gPP+GhLT4Lg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey folks,
I am having issues with multicolumn partitioning. For reference I am using
the following link as my guide:
https://www.postgresql.org/docs/devel/sql-createtable.html

To demonstrate my problem, I created a simple table called humans. I want
to partition by the year of the human birth and then the first character of
the hash. So for each year I'll have year*16 partitions. (hex)

CREATE TABLE humans (
hash bytea,
fname text,
dob date
)PARTITION BY RANGE (EXTRACT(YEAR FROM dob),substring(hash::text, 1,
1));

Reading the documentation: "When creating a range partition, the lower
bound specified with FROM is an inclusive bound, whereas the upper bound
specified with TO is an exclusive bound".

However I can't insert any of the following after the first one, because it
says it overlaps. Do I need to do anything different when defining
multi-column partitions?

This works:
CREATE TABLE humans_1968_0 PARTITION OF humans FOR VALUES FROM (1968, '0')
TO (1969, '1');

These fail:
CREATE TABLE humans_1968_1 PARTITION OF humans FOR VALUES FROM (1968, '1')
TO (1969, '2');
CREATE TABLE humans_1968_2 PARTITION OF humans FOR VALUES FROM (1968, '2')
TO (1969, '3');
CREATE TABLE humans_1968_3 PARTITION OF humans FOR VALUES FROM (1968, '3')
TO (1969, '4');
CREATE TABLE humans_1968_4 PARTITION OF humans FOR VALUES FROM (1968, '4')
TO (1969, '5');
CREATE TABLE humans_1968_5 PARTITION OF humans FOR VALUES FROM (1968, '5')
TO (1969, '6');
CREATE TABLE humans_1968_6 PARTITION OF humans FOR VALUES FROM (1968, '6')
TO (1969, '7');
CREATE TABLE humans_1968_7 PARTITION OF humans FOR VALUES FROM (1968, '7')
TO (1969, '8');
CREATE TABLE humans_1968_8 PARTITION OF humans FOR VALUES FROM (1968, '8')
TO (1969, '9');
CREATE TABLE humans_1968_9 PARTITION OF humans FOR VALUES FROM (1968, '9')
TO (1969, 'a');
CREATE TABLE humans_1968_a PARTITION OF humans FOR VALUES FROM (1968, 'a')
TO (1969, 'b');
CREATE TABLE humans_1968_b PARTITION OF humans FOR VALUES FROM (1968, 'b')
TO (1969, 'c');
CREATE TABLE humans_1968_c PARTITION OF humans FOR VALUES FROM (1968, 'c')
TO (1969, 'd');
CREATE TABLE humans_1968_d PARTITION OF humans FOR VALUES FROM (1968, 'd')
TO (1969, 'e');
CREATE TABLE humans_1968_e PARTITION OF humans FOR VALUES FROM (1968, 'e')
TO (1969, 'f');
CREATE TABLE humans_1968_f PARTITION OF humans FOR VALUES FROM (1968, 'f')
TO (1969, 'g');
CREATE TABLE humans_1969_0 PARTITION OF humans FOR VALUES FROM (1969, '0')
TO (1970, '1');
CREATE TABLE humans_1969_1 PARTITION OF humans FOR VALUES FROM (1969, '1')
TO (1970, '2');
CREATE TABLE humans_1969_2 PARTITION OF humans FOR VALUES FROM (1969, '2')
TO (1970, '3');
CREATE TABLE humans_1969_3 PARTITION OF humans FOR VALUES FROM (1969, '3')
TO (1970, '4');
CREATE TABLE humans_1969_4 PARTITION OF humans FOR VALUES FROM (1969, '4')
TO (1970, '5');
CREATE TABLE humans_1969_5 PARTITION OF humans FOR VALUES FROM (1969, '5')
TO (1970, '6');
CREATE TABLE humans_1969_6 PARTITION OF humans FOR VALUES FROM (1969, '6')
TO (1970, '7');
CREATE TABLE humans_1969_7 PARTITION OF humans FOR VALUES FROM (1969, '7')
TO (1970, '8');
CREATE TABLE humans_1969_8 PARTITION OF humans FOR VALUES FROM (1969, '8')
TO (1970, '9');
CREATE TABLE humans_1969_9 PARTITION OF humans FOR VALUES FROM (1969, '9')
TO (1970, 'a');
CREATE TABLE humans_1969_a PARTITION OF humans FOR VALUES FROM (1969, 'a')
TO (1970, 'b');
CREATE TABLE humans_1969_b PARTITION OF humans FOR VALUES FROM (1969, 'b')
TO (1970, 'c');
CREATE TABLE humans_1969_c PARTITION OF humans FOR VALUES FROM (1969, 'c')
TO (1970, 'd');
CREATE TABLE humans_1969_d PARTITION OF humans FOR VALUES FROM (1969, 'd')
TO (1970, 'e');
CREATE TABLE humans_1969_e PARTITION OF humans FOR VALUES FROM (1969, 'e')
TO (1970, 'f');
CREATE TABLE humans_1969_f PARTITION OF humans FOR VALUES FROM (1969, 'f')
TO (1970, 'g');

Thank you for reviewing this problem.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rick Otten 2023-03-13 12:45:14 Re: Huge Tables
Previous Message Jeff Janes 2023-03-11 22:13:59 Re: Planner choosing nested loop in place of Hashjoin