Reply-To: jesper.pedersen@redhat.com
Subject: Re: [HACKERS] Runtime Partition Pruning
To: David Rowley <david.rowley@2ndquadrant.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>,
 Beena Emerson <memissemerson@gmail.com>,
 Alvaro Herrera <alvherre@alvh.no-ip.org>, Robert Haas
 <robertmhaas@gmail.com>, amul sul <sulamul@gmail.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>,
 Dilip Kumar <dilipbalaut@gmail.com>
References: 
 <CAKJS1f-zohKp_ANHYMe49nNje4vb81nF9s9EicK8KsjqQNkQ1A@mail.gmail.com>
 <CAOG9ApEuPL02ZCpAwxDBgkaqs1K-i4ZfVkTrpc_x8B_Eq5OtGA@mail.gmail.com>
 <CAOG9ApERiop7P=GRkqQKa82AuBKjxN3qVixie3WK4WqQpEjS6g@mail.gmail.com>
 <CAKJS1f8iop-r82tm6-1wrWwqiphpMzuSsCSFg-Ndns+Qg3ukPg@mail.gmail.com>
 <CAKJS1f_vSFZFSOW6Dspj87iU9BXvbAiVesviDZMCfYchVP+mhA@mail.gmail.com>
 <CAKcux6mf146esdNqa6Cv5RegiqGTNecN6mpoXMqnsCT7Pg5YxQ@mail.gmail.com>
 <CAKJS1f8rF+7WOu95WHH_pFu6NFh0iyF3oi+JZKGwaZbDXnJQ3A@mail.gmail.com>
 <CAKJS1f_bzRdbUutwc8iy5Bs5Krc9couEQK9NH1SUgMJvvnvWxg@mail.gmail.com>
 <31e27e19-eaf6-642f-8eb6-9cc7528fa57b@lab.ntt.co.jp>
 <CAKJS1f8+p-mXfFUiwR4xZ37STvgJeYF44yAjo5Rfxf92cFJyYQ@mail.gmail.com>
 <2dbe3de6-ad07-655f-1278-356709cbf185@lab.ntt.co.jp>
 <CAKJS1f_rPuzKvTg+uO95hepEoyx9zHe1rriC4zAO2z8ga7VgyA@mail.gmail.com>
 <CAKJS1f9xAKLew74v57zXBp4H5=g=V=TixbrnaYGLrquTD_5BRA@mail.gmail.com>
 <c3eb3284-24c8-eff0-f930-a33984d1168a@redhat.com>
 <CAKJS1f-A7wMdDZEyAQU=jY-AUgi1iGfmrmPXirV39sKdJrDu2A@mail.gmail.com>
From: Jesper Pedersen <jesper.pedersen@redhat.com>
Organization: Red Hat
Message-ID: <95dec133-7654-ce27-a7af-c91b0e58ad27@redhat.com>
Date: Fri, 9 Mar 2018 13:50:54 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CAKJS1f-A7wMdDZEyAQU=jY-AUgi1iGfmrmPXirV39sKdJrDu2A@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi David,

On 03/01/2018 08:29 PM, David Rowley wrote:
>> 0004 fails "make check-world" due to
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 670; 1259 49954 TABLE
>> boolp_f jpedersen
>> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at
>> or near "false"
>> LINE 24: ..." ATTACH PARTITION "public"."boolp_f" FOR VALUES IN (false);
> 
> The tests seem to have stumbled on a pg_dump bug which causes it to
> produce syntax that's not valid (currently)
> 
> I should be able to stop my patch failing the test by dropping that
> table, which I should have been doing anyway.
>

I've added that thread to the Open Items list.

> Thanks for the review and in advance for the future review.
> 
> I'll delay releasing a new patch as there's some discussion over on
> the faster partition pruning thread which affects this too [1]
> 
> [1] https://www.postgresql.org/message-id/CA+Tgmoa4D1c4roj7L8cx8gkkeBWAZD=MTcXKxTwBnsLRHD3rig@mail.gmail.com
> 

Sure, 0003-0005 depends on that thread. 0002 is refactoring so that one 
is ready.

In 0004 should we add a HASH based test case,

-- test.sql --
-- verify pruning in hash partitions
create table hashp (a int primary key, b int) partition by hash (a);
create table hashp_0 partition of hashp for values with (modulus 2, 
remainder 0);
create table hashp_1 partition of hashp for values with (modulus 2, 
remainder 1);
insert into hashp values (0,0), (1,1), (2,2), (3,3);
prepare q1 (int) as select * from hashp where a = $1;
execute q1 (1);
execute q1 (1);
execute q1 (1);
execute q1 (1);
execute q1 (1);
explain (analyze, costs off, summary off, timing off)  execute q1 (1);
explain (analyze, costs off, summary off, timing off)  execute q1 (3);
deallocate q1;
drop table hashp;
-- test.sql --

Also, should 0004 consider the "Parallel Append" case, aka

-- parallel.sql --
CREATE TABLE t1 (
     a integer NOT NULL,
     b integer NOT NULL
) PARTITION BY HASH (b);

CREATE TABLE t1_p00 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 0);
CREATE TABLE t1_p01 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 1);
CREATE TABLE t1_p02 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 2);
CREATE TABLE t1_p03 PARTITION OF t1 FOR VALUES WITH (MODULUS 4, 
REMAINDER 3);
INSERT INTO t1 (SELECT i, i FROM generate_series(1, 1000000) AS i);
PREPARE q1 (int) AS SELECT * FROM t1 WHERE a = $1;
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXECUTE q1 (5432);
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)  EXECUTE q1 (5432);
DEALLOCATE q1;
DROP TABLE t1;
-- parallel.sql --

Best regards,
  Jesper