Postgres 12

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 namechanges (
  id text,
  new_name text,
  change_date timestamp
);
INSERT INTO namechanges VALUES
  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11');
6 rows affected

explain (analyze, buffers) select distinct on (id) *
from namechanges
order by id, change_date desc;


QUERY PLAN


Unique  (cost=57.23..61.28 rows=200 width=72) (actual time=0.050..0.054 rows=3 loops=1)

  Buffers: shared hit=7

  ->  Sort  (cost=57.23..59.26 rows=810 width=72) (actual time=0.049..0.050 rows=6 loops=1)

        Sort Key: id, change_date DESC

        Sort Method: quicksort  Memory: 25kB

        Buffers: shared hit=7

        ->  Seq Scan on namechanges  (cost=0.00..18.10 rows=810 width=72) (actual time=0.007..0.008 rows=6 loops=1)

              Buffers: shared hit=1

Planning Time: 0.155 ms

Execution Time: 0.118 ms