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