Re: Declarative partitioning

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