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