TECH NOTE

GET /items?offset=20&limit=10

GET /items?page=3&size=10

GET /items?after=eyJpZCI6MTIzfQ==&limit=10

SELECT * FROM histories
WHERE create_user_id = ? AND id < ?
ORDER BY id DESC
LIMIT 10;

CREATE INDEX idx_histories_userid_id_desc
ON histories (create_user_id, id DESC);

services:
  db:
    image: postgres:17
    container_name: postgres17
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres
    ports:
      - "5432:5432"
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d:ro

$ docker compose up -d
[+] Running 2/2
 ✔ Network pagination-demo_default  Created
 ✔ Container postgres17             Started
$ psql -h localhost -U postgres -d postgres
Password for user postgres:
psql (17.4, server 17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=#
postgres-# \q

CREATE TABLE download_histories (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INTEGER        NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

$ docker compose down -v
[+] Running 2/2
 ✔ Network pagination-demo_default  Created
 ✔ Container postgres17             Started
$ psql -h localhost -U postgres -d postgres
Password for user postgres:
psql (17.4, server 17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

postgres=# \dt
               List of relations
 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
 public | download_histories | table | postgres
(1 row)

postgres=# \d download_histories
                              Table "public.download_histories"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 user_id    | integer                  |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "download_histories_pkey" PRIMARY KEY, btree (id)

postgres=# \q

-- 1. id の降順インデックス
CREATE INDEX idx_download_histories_id_desc
  ON download_histories (id DESC);

-- 2. user_id の昇順インデックス
CREATE INDEX idx_download_histories_user_id_asc
  ON download_histories (user_id ASC);

-- 3. user_id 昇順 + id 降順 の複合インデックス
CREATE INDEX idx_download_histories_user_id_id_desc
  ON download_histories (user_id ASC, id DESC);

INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100)   AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);

CREATE TABLE download_histories (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INTEGER        NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100)   AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);

postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..201.87 rows=10 width=20)
   ->  Index Scan Backward using download_histories_pkey on download_histories  (cost=0.43..363532.10 rows=18047 width=20)
         Index Cond: (id < 12345)
         Filter: (user_id = 56)
(4 rows)

-- 1. id の降順インデックス
CREATE INDEX idx_download_histories_id_desc
  ON download_histories (id DESC);

-- 2. user_id の昇順インデックス
CREATE INDEX idx_download_histories_user_id_asc
  ON download_histories (user_id ASC);

CREATE TABLE download_histories (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INTEGER        NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_download_histories_id_desc
  ON download_histories (id DESC);

CREATE INDEX idx_download_histories_user_id_asc
  ON download_histories (user_id ASC);

INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100)   AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);

postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..201.87 rows=10 width=20)
   ->  Index Scan Backward using download_histories_pkey on download_histories  (cost=0.43..363532.10 rows=18047 width=20)
         Index Cond: (id < 12345)
         Filter: (user_id = 56)
(4 rows)

CREATE TABLE download_histories (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id INTEGER        NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_download_histories_user_id_id_desc
  ON download_histories (user_id ASC, id DESC);

INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100)   AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);

postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..18.87 rows=10 width=20)
   ->  Index Scan using idx_download_histories_user_id_id_desc on download_histories  (cost=0.56..189.12 rows=103 width=20)
         Index Cond: ((user_id = 56) AND (id < 12345))
(3 rows)

INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 1000)   AS gs_users(user_id)
CROSS JOIN generate_series(1, 10000) AS gs_seq(seq);

explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;

postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..492.30 rows=13 width=20)
   ->  Index Scan Backward using download_histories_pkey on download_histories  (cost=0.43..492.30 rows=13 width=20)
         Index Cond: (id < 12345)
         Filter: (user_id = 1234)
(4 rows)

postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..2014.80 rows=100 width=20)
   ->  Index Scan Backward using download_histories_pkey on download_histories  (cost=0.43..363532.10 rows=18047 width=20)
         Index Cond: (id < 12345)
         Filter: (user_id = 1234)
(4 rows)

postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..355.46 rows=100 width=20)
   ->  Index Scan using idx_download_histories_user_id_id_desc on download_histories  (cost=0.56..64049.50 rows=18047 width=20)
         Index Cond: ((user_id = 1234) AND (id < 12345))
(3 rows)