MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Tue, 26 Apr 2016 08:46:33 +0000
From: Erik Rijkers <er@xs4all.nl>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Robert Haas
 <robertmhaas@gmail.com>, Alexander Korotkov <a.korotkov@postgrespro.ru>,
 Amit Langote <amitlangote09@gmail.com>, Corey Huinker
 <corey.huinker@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>,
 pgsql-hackers-owner@postgresql.org
Subject: Re: Declarative partitioning
In-Reply-To: <57106F9E.3000309@lab.ntt.co.jp>
References: <55D3093C.5010800@lab.ntt.co.jp>
 <566E6C48.9080500@lab.ntt.co.jp>
 <CA+TgmoZZMfcf16YaHuhP1Vk=j8PDFeHCvfj+FJQd+eFhs+7P8A@mail.gmail.com>
 <5678ACBC.8050809@lab.ntt.co.jp> <5698CE6D.3070907@lab.ntt.co.jp>
 <CA+TgmoY9C_XxbngCd9TtbEuwhu+4oHjeHt+QSzs-6bM=oY4f-w@mail.gmail.com>
 <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>
Message-ID: <305d42cd2dc173ae914ca2ef7d4b9710@xs4all.nl>
User-Agent: XS4ALL Webmail
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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)


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers