Re: Declarative partitioning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Amit Langote <amitlangote09(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
Cc: Ildar Musin <i(dot)musin(at)postgrespro(dot)ru>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Declarative partitioning
Date: 2016-04-20 10:28:40
Message-ID: 571759D8.7030205@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 2016/04/19 23:52, Amit Langote wrote:
> On Tue, Apr 19, 2016 at 11:26 PM, Alexander Korotkov
>> Another question is that it might be NOT what users expect from that. From
>> the syntax side it very looks like defining something boxes regions for two
>> keys which could be replacement for subpartitioning. But it isn't so.
>
> Need to check why query with qual b < 100 behaves the way it does.
> Something's going wrong there with the constraints (partition
> predicates) that are being generated internally (as mentioned before,
> still driven by constraint exclusion using the constraints generated
> on-the-fly).
>
> As for the composite range partition bounds in Ildar's example, it's
> as if the second value in the key never determines the fate of a row
> going into some partition, therefore no constraints should have been
> generated for column b of the key. I'm afraid that's not the case as
> per the latest patch. Will fix.

The strange behavior that Ildar reported should have been fixed with the
attached updated set of patches (v2):

create table test(a int, b int) partition by range (a, b);
create table test_1 partition of test for values start (0, 0) end (100, 100);
create table test_2 partition of test for values start (100, 100) end
(200, 200);
create table test_3 partition of test for values start (200, 200) end
(300, 300);
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

insert into test(a, b) values (150, 50);
INSERT 0 1

select * from test where b < 100;
a | b
-----+----
150 | 50
(1 row)

explain (costs off) select * from test where b < 100;
QUERY PLAN
---------------------------
Append
-> Seq Scan on test
Filter: (b < 100)
-> Seq Scan on test_1
Filter: (b < 100)
-> Seq Scan on test_2
Filter: (b < 100)
-> Seq Scan on test_3
Filter: (b < 100)
(9 rows)

Multi-column range partitioning seems a bit tricky as far as generating
constraints on individual columns using a partition's lower and upper
bounds (both composite values) is concerned. I mentally pictured
something like the following example scenario:

create table test(a int, b int, c int)
partition by range (a, b, c);
create table test_1 partition of test
for values start (0, 0, 0) end (0, 2, 0);
create table test_2 partition of test
for values start (0, 2, 0) end (0, 3, 0);
create table test_3 partition of test
for values start (0, 3, 0) end (0, 4, 0);
create table test_4 partition of test
for values start (0, 4, 0) end (1, 0, 0);
create table test_5 partition of test
for values start (1, 0, 0) end (1, 2, 0);
create table test_6 partition of test
for values start (1, 2, 0) end (1, 3, 0);
create table test_7 partition of test
for values start (1, 3, 0) end (1, 4, 0);
create table test_8 partition of test
for values start (1, 4, 0) end (2, 0, 0);

Useful to think of the above as sequence of ranges [000, 020), [020, 030),
[030, 040), [040, 100), [100, 120), [120, 130), [130, 140), [140, 200) for
purposes of finding the partition for a row.

Then constraints generated internally for each partition:

test_1: a = 0 AND b >= 0 AND b <= 2
test_2: a = 0 AND b >= 2 AND b <= 3
test_3: a = 0 AND b >= 3 AND b <= 4
test_4: a >= 0 AND a <= 1
test_5: a = 1 AND b >= 0 AND b <= 2
test_6: a = 1 AND b >= 2 AND b <= 3
test_7: a = 1 AND b >= 3 AND b <= 4
test_8: a >= 1 AND a <= 2

I will try further to poke holes in my thinking about this. Please feel
free to point out if you find any.

Thanks,
Amit

Attachment Content-Type Size
0001-Add-syntax-to-specify-partition-key-v2.patch text/x-diff 41.5 KB
0002-Infrastructure-for-creation-of-partitioned-tables-v2.patch text/x-diff 85.9 KB
0003-Add-syntax-to-create-partitions-v2.patch text/x-diff 78.0 KB
0004-Infrastructure-for-partition-metadata-storage-and-ma-v2.patch text/x-diff 97.7 KB
0005-Introduce-tuple-routing-for-partitioned-tables-v2.patch text/x-diff 27.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Petr Jelinek 2016-04-20 13:14:16 pg_stat_activity crashes
Previous Message Yury Zhuravlev 2016-04-20 08:43:53 Re: Proposal: Remove regress-python3-mangle.mk