Re: no partition pruning when partitioning using array type - Mailing list pgsql-hackers

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
:inputcollid0 :args ({VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1
:varoattno1 :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