Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE tbl (x int, y int, z int);

INSERT INTO tbl VALUES
  (1, 3, 1)
, (3, 1, 1)
, (2, 4, 1);

CREATE INDEX ON tbl (x, y);

SET enable_seqscan = OFF;

CREATE TABLE

INSERT 0 3

CREATE INDEX

SET

EXPLAIN SELECT * FROM tbl ORDER BY x, y;  -- index used

QUERY PLAN

Index Scan using tbl_x_y_idx on tbl  (cost=0.13..12.18 rows=3 width=12)

EXPLAIN

EXPLAIN SELECT * FROM tbl ORDER BY x, y DESC;  -- index not used

QUERY PLAN

Incremental Sort  (cost=4.15..12.31 rows=3 width=12)

  Sort Key: x, y DESC

  Presorted Key: x

  ->  Index Scan using tbl_x_y_idx on tbl  (cost=0.13..12.18 rows=3 width=12)

EXPLAIN

EXPLAIN SELECT * FROM tbl WHERE x = 999 ORDER BY y;  -- index used

QUERY PLAN

Index Scan using tbl_x_y_idx on tbl  (cost=0.13..8.15 rows=1 width=12)

  Index Cond: (x = 999)

EXPLAIN

EXPLAIN SELECT * FROM tbl WHERE x = 999 ORDER BY y DESC;  -- index used

QUERY PLAN

Index Scan Backward using tbl_x_y_idx on tbl  (cost=0.13..8.15 rows=1 width=12)

  Index Cond: (x = 999)

EXPLAIN