From: Ildar Musin <i.musin@postgrespro.ru>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Amit Langote <amitlangote09@gmail.com>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Corey Huinker <corey.huinker@gmail.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Declarative partitioning
Date: Wed, 20 Apr 2016 17:46:43 +0300
Message-ID: <57179653.30905@postgrespro.ru> (raw)
In-Reply-To: <571759D8.7030205@lab.ntt.co.jp>
References: <55D3093C.5010800@lab.ntt.co.jp>
<56C55973.80309@lab.ntt.co.jp>
<CADkLM=fz4SRiaLggFctOcJegpcjxaWPwCyi6YDwq9Wnceno7=w@mail.gmail.com>
<56CD6598.4090302@lab.ntt.co.jp>
<CADkLM=f1zyL-cV_LK8GpXv=iS_=VbHs8bernUMa+8yHtUXjebQ@mail.gmail.com>
<56DE2D4B.1040001@lab.ntt.co.jp>
<CADkLM=cb7q4TSYQYX4EsMHVK0hjEnBJiA8+XMSSropk2CHwa_A@mail.gmail.com>
<56DF74F0.7060404@lab.ntt.co.jp>
<CAPpHfdut34uqoY1vKgvBhsGZqhR6Nky=_Dxn0un57G_M2CvLTA@mail.gmail.com>
<CA+HiwqFFH10_ku6z_Ps=H7=pR5cWJk9Bdr9nE08+3F53yTEr9A@mail.gmail.com>
<CAPpHfdu0mGbt9T2Jx04OKJfJQ0zW55BFqmisinRYWd2OaowRWQ@mail.gmail.com>
<CA+TgmoaoZRqk2dr3xonta3o4omKuO4V4niVC2DKKo9VpdMc_zQ@mail.gmail.com>
<56F0AC96.2010303@lab.ntt.co.jp>
<CAFjFpReZ2nmpfOA2uGdgVp3ivWC13VDFYOQ7rs5LFeqo_Zm4vA@mail.gmail.com>
<57106F9E.3000309@lab.ntt.co.jp>
<57163954.2020701@postgrespro.ru>
<CAPpHfdtH4rhckHzkqGhGP8O7aejj=J7_qJRA3dTu+iAUpuzPgg@mail.gmail.com>
<CA+HiwqEMQ2EZ0GKBjg2hHT1uP-NRodtodc2JjN1vd0xp_BzTCg@mail.gmail.com>
<571759D8.7030205@lab.ntt.co.jp>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.musin@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: i.musin@postgrespro.ru, Langote_Amit_f8@lab.ntt.co.jp, amitlangote09@gmail.com, a.korotkov@postgrespro.ru, ashutosh.bapat@enterprisedb.com, robertmhaas@gmail.com, corey.huinker@gmail.com
Subject: Re: Declarative partitioning
In-Reply-To: <57179653.30905@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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