Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table post (
  created_on timestamp(6),
  id bigint not null,
  title varchar(255),
  primary key (id)
  );
CREATE INDEX idx_post_created_on_id ON post (created_on DESC, id DESC);
INSERT INTO post (id, created_on, title)  
SELECT i, '2019-10-01'::date - (i % 100 || ' minutes')::interval, 'post ' || i  
FROM generate_series(1, 5000) i;  
ANALYZE post;

CREATE TABLE

CREATE INDEX

INSERT 0 5000

ANALYZE

-- The best in PostgreSQL: row comparator with datatype match (Index cond)
                                                   --     |
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)           --     |
SELECT id, created_on                              --     |
FROM post                                          --     | 
WHERE                                              --     v        
  (created_on, id) < (  '2019-10-02 21:00:00.0'  ,     4951  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

QUERY PLAN

Limit (actual rows=50 loops=1)

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50 loops=1)

        Index Cond: (ROW(created_on, id) < ROW('2019-10-02 21:00:00'::timestamp without time zone, 4951))

        Heap Fetches: 50

Planning Time: 0.776 ms

Execution Time: 0.162 ms

EXPLAIN

-- Index cond on prefix only with row comp. and datatype mismatch (Index Cond + Filter)
                                                   --     |
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)           --     |
SELECT id, created_on                              --     |
FROM post                                          --     | 
WHERE                                              --     v        
  (created_on, id) < (  '2019-10-02 21:00:00.0'  ,   4951.0  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

QUERY PLAN

Limit (actual rows=50 loops=1)

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50 loops=1)

        Index Cond: (created_on <= '2019-10-02 21:00:00'::timestamp without time zone)

        Filter: (ROW(created_on, (id)::numeric) < ROW('2019-10-02 21:00:00'::timestamp without time zone, 4951.0))

        Heap Fetches: 50

Planning Time: 0.131 ms

Execution Time: 0.120 ms

EXPLAIN

-- Index cond on prefix only with row comp. and datatype mismatch (Index Cond + Filter)
                                                   --     |
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)           --     |
SELECT id, created_on                              --     |
FROM post                                          --     | 
WHERE                                              --     v        
  (created_on, id) < (  '2019-10-02 21:00:00.0'  ,   4951::numeric  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

QUERY PLAN

Limit (actual rows=50 loops=1)

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50 loops=1)

        Index Cond: (created_on <= '2019-10-02 21:00:00'::timestamp without time zone)

        Filter: (ROW(created_on, (id)::numeric) < ROW('2019-10-02 21:00:00'::timestamp without time zone, '4951'::numeric))

        Heap Fetches: 50

Planning Time: 0.074 ms

Execution Time: 0.053 ms

EXPLAIN

-- Index cond on prefix only with row comp. and datatype mismatch (Index Cond + Filter)
                                                   --     |
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)           --     |
SELECT id, created_on                              --     |
FROM post                                          --     | 
WHERE                                              --     v        
  (created_on, id) < (  '2019-10-02 21:00:00.0'  ,   '4951'::numeric  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

QUERY PLAN

Limit (actual rows=50 loops=1)

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50 loops=1)

        Index Cond: (created_on <= '2019-10-02 21:00:00'::timestamp without time zone)

        Filter: (ROW(created_on, (id)::numeric) < ROW('2019-10-02 21:00:00'::timestamp without time zone, '4951'::numeric))

        Heap Fetches: 50

Planning Time: 0.050 ms

Execution Time: 0.051 ms

EXPLAIN

-- The best in PostgreSQL: row comparator with datatype match (Index cond)
                                                   --     |
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)           --     |
SELECT id, created_on                              --     |
FROM post                                          --     | 
WHERE                                              --     v        
  (created_on, id) < (  '2019-10-02 21:00:00.0'  ,   '4951'::bigint  )
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY

QUERY PLAN

Limit (actual rows=50 loops=1)

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50 loops=1)

        Index Cond: (ROW(created_on, id) < ROW('2019-10-02 21:00:00'::timestamp without time zone, '4951'::bigint))

        Heap Fetches: 50

Planning Time: 0.045 ms

Execution Time: 0.056 ms

EXPLAIN