Hi Amit,
On 20.04.2016 13:28, Amit Langote wrote:
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
Thanks for clarification! I tried the updated patch, now it works correctly.
I encountered another problem that concerns expressions as partitioning
key. Probably there is still some work in progress. But if it will help
here is my case:
create table inh(a int, b int) partition by range ((a+b));
create table inh_1 partition of inh for values start (0) end (10);
create table inh_2 partition of inh for values start (10) end (20);
Then if we run any SELECT query it crashes postgres:
select * from inh;
Crash occurs in get_check_expr_from_partbound(). It seems that function
is not yet expecting an expression key and designed to handle only
simple attributes keys. Backtrace:
#0 strlen () at ../sysdeps/x86_64/strlen.S:106
#1 0x00000000004add8a in hashname (fcinfo=0x7ffdbdb9c760) at hashfunc.c:145
#2 0x000000000099cc08 in DirectFunctionCall1Coll (func=0x4add66
<hashname>, collation=0, arg1=0) at fmgr.c:1027
#3 0x00000000009724dd in CatalogCacheComputeHashValue (cache=0x26590b0,
nkeys=2, cur_skey=0x7ffdbdb9cbf0) at catcache.c:207
#4 0x0000000000974979 in SearchCatCache (cache=0x26590b0, v1=32807,
v2=0, v3=0, v4=0) at catcache.c:1151
#5 0x0000000000988e35 in SearchSysCache (cacheId=6, key1=32807, key2=0,
key3=0, key4=0) at syscache.c:1006
#6 0x0000000000988fe3 in SearchSysCacheAttName (relid=32807,
attname=0x0) at syscache.c:1106
#7 0x000000000098a744 in get_attnum (relid=32807, attname=0x0) at
lsyscache.c:825
#8 0x000000000056afd2 in get_check_expr_from_partbound
(rel=0x7f868601ca20, parent=0x7f868601b770, bound=0x26e6ac8) at
partition.c:1427
#9 0x000000000056bc9e in generate_partition_check_expr
(rel=0x7f868601ca20) at partition.c:1788
#10 0x000000000056bb5f in RelationGetPartitionCheckExpr
(rel=0x7f868601ca20) at partition.c:1746
#11 0x0000000000782b5f in get_relation_constraints (root=0x268f1b8,
relationObjectId=32807, rel=0x26e5cd8, include_notnull=1 '\001') at
plancat.c:1209
#12 0x0000000000782d74 in relation_excluded_by_constraints
(root=0x268f1b8, rel=0x26e5cd8, rte=0x268ebf0) at plancat.c:1302
#13 0x000000000072a18d in set_append_rel_size (root=0x268f1b8,
rel=0x26e5690, rti=1, rte=0x268ea80) at allpaths.c:947
...
--
Ildar Musin
i.mu...@postgrespro.ru
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
