Re: bug with expression index on partition

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:

create table p (a int, b int);
create index on p((p.*));
explain select * from p where p > row(0,0) order by p limit 25 ;
                                QUERY PLAN                                 
───────────────────────────────────────────────────────────────────────────
 Limit  (cost=0.15..2.05 rows=25 width=40)
   ->  Index Scan using p_p_idx on p  (cost=0.15..57.33 rows=753 width=40)
         Index Cond: (p.* > '(0,0)'::record)
(3 filas)

So it would be good to fix this, but there are more pieces missing.  Or
there is some trick to enable the indexes to be used in that example --
if so I'm all ears.

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