Re: bug with expression index on partition

On 2018/06/23 6:51, Alvaro Herrera wrote:
> On 2018-Jun-21, Amit Langote wrote:
>
>> explain (costs off) select p from p order by p;
>> QUERY PLAN
>> ---------------------------------------
>> Merge Append
>> Sort Key: ((p1.*)::p)
>> -> Index Scan using p1_p_idx on p1
>> -> Index Scan using p2_p_idx on p2
>> -> Index Scan using p3_p_idx on p3
>> (5 rows)
>
> Nice, but try adding a row > operator in the where clause.
>
> I think it's clearly desirable to allow this row-based search to use indexes;
> as I recall, we mostly enable pagination of results via this kind of
> constructs. However, we're lacking planner or executor features apparently,
> because a query using a row > operator does not use indexes:
>
> create table partp (a int, b int) partition by range (a);
> create table partp1 partition of partp for values from (0) to (35);
> create table partp2 partition of partp for values from (35) to (100);
> create index on partp1 ((partp1.*));
> create index on partp2 ((partp2.*));
> explain select * from partp where partp > row(0,0) order by partp limit 25 ;
> QUERY PLAN
> ──────────────────────────────────────────────────────────────────────────
> Limit (cost=6.69..6.75 rows=25 width=40)
> -> Sort (cost=6.69..6.86 rows=66 width=40)
> Sort Key: ((partp1.*)::partp)
> -> Append (cost=0.00..4.83 rows=66 width=40)
> -> Seq Scan on partp1 (cost=0.00..1.88 rows=23 width=40)
> Filter: ((partp1.*)::partp > '(0,0)'::record)
> -> Seq Scan on partp2 (cost=0.00..2.62 rows=43 width=40)
> Filter: ((partp2.*)::partp > '(0,0)'::record)
> (8 filas)
>
> Note the indexes are ignored, as opposed to what it does in a non-partitioned
> table:

Ah, yes.

IIUC, that happens because any whole-row Vars in WHERE quals and
EquivalenceClass expressions corresponding to child relations each has a
ConvertRowtypeExpr on top, whereas, a child index's expressions read off
pg_index doesn't contain ConvertRowtypeExpr expressions. So, WHERE quals
and/or ORDER BY expressions containing references to the parent's
whole-row Vars cannot be matched to a child index containing same
whole-row Vars.

It's a bit unfortunate that the WHERE quals and EC expressions are
transformed such that they contain ConvertRowtypeExpr nodes at a point
where they're perhaps not necessary (such as the point when a WHERE clause
or EC expression is matched with an index expression). A related
discussion is underway on a nearby thread titled "Expression errors with
"FOR UPDATE" and postgres_fdw with partition wise join enabled", so it'd
be nice if that thread concludes such that whole-row child indexes start
becoming useful.

Thanks,
Amit