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 users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  user_id INT,
  content TEXT,
  created_at TIMESTAMP,
  CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE index ids_notes_user_id_created_at ON notes(user_id, created_at);
INSERT INTO users(name) VALUES 
  ('Alice'),
  ('Bob'),
  ('Charlie');
INSERT INTO notes(user_id, content, created_at) VALUES
  (1, 'Alice Note 1', '2021-01-01T15:30:00Z'),
  (2, 'Bob Note 1', '2021-01-02T13:00:00Z'),
  (3, 'Charlie Note 1', '2021-01-01T10:00:00Z'),
  (1, 'Alice Note 2', '2021-02-01T11:00:00Z'),
  (2, 'Bob Note 2', '2021-01-20T12:00:00Z'),
  (1, 'Alice Note 3', '2021-03-01T13:00:00Z');
3 rows affected

6 rows affected

SELECT * FROM notes;


id
user_id
content
created_at


1
1
Alice Note 1
2021-01-01 15:30:00

2
2
Bob Note 1
2021-01-02 13:00:00

3
3
Charlie Note 1
2021-01-01 10:00:00

4
1
Alice Note 2
2021-02-01 11:00:00

5
2
Bob Note 2
2021-01-20 12:00:00

6
1
Alice Note 3
2021-03-01 13:00:00


SELECT * FROM users;


id
name


1
Alice

2
Bob

3
Charlie


SELECT latest_notes.*
FROM notes AS latest_notes
LEFT JOIN notes AS user_notes
ON latest_notes.user_id = user_notes.user_id
AND user_notes.created_at > latest_notes.created_at
WHERE user_notes.id IS NULL;


id
user_id
content
created_at


6
1
Alice Note 3
2021-03-01 13:00:00

5
2
Bob Note 2
2021-01-20 12:00:00

3
3
Charlie Note 1
2021-01-01 10:00:00


SELECT notes.*
FROM (
  SELECT user_id, MAX(created_at) AS created_at
  FROM notes
  GROUP BY notes.user_id
) AS latest_notes
INNER JOIN notes
ON notes.user_id = latest_notes.user_id
AND notes.created_at = latest_notes.created_at


id
user_id
content
created_at


3
3
Charlie Note 1
2021-01-01 10:00:00

5
2
Bob Note 2
2021-01-20 12:00:00

6
1
Alice Note 3
2021-03-01 13:00:00


EXPLAIN ANALYZE
SELECT latest_notes.*
FROM notes AS latest_notes
LEFT JOIN notes AS user_notes
ON latest_notes.user_id = user_notes.user_id
AND user_notes.created_at > latest_notes.created_at
WHERE user_notes.id IS NULL;


QUERY PLAN


Merge Left Join  (cost=0.30..231.27 rows=10 width=48) (actual time=0.039..0.045 rows=3 loops=1)

  Merge Cond: (latest_notes.user_id = user_notes.user_id)

  Join Filter: (user_notes.created_at > latest_notes.created_at)

  Rows Removed by Join Filter: 10

  Filter: (user_notes.id IS NULL)

  Rows Removed by Filter: 4

  ->  Index Scan using ids_notes_user_id_created_at on notes latest_notes  (cost=0.15..64.20 rows=1070 width=48) (actual time=0.016..0.018 rows=6 loops=1)

  ->  Materialize  (cost=0.15..66.88 rows=1070 width=16) (actual time=0.013..0.017 rows=14 loops=1)

        ->  Index Scan using ids_notes_user_id_created_at on notes user_notes  (cost=0.15..64.20 rows=1070 width=16) (actual time=0.007..0.009 rows=6 loops=1)

Planning Time: 0.111 ms

Execution Time: 0.068 ms


EXPLAIN ANALYZE
SELECT notes.*
FROM (
  SELECT user_id, MAX(created_at) AS created_at
  FROM notes
  GROUP BY notes.user_id
) AS latest_notes
INNER JOIN notes
ON notes.user_id = latest_notes.user_id
AND notes.created_at = latest_notes.created_at


QUERY PLAN


Hash Join  (cost=33.05..59.37 rows=5 width=48) (actual time=0.041..0.043 rows=3 loops=1)

  Hash Cond: ((notes.user_id = notes_1.user_id) AND (notes.created_at = (max(notes_1.created_at))))

  ->  Seq Scan on notes  (cost=0.00..20.70 rows=1070 width=48) (actual time=0.009..0.010 rows=6 loops=1)

  ->  Hash  (cost=30.05..30.05 rows=200 width=12) (actual time=0.018..0.018 rows=3 loops=1)

        Buckets: 1024  Batches: 1  Memory Usage: 9kB

        ->  HashAggregate  (cost=26.05..28.05 rows=200 width=12) (actual time=0.014..0.016 rows=3 loops=1)

              Group Key: notes_1.user_id

              Batches: 1  Memory Usage: 40kB

              ->  Seq Scan on notes notes_1  (cost=0.00..20.70 rows=1070 width=12) (actual time=0.005..0.006 rows=6 loops=1)

Planning Time: 0.157 ms

Execution Time: 0.091 ms
id	user_id	content	created_at
1	1	Alice Note 1	2021-01-01 15:30:00
2	2	Bob Note 1	2021-01-02 13:00:00
3	3	Charlie Note 1	2021-01-01 10:00:00
4	1	Alice Note 2	2021-02-01 11:00:00
5	2	Bob Note 2	2021-01-20 12:00:00
6	1	Alice Note 3	2021-03-01 13:00:00