Re: path toward faster partition pruning

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Rajkumar Raghuwanshi <rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com>
Cc: Beena Emerson <memissemerson(at)gmail(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: path toward faster partition pruning
Date: 2017-10-27 09:11:25
Message-ID: a41f1309-019f-ef8b-f6e8-c92dbdfc8207@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Thanks Rajkumar for the test case.

On 2017/10/27 17:05, Rajkumar Raghuwanshi wrote:
> while testing further this feature, I got a bug with partitions as foreign
> tables. Test case given below. Take a look.

[ ... ]

>
> --PG-HEAD
> postgres=# EXPLAIN (COSTS OFF) SELECT t1.c FROM fplt1 t1, LATERAL (SELECT
> DISTINCT t2.c FROM fplt1 t2 WHERE t2.c = t1.c ) q;
> QUERY PLAN
> --------------------------------------------------
> Nested Loop
> -> Append
> -> Foreign Scan on ftplt1_p1 t1
> -> Foreign Scan on ftplt1_p2 t1_1
> -> Unique
> -> Append
> -> Foreign Scan on ftplt1_p1 t2
> -> Foreign Scan on ftplt1_p2 t2_1
> (8 rows)
>
> --PG-HEAD +v5 patches
> postgres=# EXPLAIN (COSTS OFF) SELECT t1.c FROM fplt1 t1, LATERAL (SELECT
> DISTINCT t2.c FROM fplt1 t2 WHERE t2.c = t1.c ) q;
>
> *ERROR: invalid expression for partition key*

I looked at this and it seems the error occurs not because partitions
being foreign tables, but because the new code is wrong to assume that
Param nodes can never appear in the clauses coming from baserestrictinfo.
When trying to do the plan-time pruning for the partitioned table
appearing inside the lateral subquery, there are Params in the clauses in
baserestrictinfo that the new pruning code was unprepared to handle.
Fixed the code to instead give up on plan-time pruning in such a case.

Attached updated set of patches. In addition to fixing the above bug, it
also fixes one of the cases reported by Beena regarding default partition
pruning that I yesterday had given up on as being too difficult to
implement [1], but today found out is not that difficult to do [2].
Change summary:

0001: added some new tests
0002: no change
0003: fixed issue that Rajkumar reported (cope with Params properly)
0004: no change
0005: fix the case to prune the default partition when warranted (the
issue reported by Beena)

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/0d6096e8-7c7b-afed-71d3-dca151306626%40lab.ntt.co.jp

[2]
https://www.postgresql.org/message-id/8499324c-8a33-4be7-9d23-7e6a95e60ddf%40lab.ntt.co.jp

Attachment Content-Type Size
0001-Add-new-tests-for-partition-pruning-v6.patch text/plain 44.2 KB
0002-Planner-side-changes-for-partition-pruning-v6.patch text/plain 41.0 KB
0003-Implement-get_partitions_from_clauses-v6.patch text/plain 32.2 KB
0004-Some-interface-changes-for-partition_bound_-cmp-bsea-v6.patch text/plain 10.1 KB
0005-Implement-get_partitions_for_keys-v6.patch text/plain 21.3 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2017-10-27 09:12:27 Re: Burst in WAL size when UUID is used as PK while full_page_writes are enabled
Previous Message Simon Riggs 2017-10-27 08:55:05 MERGE SQL Statement for PG11