PostgreSQL’da sekin so‘rovlarni topish: EXPLAIN ANALYZE

Qisqa javob

Sekin so‘rovlar bilan ishlash ikki qadamdan iborat:

1. Bazani haqiqatan qaysi so‘rovlar yuklayotganini topish: pg_stat_statements va sekin so‘rovlar logi.
2. Aniq so‘rovni tahlil qilish: EXPLAIN (ANALYZE, BUFFERS) rejaning qaysi tuguni vaqt sarflayotganini ko‘rsatadi va nimani tuzatish kerakligi aniq bo‘ladi.

1-qadam. Sekin so‘rovlarni topish

pg_stat_statements barcha normallashtirilgan so‘rovlar bo‘yicha statistika yig‘adi. Uni postgresql.confda yoqing, serverni qayta ishga tushiring va kengaytmani yarating:

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

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

total_exec_time bo‘yicha saralang: million marta bajariladigan 5 ms’lik so‘rov kamdan-kam ishlaydigan 3 soniyalik so‘rovdan ko‘pincha muhimroq. PostgreSQL’ning eski versiyalarida bu ustunlar total_time va mean_time deb ataladi.

Sekin so‘rovlar logi chegaradan uzoqroq davom etgan har bir so‘rovni yozadi:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

auto_explain moduli bunday so‘rovlarning rejasini ham logga yozishi mumkin — so‘rov faqat production’da sekin bo‘lganda qulay.

2-qadam. Rejani o‘qish

EXPLAIN rejani va taxminlarni ko‘rsatadi, EXPLAIN ANALYZE esa so‘rovni haqiqatan bajaradi va real vaqt hamda qatorlar sonini qo‘shadi. UPDATE va DELETE uchun uni BEGIN; ... ROLLBACK; ichiga oling.

Reja pastdan yuqoriga va ichkaridan tashqariga o‘qiladi. Asosiy tugunlar:

Nimaga qarash kerak:

• actual time — vaqt qayerda to‘planmoqda;
• taxminiy va haqiqiy rows — bir necha barobar farq statistika eskirganini yoki rejalashtiruvchi adashganini bildiradi;
• Rows Removed by Filter — behuda o‘qilgan qatorlar;
• Buffers — keshdan va diskdan o‘qilgan sahifalar.

3-qadam. Real so‘rovni tezlashtirish

«Mening buyurtmalarim» sahifasi sekin ochilmoqda:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Rejada katta Rows Removed by Filter bilan Seq Scan on orders va uning ustida Sort ko‘rinadi. Baza bitta mijozning buyurtmalarini topish uchun butun jadvalni o‘qiydi, keyin ularni saralaydi.

Yechim — ham filtrlaydigan, ham allaqachon saralangan tarkibli indeks:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at DESC);
ANALYZE orders;

EXPLAIN ANALYZE qayta ishga tushirilganda Limit ostida Index Scan using idx_orders_customer_created ko‘rinadi, Sort tuguni yo‘qoladi: baza indeksdan aynan kerakli 20 ta qatorni o‘qiydi.

Ustunlar tartibi muhim: avval tenglik shartidagi ustun (customer_id), keyin saralash yoki diapazon ustuni.

Boshqa keng tarqalgan sabablar

• Eskirgan statistika — ANALYZE bajaring, autovacuum ishlayotganini tekshiring.
• WHEREda ustun ustidagi funksiya, masalan lower(email) = ..., oddiy indeksdan foydalanmaydi — ifoda bo‘yicha indeks kerak.
• Taqqoslashda turlar mos kelmasligi indeks ishlatilishiga to‘sqinlik qiladi.
• ORM’dan N+1 so‘rovlar — har biri tez, lekin ular minglab; pg_stat_statementsda juda katta calls qiymati bilan ko‘rinadi.
• Muayyan saralash yoki hash uchun work_mem kam — rejada ish diskda bajarilayotgani ko‘rinadi.