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?.
-- your original poor setup
CREATE TABLE posts (
  title varchar(30),
  author varchar(30),
  created_at date
);

INSERT INTO posts VALUES
  ('Johns first post', 'John', 'January 1, 2021'),
  ('Johns second post', 'John', 'January 2, 2021'),
  ('Johns third post', 'John', 'January 3, 2021'),
  ('Mikes first post', 'Mike', 'January 1, 2021'),
  ('Mikes second post', 'Mike', 'January 2, 2021'),
  ('Mikes third post', 'Mike', 'January 3, 2021');

CREATE INDEX ON posts (author DESC NULLS LAST, created_at DESC NULLS LAST);

CREATE TABLE



INSERT 0 6



CREATE INDEX


-- smart query
WITH RECURSIVE cte AS (
   (
   SELECT *
   FROM   posts
   ORDER  BY author DESC NULLS LAST, created_at DESC NULLS LAST
   LIMIT  1
   )

   UNION ALL
   SELECT p.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT *
      FROM   posts p
      WHERE  p.author < c.author  -- lateral reference
      ORDER  BY author DESC NULLS LAST, created_at DESC NULLS LAST
      LIMIT  1
      ) p
)
SELECT *, 1 AS post_num
FROM   cte

UNION ALL
SELECT p.*
FROM   cte c
CROSS  JOIN LATERAL (
   SELECT *, 2 AS post_num
   FROM   posts p
   WHERE  p.author = c.author
   AND    p.created_at < c.created_at   -- assuming no two posts with same date
   ORDER  BY created_at DESC NULLS LAST
   LIMIT  1
   ) p;


title
author
created_at
post_num


Mikes third post
Mike
2021-01-03
1

Johns third post
John
2021-01-03
1

Mikes second post
Mike
2021-01-02
2

Johns second post
John
2021-01-02
2



SELECT 4


-- simple query
SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY author ORDER BY created_at DESC NULLS LAST) AS post_num
   FROM   posts
   ) p
WHERE post_num < 3;


title
author
created_at
post_num


Johns third post
John
2021-01-03
1

Johns second post
John
2021-01-02
2

Mikes third post
Mike
2021-01-03
1

Mikes second post
Mike
2021-01-02
2



SELECT 4


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

SET


-- all index scans :)
explain (analyze,buffers)
WITH RECURSIVE cte AS (
   (
   SELECT *
   FROM   posts
   ORDER  BY author DESC NULLS LAST, created_at DESC NULLS LAST
   LIMIT  1
   )

   UNION ALL
   SELECT p.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT *
      FROM   posts p
      WHERE  p.author < c.author  -- lateral reference
      ORDER  BY author DESC NULLS LAST, created_at DESC NULLS LAST
      LIMIT  1
      ) p
)
SELECT *, 1 AS post_num
FROM   cte

UNION ALL
SELECT p.*
FROM   cte c
CROSS  JOIN LATERAL (
   SELECT *, 2 AS post_num
   FROM   posts p
   WHERE  p.author = c.author
   AND    p.created_at < c.created_at   -- assuming no two posts with same date
   ORDER  BY created_at DESC NULLS LAST
   LIMIT  1
   ) p;


QUERY PLAN


Append  (cost=423.17..1254.65 rows=202 width=164) (actual time=0.277..0.324 rows=4 loops=1)

  Buffers: shared hit=8 read=1

  CTE cte

    ->  Recursive Union  (cost=0.13..423.17 rows=101 width=160) (actual time=0.275..0.300 rows=2 loops=1)

          Buffers: shared hit=4 read=1

          ->  Limit  (cost=0.13..2.15 rows=1 width=160) (actual time=0.274..0.274 rows=1 loops=1)

                Buffers: shared hit=1 read=1

                ->  Index Scan using posts_author_created_at_idx on posts  (cost=0.13..12.22 rows=6 width=160) (actual time=0.273..0.273 rows=1 loops=1)

                      Buffers: shared hit=1 read=1

          ->  Nested Loop  (cost=0.13..41.90 rows=10 width=160) (actual time=0.010..0.011 rows=0 loops=2)

                Buffers: shared hit=3

                ->  WorkTable Scan on cte c_1  (cost=0.00..0.20 rows=10 width=78) (actual time=0.000..0.000 rows=1 loops=2)

                ->  Limit  (cost=0.13..4.15 rows=1 width=160) (actual time=0.008..0.009 rows=0 loops=2)

                      Buffers: shared hit=3

                      ->  Index Scan using posts_author_created_at_idx on posts p_1  (cost=0.13..8.17 rows=2 width=160) (actual time=0.007..0.007 rows=0 loops=2)

                            Index Cond: ((author)::text < (c_1.author)::text)

                            Buffers: shared hit=3

  ->  CTE Scan on cte  (cost=0.00..2.02 rows=101 width=164) (actual time=0.277..0.302 rows=2 loops=1)

        Buffers: shared hit=4 read=1

  ->  Nested Loop  (cost=0.13..827.44 rows=101 width=164) (actual time=0.014..0.019 rows=2 loops=1)

        Buffers: shared hit=4

        ->  CTE Scan on cte c  (cost=0.00..2.02 rows=101 width=82) (actual time=0.000..0.001 rows=2 loops=1)

        ->  Limit  (cost=0.13..8.15 rows=1 width=164) (actual time=0.008..0.008 rows=1 loops=2)

              Buffers: shared hit=4

              ->  Index Scan using posts_author_created_at_idx on posts p  (cost=0.13..8.15 rows=1 width=164) (actual time=0.007..0.007 rows=1 loops=2)

                    Index Cond: (((author)::text = (c.author)::text) AND (created_at < c.created_at))

                    Buffers: shared hit=4

Planning Time: 0.225 ms

Execution Time: 0.378 ms



EXPLAIN


-- seq scan :(
explain (analyze,buffers)
SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY author ORDER BY created_at DESC NULLS LAST) AS post_num
   FROM   posts
   ) p
WHERE post_num < 3;


QUERY PLAN


Subquery Scan on p  (cost=10000000001.14..10000000001.33 rows=2 width=168) (actual time=0.020..0.026 rows=4 loops=1)

  Filter: (p.post_num < 3)

  Rows Removed by Filter: 2

  Buffers: shared hit=1

  ->  WindowAgg  (cost=10000000001.14..10000000001.26 rows=6 width=168) (actual time=0.019..0.024 rows=6 loops=1)

        Buffers: shared hit=1

        ->  Sort  (cost=10000000001.14..10000000001.15 rows=6 width=160) (actual time=0.015..0.015 rows=6 loops=1)

              Sort Key: posts.author, posts.created_at DESC NULLS LAST

              Sort Method: quicksort  Memory: 25kB

              Buffers: shared hit=1

              ->  Seq Scan on posts  (cost=10000000000.00..10000000001.06 rows=6 width=160) (actual time=0.007..0.008 rows=6 loops=1)

                    Buffers: shared hit=1

Planning Time: 0.052 ms

Execution Time: 0.070 ms



EXPLAIN
title	author	created_at	post_num
Mikes third post	Mike	2021-01-03	1
Johns third post	John	2021-01-03	1
Mikes second post	Mike	2021-01-02	2
Johns second post	John	2021-01-02	2