Re: Declarative partitioning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Erik Rijkers <er(at)xs4all(dot)nl>
Cc: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, Amit Langote <amitlangote09(at)gmail(dot)com>, Corey Huinker <corey(dot)huinker(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, pgsql-hackers-owner(at)postgresql(dot)org
Subject: Re: Declarative partitioning
Date: 2016-05-11 02:26:23
Message-ID: 5732984F.6090200@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers


Hi Erik,

On 2016/04/26 17:46, Erik Rijkers wrote:
> On 2016-04-15 04:35, Amit Langote wrote:
>
> A quick test with:
>
>> 0001-Add-syntax-to-specify-partition-key-v3.patch
>> 0002-Infrastructure-for-creation-of-partitioned-tables-v3.patch
>> 0003-Add-syntax-to-create-partitions-v3.patch
>> 0004-Infrastructure-for-partition-metadata-storage-and-ma-v3.patch
>> 0005-Introduce-tuple-routing-for-partitioned-tables-v3.patch
>
> patches apply, build and make check ok.

Thanks for testing!

> There is somwthing wrong with indexes on child tables (and only with
> higher rowcounts).

There was an oversight in patch 0005 that caused partition indexes to not
be opened and tuples inserted into. Attached new version should have
fixed it.

> Surely the below code should give 6 rows; it actually does return 6 rows
> without the indexes.
> With indexes it returns 0 rows.
>
> (but when doing the same test with low rowcounts, things are OK.)

...

> ---------------------------------------
> create table inh(a int, b int) partition by range ((a+b));
> create table inh_1 partition of inh for values start ( 0) end ( 10000);
> create table inh_2 partition of inh for values start ( 10000) end ( 20000);
> create table inh_3 partition of inh for values start ( 20000) end ( 100000);
>
> create index inh_1_a_idx on inh_1 (a);
> create index inh_2_a_idx on inh_2 (a);
> create index inh_3_a_idx on inh_3 (a);
>
> insert into inh select i, i as j from generate_series(1, 10000) as f(i);
>
> analyze inh_1;
> analyze inh_2;
> analyze inh_3;
>
> select 'inh' , count(*) from inh
> union all select 'inh_1', count(*) from inh_1
> union all select 'inh_2', count(*) from inh_2
> union all select 'inh_3', count(*) from inh_3
> ;
>
> explain analyze select * from inh where a between 10110 and 10115;

Hmm, this last query should return 0 rows because:

select max(a) from inh;
max
=------
10000
(1 row)

Did you by any chance mean to write the following:

explain analyze select * from inh where a + b between 10110 and 10115;

In which case:

explain analyze select * from inh where a + b between 10110 and 10115;
QUERY PLAN

=-------------------------------------------------------------------------------------------------------
Append (cost=0.00..123.00 rows=26 width=8) (actual time=0.119..6.407
rows=3 loops=1)
-> Seq Scan on inh (cost=0.00..0.00 rows=1 width=8) (actual
time=0.015..0.015 rows=0 loops=1)
Filter: (((a + b) >= 10110) AND ((a + b) <= 10115))
-> Seq Scan on inh_2 (cost=0.00..123.00 rows=25 width=8) (actual
time=0.076..6.198 rows=3 loops=1)
Filter: (((a + b) >= 10110) AND ((a + b) <= 10115))
Rows Removed by Filter: 4997
Planning time: 0.521 ms
Execution time: 6.572 ms
(8 rows)

select * from inh where a + b between 10110 and 10115;
a | b
=-----+------
5055 | 5055
5056 | 5056
5057 | 5057
(3 rows)

Now that doesn't use index for the obvious reason (mismatched key). So,
let's try one which will:

explain analyze select * from inh where a = 4567;
QUERY PLAN

=------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..17.61 rows=4 width=8) (actual time=0.189..0.293
rows=1 loops=1)
-> Seq Scan on inh (cost=0.00..0.00 rows=1 width=8) (actual
time=0.016..0.016 rows=0 loops=1)
Filter: (a = 4567)
-> Index Scan using inh_1_a_idx on inh_1 (cost=0.28..8.30 rows=1
width=8) (actual time=0.043..0.056 rows=1 loops=1)
Index Cond: (a = 4567)
-> Index Scan using inh_2_a_idx on inh_2 (cost=0.28..8.30 rows=1
width=8) (actual time=0.024..0.024 rows=0 loops=1)
Index Cond: (a = 4567)
-> Seq Scan on inh_3 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.029..0.029 rows=0 loops=1)
Filter: (a = 4567)
Rows Removed by Filter: 1
Planning time: 0.589 ms
Execution time: 0.433 ms

select * from inh where a = 4567;
a | b
=-----+------
4567 | 4567
(1 row)

No pruning occurs this time for the obvious reason (mismatched key).

Does that help clarify?

Thanks,
Amit

Attachment Content-Type Size
0001-Add-syntax-to-specify-partition-key-v4.patch text/x-diff 41.5 KB
0002-Infrastructure-for-creation-of-partitioned-tables-v4.patch text/x-diff 85.6 KB
0003-Add-syntax-to-create-partitions-v4.patch text/x-diff 77.8 KB
0004-Infrastructure-for-partition-metadata-storage-and-ma-v4.patch text/x-diff 98.6 KB
0005-Introduce-tuple-routing-for-partitioned-tables-v4.patch text/x-diff 29.2 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Euler Taveira 2016-05-11 02:35:41 Re: Does Type Have = Operator?
Previous Message Euler Taveira 2016-05-11 02:08:56 Re: alter table alter column ... (larger type) ... when there are dependent views