PostgreSQL суроолорду оптималдаштыруу: EXPLAIN ANALYZE менен иштөө

Продукцияда жай суроо (slow query) - бул жөн гана колдонуучуга ыңгайсыздык эмес. Бул CPU жүктөмү, transaction lock'тор, жана акыры - башка суроолордун да жайлашы. Бир жай суроо бүтүндөй серверди тизе бүктүрүшү мүмкүн.

PostgreSQL EXPLAIN ANALYZE - жай суроону диагностикалоонун эң күчтүү инструменти. Бирок чыгарылышын туура окуй билүү керек - болбосо сандар маани бербейт.

---

Seq Scan vs Index Scan: качан кайсынысы колдонулат

Sequential Scan (Seq Scan) - PostgreSQL таблицадагы ар бир жолду тартипте окуйт. Кичинекей таблицаларда (10 000 жолдон аз) же таблицанын 15–20%дан ашыкча жолдорун кайтаруу керек болгондо бул абдан эффективдүү.

Index Scan - маалыматты B-tree индекс аркылуу табат. Аз сандагы жолдорду (таблицанын 5–10%ынан аз) кайтаруу керек болгондо тезирээк.

Bitmap Index Scan - индексти колдонуп, натыйжаларды heap'тен бир жолу окуйт. Орточо санда жол кайтарганда (5–20%) колдонулат.

PostgreSQL планировщик статистикага (pg_statistics) таянып, кайсынысын колдонуунун туура экенин өзү чечет. Бирок ал туура чечпей да калат - жана ошондо EXPLAIN ANALYZE жардамга келет.

---

EXPLAIN ANALYZE чыгарылышын кантип окуу

EXPLAIN - пландаштырылган планды көрсөтөт (суроо аткарылбайт).
EXPLAIN ANALYZE - суроону чыныгы аткарып, планды реалдуу убакыт менен салыштырат.

Биздин тест маалымат базасы: orders таблицасында 2 миллион жол.

EXPLAIN ANALYZE
SELECT o.id, o.total_amount, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Индекссиз чыгарылыш:

Limit  (cost=142857.23..142857.36 rows=50 width=52)
       (actual time=3842.112..3842.118 rows=50 loops=1)
  ->  Sort  (cost=142857.23..143730.48 rows=349300 width=52)
            (actual time=3842.110..3842.114 rows=50 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: external merge  Disk: 28672kB
        ->  Hash Join  (cost=18420.00..128744.50 rows=349300 width=52)
                       (actual time=312.443..3601.227 rows=387412 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o
                  (cost=0.00..98320.00 rows=349300 width=36)
                  (actual time=0.042..2891.334 rows=387412 loops=1)
                    Filter: ((status = 'pending') AND
                             (created_at >= '2026-01-01'))
                    Rows Removed by Filter: 1612588
              ->  Hash  (cost=11230.00..11230.00 rows=950400 width=24)
                        (actual time=298.112..298.112 rows=950400 loops=1)
                    Buckets: 131072  Batches: 16  Memory Usage: 3073kB
Planning Time: 1.234 ms
Execution Time: 3843.891 ms  ← 3.8 секунд!

Негизги маанилер:

• actual time=2891.334 - Seq Scan'да orders таблицасынын 2М жолун окуп чыгат
• Rows Removed by Filter: 1612588 - 1.6М жол чыпкаланып ташталды, пайдасыз жумуш
• Sort Method: external merge Disk: 28672kB - сорттоо дискке чыгып кетти, абдан жай
• loops=1 - бул нод бир жолу аткарылды (JOIN'да кайталанат, loops > 1 болот)

---

Индекс кошуу жана жакшыртуу

WHERE шарттарын жана ORDER BY'ды талдайлы:

-- status = 'pending' → точное совпадение (equality)
-- created_at >= '2026-01-01' → диапазон (range)
-- ORDER BY created_at DESC → ошол эле колонка

-- Composite индекс: алгач equality, анан range - бул PostgreSQL'дин эрежеси
CREATE INDEX idx_orders_status_created
  ON orders(status, created_at DESC);

-- JOIN үчүн: user_id (эгер foreign key болсо, индекс автоматтык болот,
-- бирок текшеребиз)
CREATE INDEX idx_orders_user_id ON orders(user_id);

Индекс кошкондон кийин EXPLAIN ANALYZE:

Limit  (cost=0.56..312.44 rows=50 width=52)
       (actual time=1.823..8.441 rows=50 loops=1)
  ->  Nested Loop  (cost=0.56..2187432.10 rows=350212 width=52)
                   (actual time=1.821..8.432 rows=50 loops=1)
        ->  Index Scan using idx_orders_status_created on orders o
            (cost=0.43..156823.12 rows=350212 width=36)
            (actual time=1.814..5.223 rows=50 loops=1)
              Index Cond: ((status = 'pending') AND
                           (created_at >= '2026-01-01'))
        ->  Index Scan using users_pkey on users u
            (cost=0.43..0.45 rows=1 width=24)
            (actual time=0.062..0.062 rows=1 loops=50)
              Index Cond: (id = o.user_id)
Planning Time: 2.112 ms
Execution Time: 8.673 ms  ← 8.6 мс!

Жыйынтык: 3843 мс → 8.6 мс. 443 эсе тез.

Байкаган маанилер:

• Index Scan using idx_orders_status_created - индекс колдонулду
• loops=50 - users'ка 50 жолу кирди (50 натыйжа үчүн), ар бири ~0.062 мс
• Сорттоо жок - индекс буга чейин created_at DESC тартибинде

---

WHERE'да функция колдонуу индексти жарамсыз кылат

Бул эң кеңири таралган ката. Senior иштеп чыгуучулар да кетирет.

-- БУЛ СУРОО ИНДЕКСТИ КОЛДОНБОЙТ:
WHERE DATE(created_at) = '2026-01-15'
WHERE LOWER(email) = 'user@example.com'
WHERE EXTRACT(YEAR FROM created_at) = 2026
WHERE created_at::date = '2026-01-15'

-- ТУУРА ЖАЗУУ:
-- DATE() ордуна диапазон колдон
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16'

-- LOWER() ордуна - functional index же citext
-- Functional index:
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Анда суроо индексти колдонот:
WHERE LOWER(email) = 'user@example.com'

-- Же PostgreSQL citext extension:
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;
-- Эми кадимки = оператору регистрге карабастан иштейт
WHERE email = 'user@example.com'

Эмне үчүн? Индекс колонканын баштапкы маанисин сактайт. DATE(created_at) - бул трансформация, PostgreSQL ар бир жол үчүн функцияны эсептеп, индекс менен салыштыра албайт.

---

Индекстердин түрлөрү

B-tree (демейки)

CREATE INDEX idx_name ON table(column);
-- =, <, >, <=, >=, BETWEEN, LIKE 'prefix%' үчүн иштейт
-- Эң кеңири колдонулган

GIN (Generalized Inverted Index)

-- JSONB'ни издөө үчүн:
CREATE INDEX idx_meta_gin ON products USING GIN(metadata);
-- Суроо:
WHERE metadata @> '{"category": "electronics"}'

-- Массивдерди издөө:
CREATE INDEX idx_tags_gin ON articles USING GIN(tags);
WHERE tags @> ARRAY['postgresql', 'optimization']

-- Full-text search:
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('russian', content));
WHERE to_tsvector('russian', content) @@ to_tsquery('postgresql')

Partial Index (жарым-жартылай индекс)

-- Пайдалуу: status = 'pending' суроолору көп болсо,
-- бирок жалпы жолдордун 5%ы гана 'pending'
CREATE INDEX idx_orders_pending
  ON orders(created_at DESC)
  WHERE status = 'pending';

-- Индекс кичинекей → азыраак орун, азыраак I/O
-- Бирок WHERE статусу шартта болушу КЕРЕК:
WHERE status = 'pending' AND created_at > ...  -- ✓ индекс иштейт
WHERE created_at > ...                          -- ✗ иштебейт

---

Реалдуу жай суроону оптималдаштыруунун мисалы: кадам-кадам

Проблема: Кардарыбыздын e-commerce тиркемесинде /admin/orders беттери 12 секунд жүктөлөт.

Кадам 1: Жай суроону табуу

-- pg_stat_statements extension'ди активдештириңиз:
-- postgresql.conf'та: shared_preload_libraries = 'pg_stat_statements'

SELECT query,
       calls,
       mean_exec_time,
       total_exec_time,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Кадам 2: EXPLAIN ANALYZE менен текшерүү

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
  o.id,
  o.status,
  o.total_amount,
  o.created_at,
  json_build_object('id', u.id, 'name', u.full_name, 'email', u.email) AS customer,
  COUNT(oi.id) AS items_count
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.shop_id = 42
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.id, u.id
ORDER BY o.created_at DESC
LIMIT 25 OFFSET 0;

Кадам 3: Маселени аныктоо

EXPLAIN чыгарылышында: Seq Scan on orders (rows=1847392) - shop_id = 42 боюнча индекс жок.

Кадам 4: Чечим

-- shop_id + created_at composite индекс
CREATE INDEX idx_orders_shop_created
  ON orders(shop_id, created_at DESC);

-- order_items'та order_id индекси (эгер жок болсо)
CREATE INDEX idx_order_items_order_id
  ON order_items(order_id);

Натыйжа: 12 000 мс → 28 мс.

---

Кеңеш: pg_explain.depesz.com

EXPLAIN ANALYZE чыгарылышы татаал болгондо - explain.depesz.com сайтына чаптаңыз. Ал эң жай нодду бөлүп көрсөтүп, визуалдык анализ берет.

---

Aunimeda - PostgreSQL оптималдаштыруу, backend архитектурасы жана production'готовый тиркемелер иштеп чыгуу.

Долбооруңузду талкуулайлы же WhatsApp аркылуу жазыңыз.