Re: bug with expression index on partition - Mailing list pgsql-hackers

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