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?.

-- proper design
CREATE TABLE author (
  author_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, author    text NOT NULL
);

INSERT INTO author(author) VALUES
  ('John')
, ('Mike');

CREATE TABLE post (
  post_id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, author_id  int NOT NULL REFERENCES author
, title      varchar(30) NOT NULL
, created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO post (author_id, title, created_at) VALUES
  (1, 'Johns first post',  'January 1, 2021'),
  (1, 'Johns second post', 'January 2, 2021'),
  (1, 'Johns third post',  'January 3, 2021'),
  (2, 'Mikes first post',  'January 1, 2021'),
  (2, 'Mikes second post', 'January 2, 2021'),
  (2, 'Mikes third post',  'January 3, 2021');

CREATE INDEX ON post (author_id, created_at);

CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 6



CREATE INDEX


-- simple, smart query
SELECT p.*
FROM   author a
CROSS  JOIN LATERAL (
   SELECT *
   FROM   post
   WHERE  author_id = a.author_id
   ORDER  BY created_at DESC
   LIMIT  2
   ) p;


post_id
author_id
title
created_at


3
1
Johns third post
2021-01-03 00:00:00+00

2
1
Johns second post
2021-01-02 00:00:00+00

6
2
Mikes third post
2021-01-03 00:00:00+00

5
2
Mikes second post
2021-01-02 00:00:00+00



SELECT 4


SET enable_seqscan = off;  -- to force index if possible for small sample

SET


-- all index scans :)
explain (analyze,buffers)
SELECT p.*
FROM   author a
CROSS  JOIN LATERAL (
   SELECT *
   FROM   post
   WHERE  author_id = a.author_id
   ORDER  BY created_at DESC
   LIMIT  2
   ) p;


QUERY PLAN


Nested Loop  (cost=14.95..10413.42 rows=1270 width=94) (actual time=0.078..0.086 rows=4 loops=1)

  Buffers: shared hit=5 read=1

  ->  Bitmap Heap Scan on author a  (cost=14.82..37.52 rows=1270 width=4) (actual time=0.020..0.021 rows=2 loops=1)

        Heap Blocks: exact=1

        Buffers: shared hit=2

        ->  Bitmap Index Scan on author_pkey  (cost=0.00..14.50 rows=1270 width=0) (actual time=0.012..0.012 rows=2 loops=1)

              Buffers: shared hit=1

  ->  Limit  (cost=0.13..8.15 rows=1 width=94) (actual time=0.029..0.030 rows=2 loops=2)

        Buffers: shared hit=3 read=1

        ->  Index Scan Backward using post_author_id_created_at_idx on post  (cost=0.13..8.15 rows=1 width=94) (actual time=0.028..0.028 rows=2 loops=2)

              Index Cond: (author_id = a.author_id)

              Buffers: shared hit=3 read=1

Planning Time: 0.100 ms

Execution Time: 0.145 ms



EXPLAIN

post_id	author_id	title	created_at
3	1	Johns third post	2021-01-03 00:00:00+00
2	1	Johns second post	2021-01-02 00:00:00+00
6	2	Mikes third post	2021-01-03 00:00:00+00
5	2	Mikes second post	2021-01-02 00:00:00+00