Re: no partition pruning when partitioning using array type

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: no partition pruning when partitioning using array type
Date: 2018-07-09 02:48:32
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Thanks for taking a look.

On 2018/07/07 9:19, Alvaro Herrera wrote:
> On 2018-May-08, Amit Langote wrote:
>> I would like to revisit this as a bug fix for get_partition_operator() to
>> be applied to both PG 10 and HEAD. In the former case, it fixes the bug
>> that constraint exclusion code will fail to prune correctly when partition
>> key is of array, enum, range, or record type due to the structural
>> mismatch between the OpExpr that partitioning code generates and one that
>> the parser generates for WHERE clauses involving partition key columns.
> Interesting patchset. Didn't read your previous v2, v3 versions; I only
> checked your latest, v1 (???).

Sorry, I think I messed up version numbering there.

> I'm wondering about the choice of OIDs in the new test. I wonder if
> it's possible to get ANYNONARRAY (or others) by way of having a
> polymorphic function that passes its polymorphic argument in a qual. I
> suppose it won't do anything in v10, or will it? Worth checking :-)> Why not use IsPolymorphicType?

Hmm, so IsPolymorphicType() test covers all of these pseudo-types except
RECORDOID. I rewrote the patch to use IsPolymorphicType.

I'm not able to think of a case where the partition constraint expression
would have to contain ANYNONARRAY though.

> Also, I think it'd be good to have tests
> for all these cases (even in v10), just to make sure we don't break it
> going forward.

So, I had proposed this patch in last December, because partition pruning
using constraint exclusion was broken for these types and still is in PG
10. I have added the tests back in the patch for PG 10 to test that
partition pruning (using constraint exclusion) works for these cases. For
PG 11 and HEAD, we took care of that in e5dcbb88a15 (Rework code to
determine partition pruning procedure), so there does not appear to be any
need to add tests for pruning there.

> At least the array case is clearly broken today ...
> A test for the RECORDOID case would be particularly welcome, since it's
> somehow different from the other cases. (I didn't understand why did
> you remove the test in the latest version.)

I have added the tests in the patch for PG 10.

I have marked both patches as v5. One patch is for PG 10 and the other
applies unchanged to both HEAD and PG 11 branches.


Attachment Content-Type Size
PG10-v5-0001-Fix-how-get_partition_operator-looks-up-the-opera.patch text/plain 10.9 KB
PG11-HEAD-v5-0001-Fix-how-get_partition_operator-looks-up-the-opera.patch text/plain 4.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Imai, Yoshikazu 2018-07-09 03:18:53 RE: Locking B-tree leafs immediately in exclusive mode
Previous Message Michael Paquier 2018-07-09 02:24:09 Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query