From: | Erik Rijkers <er(at)xs4all(dot)nl> |
---|---|
To: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
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-04-26 08:46:33 |
Message-ID: | 305d42cd2dc173ae914ca2ef7d4b9710@xs4all.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
There is somwthing wrong with indexes on child tables (and only with
higher rowcounts).
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.)
thanks,
Erik Rijkers
(Linux Centos 6.6)
---------------------------------------
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;
---------------------------------------
# output :
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 index inh_1_a_idx on inh_1 (a);
create table inh_2 partition of inh for values start ( 10000) end (
20000); create index inh_2_a_idx on inh_2 (a);
create table inh_3 partition of inh for values start ( 20000) end (
100000); 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
;
?column? | count
----------+-------
inh | 10000
inh_1 | 4999
inh_2 | 5000
inh_3 | 1
(4 rows)
explain analyze select * from inh where a between 10110 and 10115;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..17.37 rows=4 width=8) (actual time=0.023..0.023
rows=0 loops=1)
-> Seq Scan on inh (cost=0.00..0.00 rows=1 width=8) (actual
time=0.004..0.004 rows=0 loops=1)
Filter: ((a >= 10110) AND (a <= 10115))
-> Index Scan using inh_1_a_idx on inh_1 (cost=0.16..8.18 rows=1
width=8) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: ((a >= 10110) AND (a <= 10115))
-> Index Scan using inh_2_a_idx on inh_2 (cost=0.16..8.18 rows=1
width=8) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((a >= 10110) AND (a <= 10115))
-> Seq Scan on inh_3 (cost=0.00..1.01 rows=1 width=8) (actual
time=0.008..0.008 rows=0 loops=1)
Filter: ((a >= 10110) AND (a <= 10115))
Rows Removed by Filter: 1
Planning time: 0.858 ms
Execution time: 0.093 ms
(12 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Sharma | 2016-04-26 09:02:05 | Re: pg_basebackup creates a corrupt file for pg_stat_tmp and pg_replslot on a backup location |
Previous Message | Magnus Hagander | 2016-04-26 08:39:27 | Re: Typo in execProcnode.c documentation |