Re: no partition pruning when partitioning using array type

On 2018-Jul-09, Amit Langote wrote:

> 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.

Well, I later realized that you had labelled the master version v4 and
the pg10 version v1, which made sense since you hadn't produced any
patch for pg10 before that ...

> > 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 think that's good.

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

I was about to give up trying to construct a case for this, when I
noticed this behavior (in pg10):

create or replace function f(anyelement) returns anynonarray immutable language plpgsql as $$
begin
return $1;
end;
$$;
create table pt (a int) partition by range (f(a));
create table pt1 partition of pt for values from (0) to (100);
create table pt2 partition of pt for values from (100) to (200);

and then pruning doesn't work:
alvherre=# explain select * from pt where a = 150;
QUERY PLAN
───────────────────────────────────────────────────────────
Append (cost=0.00..83.75 rows=26 width=4)
-> Seq Scan on pt1 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 150)
-> Seq Scan on pt2 (cost=0.00..41.88 rows=13 width=4)
Filter: (a = 150)
(5 filas)

The same occurs in 11 and master. I think this is because the
polymorphic type is resolved for the function ahead of time (at
table creation time); partexprs shows

({FUNCEXPR :funcid 35757 :funcresulttype 23 :funcretset false :funcvariadic false :funcformat 0 :funccollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 1 :location 46}) :location 44})

where the ":funcresulttype 23" bit indicates that the function is
returning type integer, which I find a bit odd. I think if we were to
leave it as funcresulttype anynonarray, pruning would work. Not sure
yet where is that done.

> > 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.

Right.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services