Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE channels (
  serial_lookup BIGSERIAL NOT NULL UNIQUE,
  id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  team_id BIGINT NOT NULL
);

CREATE UNIQUE INDEX idx_channels_serial_lookup ON channels (serial_lookup, id);

CREATE TABLE posts (
  id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
  create_at TIMESTAMP DEFAULT (clock_timestamp() AT TIME ZONE 'UTC'),
  text TEXT NOT NULL,
  channel_id UUID NOT NULL REFERENCES channels (id)
);

CREATE UNIQUE INDEX idx_posts_create_at ON posts (create_at, id);

CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX


-- evenly distribute channels by team

DO $$
BEGIN
  
  FOR i IN 1..1000 LOOP
    INSERT INTO channels (name, team_id) VALUES ('channel ' || i, (i%10)+1);
  END LOOP;
 
END $$;

ANALYZE channels;

DO



ANALYZE


-- evenly distribute posts into channels; 1000 per channel

DO $$
BEGIN
  
  FOR i in 1..100000 LOOP
    INSERT INTO posts (text, channel_id) VALUES (
      'post content ' || i,
      (SELECT id FROM channels WHERE serial_lookup = (i%1000)+1)
    );
  END LOOP;

END $$;

ANALYZE posts;

DO



ANALYZE


-- hack "constants"

CREATE FUNCTION thousand_id()
  RETURNS UUID LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT id FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 999';
CREATE FUNCTION thousand_time()
  RETURNS TIMESTAMP LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT create_at FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 999';

CREATE FUNCTION ten_thousand_id()
  RETURNS UUID LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT id FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 9999';
CREATE FUNCTION ten_thousand_time()
  RETURNS TIMESTAMP LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT create_at FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 9999';

CREATE FUNCTION fifty_thousand_id()
  RETURNS UUID LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT id FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 49999';
CREATE FUNCTION fifty_thousand_time()
  RETURNS TIMESTAMP LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
  'SELECT create_at FROM posts ORDER BY create_at ASC, id ASC LIMIT 1 OFFSET 49999';

SELECT
  thousand_id(),
  thousand_time(),
  ten_thousand_id(),
  ten_thousand_time(),
  fifty_thousand_id(),
  fifty_thousand_time();

CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION



CREATE FUNCTION




thousand_id
thousand_time
ten_thousand_id
ten_thousand_time
fifty_thousand_id
fifty_thousand_time


dc275c72-3ac6-43e0-bf30-32b3d82fade7
2024-06-26 00:28:17.604446
9ba5829c-1c0a-4966-9b9d-f0debd8954dd
2024-06-26 00:28:18.593969
7a5ad8fe-98d6-420c-8608-3302994233b6
2024-06-26 00:28:23.15725



SELECT 1


-- original query

-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at > thousand_time()
--     OR
--     (posts.create_at = thousand_time() AND posts.id > thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 5;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at > thousand_time()
--     OR
--     (posts.create_at = thousand_time() AND posts.id > thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at > ten_thousand_time()
--     OR
--     (posts.create_at = ten_thousand_time() AND posts.id > ten_thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

EXPLAIN ANALYZE
SELECT posts.*, channels.team_id
  FROM posts
  LEFT JOIN channels ON posts.channel_id = channels.id
  WHERE
    posts.create_at > fifty_thousand_time()
    OR
    (posts.create_at = fifty_thousand_time() AND posts.id > fifty_thousand_id())
  ORDER BY posts.create_at ASC, posts.id ASC
  LIMIT 1000;


QUERY PLAN


Limit  (cost=0.70..168.04 rows=1000 width=66) (actual time=11.247..15.749 rows=1000 loops=1)

  ->  Nested Loop Left Join  (cost=0.70..8420.76 rows=50319 width=66) (actual time=11.246..15.599 rows=1000 loops=1)

        ->  Index Scan using idx_posts_create_at on posts  (cost=0.42..6869.42 rows=50319 width=58) (actual time=11.213..11.616 rows=1000 loops=1)

              Filter: ((create_at > '2024-06-26 00:28:23.15725'::timestamp without time zone) OR ((create_at = '2024-06-26 00:28:23.15725'::timestamp without time zone) AND (id > '7a5ad8fe-98d6-420c-8608-3302994233b6'::uuid)))

              Rows Removed by Filter: 50000

        ->  Memoize  (cost=0.29..0.30 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1000)

              Cache Key: posts.channel_id

              Cache Mode: logical

              Hits: 0  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 133kB

              ->  Index Scan using channels_pkey on channels  (cost=0.28..0.29 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=1000)

                    Index Cond: (id = posts.channel_id)

Planning Time: 43.923 ms

Execution Time: 15.875 ms



EXPLAIN


-- original query

-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at >= thousand_time()
--     AND NOT
--     (posts.create_at = thousand_time() AND posts.id <= thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 5;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at >= thousand_time()
--     AND NOT
--     (posts.create_at = thousand_time() AND posts.id <= thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE
--     posts.create_at >= ten_thousand_time()
--     AND NOT
--     (posts.create_at = ten_thousand_time() AND posts.id <= ten_thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

EXPLAIN ANALYZE
SELECT posts.*, channels.team_id
  FROM posts
  LEFT JOIN channels ON posts.channel_id = channels.id
  WHERE
    posts.create_at >= fifty_thousand_time()
    AND NOT
    (posts.create_at = fifty_thousand_time() AND posts.id <= fifty_thousand_id())
  ORDER BY posts.create_at ASC, posts.id ASC
  LIMIT 1000;


QUERY PLAN


Limit  (cost=0.70..119.96 rows=1000 width=66) (actual time=0.027..4.661 rows=1000 loops=1)

  ->  Nested Loop Left Join  (cost=0.70..6001.73 rows=50320 width=66) (actual time=0.027..4.501 rows=1000 loops=1)

        ->  Index Scan using idx_posts_create_at on posts  (cost=0.42..4450.37 rows=50320 width=58) (actual time=0.015..0.325 rows=1000 loops=1)

              Index Cond: (create_at >= '2024-06-26 00:28:23.15725'::timestamp without time zone)

              Filter: ((create_at <> '2024-06-26 00:28:23.15725'::timestamp without time zone) OR (id > '7a5ad8fe-98d6-420c-8608-3302994233b6'::uuid))

              Rows Removed by Filter: 1

        ->  Memoize  (cost=0.29..0.30 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=1000)

              Cache Key: posts.channel_id

              Cache Mode: logical

              Hits: 0  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 133kB

              ->  Index Scan using channels_pkey on channels  (cost=0.28..0.29 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=1000)

                    Index Cond: (id = posts.channel_id)

Planning Time: 20.550 ms

Execution Time: 4.768 ms



EXPLAIN


-- post's solution

-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE (posts.create_at, posts.id) > (thousand_time(), thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 5;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE (posts.create_at, posts.id) > (thousand_time(), thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts
--   LEFT JOIN channels ON posts.channel_id = channels.id
--   WHERE (posts.create_at, posts.id) > (ten_thousand_time(), ten_thousand_id())
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000;

EXPLAIN ANALYZE
SELECT posts.*, channels.team_id
  FROM posts
  LEFT JOIN channels ON posts.channel_id = channels.id
  WHERE (posts.create_at, posts.id) > (fifty_thousand_time(), fifty_thousand_id())
  ORDER BY posts.create_at ASC, posts.id ASC
  LIMIT 1000;


QUERY PLAN


Limit  (cost=0.70..114.96 rows=1000 width=66) (actual time=0.029..3.846 rows=1000 loops=1)

  ->  Nested Loop Left Join  (cost=0.70..5750.09 rows=50319 width=66) (actual time=0.028..3.653 rows=1000 loops=1)

        ->  Index Scan using idx_posts_create_at on posts  (cost=0.42..4198.75 rows=50319 width=58) (actual time=0.014..0.281 rows=1000 loops=1)

              Index Cond: (ROW(create_at, id) > ROW('2024-06-26 00:28:23.15725'::timestamp without time zone, '7a5ad8fe-98d6-420c-8608-3302994233b6'::uuid))

        ->  Memoize  (cost=0.29..0.30 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=1000)

              Cache Key: posts.channel_id

              Cache Mode: logical

              Hits: 0  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 133kB

              ->  Index Scan using channels_pkey on channels  (cost=0.28..0.29 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=1000)

                    Index Cond: (id = posts.channel_id)

Planning Time: 31.955 ms

Execution Time: 4.022 ms



EXPLAIN


-- offset solution

-- SELECT posts.*, channels.team_id
--   FROM posts LEFT JOIN channels ON posts.channel_id = channels.id
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 5
--   OFFSET 1000;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts LEFT JOIN channels ON posts.channel_id = channels.id
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000
--   OFFSET 1000;

-- EXPLAIN ANALYZE
-- SELECT posts.*, channels.team_id
--   FROM posts LEFT JOIN channels ON posts.channel_id = channels.id
--   ORDER BY posts.create_at ASC, posts.id ASC
--   LIMIT 1000
--   OFFSET 10000;

EXPLAIN ANALYZE
SELECT posts.*, channels.team_id
  FROM posts LEFT JOIN channels ON posts.channel_id = channels.id
  ORDER BY posts.create_at ASC, posts.id ASC
  LIMIT 1000
  OFFSET 50000;


QUERY PLAN


Limit  (cost=4456.04..4545.14 rows=1000 width=66) (actual time=64.081..64.952 rows=1000 loops=1)

  ->  Nested Loop Left Join  (cost=0.70..8911.37 rows=100000 width=66) (actual time=0.027..62.637 rows=51000 loops=1)

        ->  Index Scan using idx_posts_create_at on posts  (cost=0.42..6119.42 rows=100000 width=58) (actual time=0.012..18.325 rows=51000 loops=1)

        ->  Memoize  (cost=0.29..0.30 rows=1 width=24) (actual time=0.000..0.000 rows=1 loops=51000)

              Cache Key: posts.channel_id

              Cache Mode: logical

              Hits: 50000  Misses: 1000  Evictions: 0  Overflows: 0  Memory Usage: 133kB

              ->  Index Scan using channels_pkey on channels  (cost=0.28..0.29 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=1000)

                    Index Cond: (id = posts.channel_id)

Planning Time: 0.295 ms

Execution Time: 65.038 ms



EXPLAIN