Re: [HACKERS] path toward faster partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Amit Langote <amitlangote09(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Beena Emerson <memissemerson(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] path toward faster partition pruning
Date: 2018-04-02 05:18:04
Message-ID: ed7d99d0-4844-a2d8-dcfc-14ff572ce80c@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi David.

On 2018/03/31 0:55, David Rowley wrote:
> On 30 March 2018 at 18:38, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> Please find attached the updated patches.
>
> There's a bit of a strange case with v45 around prepared statements.
> I've not debugged this yet, but in case you get there first, here's
> the case:
>
> create table listp (a int, b int) partition by list (a);
> create table listp_1 partition of listp for values in(1) partition by list (b);
> create table listp_1_1 partition of listp_1 for values in(1);
> create table listp_2 partition of listp for values in(2) partition by list (b);
> create table listp_2_1 partition of listp_2 for values in(2);
>
> explain select * from listp where b in(1,2) and 2<>b and 0<>b; -- this
> one looks fine.
> QUERY PLAN
> ----------------------------------------------------------------------------
> Append (cost=0.00..49.66 rows=22 width=8)
> -> Seq Scan on listp_1_1 (cost=0.00..49.55 rows=22 width=8)
> Filter: ((b = ANY ('{1,2}'::integer[])) AND (2 <> b) AND (0 <> b))
> (3 rows)
>
> prepare q1 (int,int,int,int) as select * from listp where b in($1,$2)
> and $3 <> b and $4 <> b;
> execute q1 (1,2,3,4);
> execute q1 (1,2,3,4);
> execute q1 (1,2,3,4);
> execute q1 (1,2,3,4);
> execute q1 (1,2,3,4);
> explain (analyze, costs off, summary off, timing off) execute q1 (1,2,2,0);
> QUERY PLAN
> --------------------------------
> Result (actual rows=0 loops=1)
> One-Time Filter: false
> (2 rows)
>
> My best guess is that something ate the bits out of a Bitmapset of the
> matching partitions somewhere.

Hmm. It is the newly added inversion step that's causing this. When
creating a generic plan (that is when the planning happens via
BuildCachedPlan called with boundParams set to NULL), the presence of
Params will cause an inversion step's source step to produce
scan-all-partitions sort of result, which the inversion step dutifully
inverts to a scan-no-partitions result.

I have tried to attack that problem by handling the
no-values-to-prune-with case using a side-channel to propagate the
scan-all-partitions result through possibly multiple steps. That is, a
base pruning step will set datum_offsets in a PruneStepResult only if
pruning is carried out by actually comparing values with the partition
bounds. If no values were provided (like in the generic plan case), it
will set a scan_all_nonnull flag instead and return without setting
datum_offsets. Combine steps perform their combining duty only if
datum_offset contains a valid value, that is, if scan_all_nonnulls is not set.

Attached updated version of the patches.

Thanks,
Amit

Attachment Content-Type Size
v46-0001-Add-partsupfunc-to-PartitionSchemeData.patch text/plain 3.4 KB
v46-0002-Add-more-tests-for-partition-pruning.patch text/plain 16.7 KB
v46-0003-Faster-partition-pruning.patch text/plain 128.0 KB
v46-0004-Add-only-unpruned-partitioned-child-rels-to-part.patch text/plain 24.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-04-02 05:29:26 Re: Commit 4dba331cb3 broke ATTACH PARTITION behaviour.
Previous Message Michael Paquier 2018-04-02 04:42:36 Re: Add default role 'pg_access_server_files'