Индексы: как ускорить запрос и не замедлить запись

docker run -d --rm --name kb-pg-indeksy -e POSTGRES_PASSWORD=test postgres:17-alpine
docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 01-seed.sql

-- Учебная таблица заказов: 1 000 000 строк, данные воспроизводимы (setseed)
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id integer       NOT NULL,
  status      text          NOT NULL,
  email       text          NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz   NOT NULL,
  attrs       jsonb         NOT NULL DEFAULT '{}'
);

SELECT setseed(0.42);

INSERT INTO orders (customer_id, status, email, total, created_at, attrs)
SELECT c,
       CASE WHEN r < 0.01 THEN 'new'          -- 1 % заказов ждут обработки
            WHEN r < 0.05 THEN 'cancelled'
            ELSE 'done' END,
       'User' || c || '@Example.com',          -- смешанный регистр, как вводят люди
       round((500 + random() * 49500)::numeric, 2),
       timestamptz '2025-01-01' + random() * interval '365 days',
       jsonb_build_object('channel', (ARRAY['site','app','phone','marketplace'])[1 + floor(random() * 4)::int])
         || CASE WHEN random() < 0.005 THEN '{"promo": "AUTUMN25"}'::jsonb ELSE '{}'::jsonb END
FROM (SELECT 1 + floor(random() * 50000)::int AS c, random() AS r
      FROM generate_series(1, 1000000)) AS g;

ANALYZE orders;

SELECT count(*) AS rows,
       pg_size_pretty(pg_relation_size('orders')) AS table_size
FROM orders;

SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;

$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 01-seed.sql
INSERT 0 1000000
ANALYZE
  rows   | table_size
---------+------------
 1000000 | 108 MB
(1 row)

  status   | count
-----------+--------
 done      | 949989
 cancelled |  39986
 new       |  10025
(3 rows)

-- Последние 10 заказов клиента: типичный запрос личного кабинета
\echo '=== ДО: индекса нет'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;

CREATE INDEX orders_customer_idx ON orders (customer_id);

\echo '=== Индекс по customer_id'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;

-- Составной индекс: равенство первым, сортировка вторым
CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);
DROP INDEX orders_customer_idx;   -- стал лишним: составной начинается с того же столбца

\echo '=== Составной индекс (customer_id, created_at)'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4242
ORDER BY created_at DESC
LIMIT 10;

\echo '=== Тот же индекс, но условие только по второму столбцу'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*)
FROM orders
WHERE created_at >= '2025-12-31';

$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 02-btree.sql
=== ДО: индекса нет
 Limit (actual time=25.159..26.615 rows=10 loops=1)
   Buffers: shared hit=13832 read=107
   ->  Gather Merge (actual time=25.158..26.613 rows=10 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=13832 read=107
         ->  Sort (actual time=12.695..12.696 rows=4 loops=3)
               Sort Key: created_at DESC
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=13832 read=107
               ->  Parallel Seq Scan on orders (actual time=0.758..12.594 rows=5 loops=3)
                     Filter: (customer_id = 4242)
                     Rows Removed by Filter: 333328
                     Buffers: shared hit=13758 read=107
 Planning Time: 0.462 ms
 Execution Time: 26.669 ms

=== Индекс по customer_id
 Limit (actual time=0.056..0.057 rows=10 loops=1)
   Buffers: shared hit=16 read=3
   ->  Sort (actual time=0.055..0.056 rows=10 loops=1)
         Sort Key: created_at DESC
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=16 read=3
         ->  Bitmap Heap Scan on orders (actual time=0.022..0.044 rows=16 loops=1)
               Recheck Cond: (customer_id = 4242)
               Heap Blocks: exact=16
               Buffers: shared hit=16 read=3
               ->  Bitmap Index Scan on orders_customer_idx (actual time=0.016..0.016 rows=16 loops=1)
                     Index Cond: (customer_id = 4242)
                     Buffers: shared read=3
 Planning Time: 0.186 ms
 Execution Time: 0.082 ms

=== Составной индекс (customer_id, created_at)
 Limit (actual time=0.026..0.049 rows=10 loops=1)
   Buffers: shared hit=10 read=3
   ->  Index Scan Backward using orders_customer_created_idx on orders (actual time=0.025..0.047 rows=10 loops=1)
         Index Cond: (customer_id = 4242)
         Buffers: shared hit=10 read=3
 Planning Time: 0.284 ms
 Execution Time: 0.068 ms

=== Тот же индекс, но условие только по второму столбцу
 Finalize Aggregate (actual time=35.394..39.077 rows=1 loops=1)
   Buffers: shared hit=13854 read=11
   ->  Gather (actual time=35.258..39.068 rows=3 loops=1)
         Buffers: shared hit=13854 read=11
         ->  Partial Aggregate (actual time=28.456..28.458 rows=1 loops=3)
               Buffers: shared hit=13854 read=11
               ->  Parallel Seq Scan on orders (actual time=0.070..28.323 rows=954 loops=3)
                     Filter: (created_at >= '2025-12-31 00:00:00+00'::timestamp with time zone)
                     Rows Removed by Filter: 332379
                     Buffers: shared hit=13854 read=11
 Planning Time: 0.131 ms
 Execution Time: 39.207 ms

-- 1. Частичный индекс: очередь новых заказов (1 % таблицы)
\echo '=== Очередь new: ДО'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'new'
ORDER BY created_at
LIMIT 50;

CREATE INDEX orders_status_created_idx ON orders (status, created_at);           -- обычный, для сравнения
CREATE INDEX orders_new_created_idx    ON orders (created_at) WHERE status = 'new'; -- частичный

SELECT indexrelid::regclass AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

DROP INDEX orders_status_created_idx;   -- оставляем только частичный

\echo '=== Очередь new: частичный индекс'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id, customer_id, created_at
FROM orders
WHERE status = 'new'
ORDER BY created_at
LIMIT 50;

-- 2. Индекс по выражению: поиск по email без учёта регистра
\echo '=== lower(email): ДО'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';

CREATE INDEX orders_email_lower_idx ON orders (lower(email));

\echo '=== lower(email): индекс по выражению'
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';

\echo '=== LIKE по префиксу: обычный класс операторов не подходит (локаль не C)'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) LIKE 'user4242%';

DROP INDEX orders_email_lower_idx;
CREATE INDEX orders_email_lower_idx ON orders (lower(email) text_pattern_ops);

\echo '=== LIKE по префиксу: text_pattern_ops'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) LIKE 'user4242%';

\echo '=== И равенство тоже работает с text_pattern_ops'
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE lower(email) = 'user4242@example.com';

$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 03-partial-expression.sql
         index_name          |  size
-----------------------------+--------
 orders_status_created_idx   | 30 MB
 orders_customer_created_idx | 30 MB
 orders_pkey                 | 21 MB
 orders_new_created_idx      | 240 kB
(4 rows)

=== Очередь new: частичный индекс
 Limit (actual time=0.021..0.092 rows=50 loops=1)
   Buffers: shared hit=50 read=2
   ->  Index Scan using orders_new_created_idx on orders (actual time=0.020..0.088 rows=50 loops=1)
         Buffers: shared hit=50 read=2
 Planning Time: 0.156 ms
 Execution Time: 0.107 ms

=== LIKE по префиксу: обычный класс операторов не подходит (локаль не C)
 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on orders
         Filter: (lower(email) ~~ 'user4242%'::text)

=== LIKE по префиксу: text_pattern_ops
 Bitmap Heap Scan on orders
   Filter: (lower(email) ~~ 'user4242%'::text)
   ->  Bitmap Index Scan on orders_email_lower_idx
         Index Cond: ((lower(email) ~>=~ 'user4242'::text) AND (lower(email) ~<~ 'user4243'::text))

-- GIN для jsonb: заказы с промокодом (0,5 % строк)
\echo '=== jsonb @>: ДО'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE attrs @> '{"promo": "AUTUMN25"}';

CREATE INDEX orders_attrs_gin ON orders USING gin (attrs jsonb_path_ops);

\echo '=== jsonb @>: GIN jsonb_path_ops'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE attrs @> '{"promo": "AUTUMN25"}';

\echo '=== Тот же смысл через ->> — GIN не используется'
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE attrs ->> 'promo' = 'AUTUMN25';

-- GIN для полнотекстового поиска
DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body   text NOT NULL,
  search tsvector GENERATED ALWAYS AS (to_tsvector('russian', body)) STORED
);

SELECT setseed(0.7);
INSERT INTO reviews (body)
SELECT (ARRAY['Быстрая доставка курьером', 'Курьер опоздал на час', 'Отличный кофе и выпечка',
              'Заказ собрали неправильно', 'Удобное приложение', 'Доставили холодным'])[1 + floor(random() * 6)::int]
       || ', заказ №' || n
FROM generate_series(1, 200000) AS n;

CREATE INDEX reviews_search_gin ON reviews USING gin (search);
ANALYZE reviews;

\echo '=== Полнотекстовый поиск: GIN по tsvector'
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM reviews
WHERE search @@ websearch_to_tsquery('russian', 'курьер опоздал');

$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 04-gin.sql
=== jsonb @>: ДО
 Finalize Aggregate (actual time=56.271..59.589 rows=1 loops=1)
   ->  Gather (actual time=56.067..59.579 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate (actual time=53.548..53.549 rows=1 loops=3)
               ->  Parallel Seq Scan on orders (actual time=0.040..53.413 rows=1643 loops=3)
                     Filter: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
                     Rows Removed by Filter: 331690
 Planning Time: 0.777 ms
 Execution Time: 59.708 ms

=== jsonb @>: GIN jsonb_path_ops
 Aggregate (actual time=4.536..4.537 rows=1 loops=1)
   ->  Bitmap Heap Scan on orders (actual time=1.073..4.394 rows=4930 loops=1)
         Recheck Cond: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
         Heap Blocks: exact=4107
         ->  Bitmap Index Scan on orders_attrs_gin (actual time=0.701..0.701 rows=4930 loops=1)
               Index Cond: (attrs @> '{"promo": "AUTUMN25"}'::jsonb)
 Planning Time: 0.204 ms
 Execution Time: 4.584 ms

=== Тот же смысл через ->> — GIN не используется
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on orders
                     Filter: ((attrs ->> 'promo'::text) = 'AUTUMN25'::text)

-- Цена индексов для записи: одна и та же пачка из 200 000 строк
-- в таблицу только с первичным ключом и в orders со всеми индексами
CREATE TEMP TABLE batch AS
SELECT customer_id, status, email, total, created_at, attrs
FROM orders
ORDER BY random()
LIMIT 200000;

DROP TABLE IF EXISTS orders_pk_only;
CREATE TABLE orders_pk_only (LIKE orders INCLUDING DEFAULTS INCLUDING IDENTITY);
ALTER TABLE orders_pk_only ADD PRIMARY KEY (id);

CHECKPOINT;
\timing on
SELECT pg_current_wal_lsn() AS lsn0 \gset
INSERT INTO orders_pk_only (customer_id, status, email, total, created_at, attrs)
SELECT * FROM batch;
SELECT pg_current_wal_lsn() AS lsn1 \gset
INSERT INTO orders (customer_id, status, email, total, created_at, attrs)
SELECT * FROM batch;
SELECT pg_current_wal_lsn() AS lsn2 \gset
\timing off

SELECT pg_size_pretty(pg_wal_lsn_diff(:'lsn1', :'lsn0')) AS wal_pk_only,
       pg_size_pretty(pg_wal_lsn_diff(:'lsn2', :'lsn1')) AS wal_all_indexes;

-- Сколько места занимают индексы и как часто их читали
SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT pg_size_pretty(pg_table_size('orders'))   AS table_size,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes_size;

$ docker exec -i kb-pg-indeksy psql -U postgres -v ON_ERROR_STOP=1 -f - < 05-write-cost.sql
INSERT 0 200000
Time: 306.514 ms
Time: 0.194 ms
INSERT 0 200000
Time: 1665.564 ms (00:01.666)
Time: 0.573 ms
Timing is off.
 wal_pk_only | wal_all_indexes
-------------+-----------------
 40 MB       | 150 MB
(1 row)