Postgres 18

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE post (  
    id         bigserial PRIMARY KEY,  --> note the bigserial
    created_on timestamp NOT NULL,  
    title      text  
);  
  
CREATE INDEX idx_post_created_on_id ON post (created_on DESC, id DESC);  
  
-- 5000 rows with duplicate timestamps (like the article)  
INSERT INTO post (created_on, title)  
SELECT '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

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)  
SELECT id, created_on  
FROM post  
WHERE (created_on, id) < ('2019-10-02 21:00:00', 4951)  
ORDER BY created_on DESC, id DESC  
FETCH FIRST 50 ROWS ONLY;

QUERY PLAN

Limit (actual rows=50.00 loops=1)

  Buffers: shared hit=39

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

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

        Heap Fetches: 50

        Index Searches: 1

        Buffers: shared hit=39

Planning:

  Buffers: shared hit=60 read=4

Planning Time: 1.925 ms

Execution Time: 0.308 ms

EXPLAIN

-- a OR doesn't push any filter to the index condition

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)  
SELECT id, created_on  
FROM post  
WHERE  created_on < '2019-10-02 21:00:00'  
   OR (created_on = '2019-10-02 21:00:00' AND id < 4951)  
ORDER BY created_on DESC, id DESC  
FETCH FIRST 50 ROWS ONLY;

QUERY PLAN

Limit (actual rows=50.00 loops=1)

  Buffers: shared hit=39

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

        Filter: ((created_on < '2019-10-02 21:00:00'::timestamp without time zone) OR ((created_on = '2019-10-02 21:00:00'::timestamp without time zone) AND (id < 4951)))

        Heap Fetches: 50

        Index Searches: 1

        Buffers: shared hit=39

Planning:

  Buffers: shared hit=3

Planning Time: 0.133 ms

Execution Time: 0.049 ms

EXPLAIN

-- except when only the date is sargable (side effect of lossy leading-column condition)

-- except when forcing type mismatch on the 2nd column:
-- only the leading column is usable as an index boundary,
-- degraded to a lossy leading-column condition

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id) < ('2019-10-02 21:00:00', 4951::numeric)  
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;

QUERY PLAN

Limit (actual rows=50.00 loops=1)

  Buffers: shared hit=39

  ->  Index Only Scan using idx_post_created_on_id on post (actual rows=50.00 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

        Index Searches: 1

        Buffers: shared hit=39

Planning:

  Buffers: shared hit=9

Planning Time: 0.288 ms

Execution Time: 0.107 ms

EXPLAIN

-- if row comparator is not possible, must use a AND to separate the conditions

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)  
SELECT id, created_on  
FROM post  
WHERE 
  created_on < '2019-10-02 21:00:00'
  AND (
       created_on < '2019-10-02 21:00:00'  
   OR (created_on = '2019-10-02 21:00:00' AND id < 4951)  
  )
ORDER BY created_on DESC, id DESC  
FETCH FIRST 50 ROWS ONLY;

QUERY PLAN

Limit (actual rows=50.00 loops=1)

  Buffers: shared hit=39

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

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

        Heap Fetches: 50

        Index Searches: 1

        Buffers: shared hit=39

Planning:

  Buffers: shared hit=3

Planning Time: 0.144 ms

Execution Time: 0.049 ms

EXPLAIN