PostgreSQL sekin so'rovlarni topish: pg_stat_statements, log va auto_explain

Qisqacha: EXPLAIN dan oldin qaysi so'rov muammo ekanini bilish kerak. pg_stat_statements kengaytmasi har so'rov shakli bo'yicha hisob yuritadi. U chaqiruvlar sonini, jami va o'rtacha vaqtni, sahifalarni yig'adi. log_min_duration_statement chegaradan uzun so'rovlarni logga yozadi, auto_explain esa ularning rejasini ham. pg_stat_user_tables va pg_stat_user_indexes ko'p Seq Scan bo'layotgan jadvallarni va ishlatilmayotgan indekslarni ko'rsatadi. Kanonda matn LIKE $1 2 chaqiruvda 263 ms — ro'yxat boshida.

Bu darsda

• pg_stat_statements ni yoqasiz va "eng ko'p vaqt olgan so'rovlar" ro'yxatini chiqarasiz.
• log_min_duration_statement va auto_explain bilan sekin so'rov va uning rejasini logdan olasiz.
• pg_stat_activity bilan hozir nima ishlayotganini ko'rasiz.
• Ishlatilmayotgan indeksni topasiz va uni o'chirishdan oldin nimani tekshirish kerakligini bilasiz.
• Sekin so'rovni topishdan tuzatishgacha bo'lgan ish tartibini qo'llaysiz.

Oldin bilishingiz kerak: EXPLAIN ANALYZE'ni o'qish, Statistika va planner, psql chuqur, Logging asoslari.

1. Nega bu kerak?

Oldingi darslarda biz sekin so'rovni bilardik va uni tahlil qildik. Production'da esa holat boshqacha. Telegram'dan xabar keladi: "Sayt sekin". Qaysi sahifa? Qaysi so'rov? Serverda soatiga yuz minglab so'rov ishlaydi.

Ikki xil "sekin" bor:

• Bitta og'ir so'rov — 3 soniya, kuniga 10 marta.
• Ko'p yengil so'rov — 5 ms, lekin kuniga 2 million marta. Jami 2.8 soat server vaqti!

Ikkinchisi ko'pincha xavfliroq va uni "ko'z bilan" topib bo'lmaydi. Kerak bo'lgani — statistika: qaysi so'rov jami eng ko'p vaqt oldi.

O'xshatish: oilaviy byudjet. "Pul qayerga ketyapti?" degan savolga bitta katta xarid (muzlatgich) emas, har kungi mayda xarajatlar (taksi, kofe) javob bo'lishi mumkin. Bank ko'chirmasini toifalar bo'yicha guruhlasangiz, haqiqat ko'rinadi. pg_stat_statements — so'rovlarning shunday guruhlangan ko'chirmasi.

Logging darsida ilova loglarini o'rgandik. Bu darsda bazaning o'z loglari va statistikasi.

Raqamlar kanon bazasidan (PostgreSQL 18.6, Docker, 2 mln vazifa; sxemaning 012-fayldagi holati, 62-sekin.txt); mashqlar — biz ishga tushirdik (PostgreSQL 17.6).

2. Ish tartibi

flowchart TD
  A["Shikoyat yoki monitoring:<br/>'sekin'"] --> B["pg_stat_statements:<br/>jami vaqt bo'yicha top"]
  A --> C["Log: log_min_duration_statement"]
  B --> D["So'rov shakli + haqiqiy parametr"]
  C --> D
  D --> E["EXPLAIN (ANALYZE, BUFFERS)"]
  E --> F["Tuzatish: indeks,<br/>so'rov, statistika"]
  F --> G["Qayta o'lchash"]
  G --> B

Diagrammada sikl bor: tuzatishdan keyin yana statistikaga qaytiladi. Bitta so'rov tuzalgach, ro'yxatda boshqasi birinchi o'ringa chiqadi — bu normal jarayon.

3. pg_stat_statements

3.1 Yoqish

pg_stat_statements — Postgres bilan birga keladigan kengaytma. U server ishga tushganda yuklanishi kerak, shuning uchun ikki qadam:

1. Server sozlamasi: shared_preload_libraries = 'pg_stat_statements' (va qayta ishga tushirish). Kanon konteyneri -c shared_preload_libraries=pg_stat_statements bilan yaratilgan.
2. Bazada: CREATE EXTENSION pg_stat_statements;

Birinchi qadamsiz ikkinchisi o'tadi, lekin ko'rinishdan o'qishda xato chiqadi ("pg_stat_statements must be loaded via shared_preload_libraries"). Bu xatoni kanon ko'rsatmagan — hujjat bo'yicha.

3.2 Kanon tajribasi

Kanon statistikani tozaladi, olti so'rov yubordi va natijani o'qidi:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS tozalandi;

SELECT count(*) FROM vazifalar WHERE egasi_id = 10;
SELECT count(*) FROM vazifalar WHERE egasi_id = 20;
SELECT count(*) FROM vazifalar WHERE egasi_id = 30;
SELECT count(*) FROM vazifalar WHERE matn LIKE '%review 77%';
SELECT count(*) FROM vazifalar WHERE matn LIKE '%review 78%';
SELECT count(*) FROM izohlar WHERE matn = 'Izoh 4242';

Endi hisobot:

SELECT left(query, 70) AS sorov, calls,
       round(total_exec_time::numeric, 1) AS jami_ms,
       round(mean_exec_time::numeric, 1) AS ortacha_ms, rows,
       shared_blks_hit + shared_blks_read AS sahifalar
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'katta')
  AND query ILIKE 'SELECT count%'
ORDER BY total_exec_time DESC;

Natija:

                       sorov                        | calls | jami_ms | ortacha_ms | rows | sahifalar 
----------------------------------------------------+-------+---------+------------+------+-----------
 SELECT count(*) FROM vazifalar WHERE matn LIKE $1  |     2 |   263.0 |      131.5 |    2 |     78986
 SELECT count(*) FROM izohlar WHERE matn = $1       |     1 |    22.5 |       22.5 |    1 |      4167
 SELECT count(*) FROM vazifalar WHERE egasi_id = $1 |     3 |     2.1 |        0.7 |    3 |       343
(3 rows)

Asosiy g'oya — normallashtirish (normalization): egasi_id = 10, = 20, = 30 — uch xil matn, lekin bitta shakl: egasi_id = $1, calls = 3. Shunday qilib, 2 million marta chaqirilgan "bir xil" so'rov bitta qatorda yig'iladi.

Ustunlar:

Natijani o'qiymiz:

1. matn LIKE $1 — jami 263 ms, 78 986 sahifa (butun jadval ikki marta). Bu indeks turlari darsidagi muammo: LIKE '%…%' ga pg_trgm kerak.
2. izohlar WHERE matn = $1 — 22.5 ms, 4 167 sahifa: izohlar.matn da indeks yo'q. Lekin u bir marta chaqirilgan — tuzatish kerakmi, calls ga qarab hal qilinadi.
3. egasi_id = $1 — 0.7 ms, indeks bilan. Muammo emas.

Maslahat: Ro'yxatni total_exec_time bo'yicha saralang, mean_exec_time bo'yicha emas. O'rtachasi 2 ms, lekin million marta chaqirilgan so'rov — eng katta "xarajat". O'rtacha bo'yicha saralash faqat "eng og'ir bitta so'rov" ni topadi.

Diqqat: query ustunida parametrlar $1 ga almashtirilgan — email, parol kabi qiymatlar statistikaga tushmaydi. Lekin so'rov matnining o'zi ga qiymat yopishtirilgan bo'lsa (SQL injection'ga ochiq kod, parametrli so'rovlar darsi), Postgres doimiylarni baribir $1 ga aylantiradi. Ammo bunday kodda har xil uzunlikdagi IN (…) ro'yxatlari alohida qatorlarga bo'linib ketishi mumkin.

Tekshirib ko'ring: A so'rov: calls = 50, mean_exec_time = 400 ms. B so'rov: calls = 900 000, mean_exec_time = 1.5 ms. Qaysi birini birinchi tuzatasiz?

4. Jadval va indeks statistikasi

4.1 Ko'p Seq Scan qilinadigan jadvallar

pg_stat_user_tables — har jadval bo'yicha hisoblagichlar:

SELECT relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC LIMIT 4;

Natija:

     relname      | seq_scan | seq_tup_read | idx_scan 
------------------+----------+--------------+----------
 vazifalar        |       72 |     48772924 |  2042761
 izohlar          |        6 |       773600 |        0
 teglar           |        3 |       149381 |  1899138
 foydalanuvchilar |        6 |        80000 |   369390
(4 rows)

vazifalar 72 marta to'liq o'qilgan, jami 48.8 mln qator. Bu o'quv bazasida — oldingi darslardagi tajribalar (Seq Scan misollari). Real serverda katta seq_tup_read li katta jadval — "qaysidir so'rov indekssiz ishlayapti" belgisi. izohlar da idx_scan = 0 — izohlar faqat Seq Scan bilan o'qilgan.

4.2 Ishlatilmayotgan indekslar

SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS hajm
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC LIMIT 5;

Natija:

             indexrelname              | idx_scan |  hajm   
---------------------------------------+----------+---------
 vazifalar_egasi_id_matn_key           |        0 | 169 MB
 izohlar_vazifa_id_yaratilgan_vaqt_idx |        0 | 19 MB
 vazifa_teglari_teg_id_idx             |        0 | 18 MB
 izohlar_pkey                          |        0 | 11 MB
 vazifa_tarixi_vazifa_id_vaqt_idx      |        0 | 6800 kB
(5 rows)

Ro'yxatning birinchisi — 169 MB va hech qachon ishlatilmagan. O'chiramizmi? Yo'q!

vazifalar_egasi_id_matn_key — partial unique indeks: u "faol vazifalar orasida takror matn yo'q" qoidasini kafolatlaydi. idx_scan faqat o'qish so'rovlarini sanaydi. Noyoblik tekshiruvi — har INSERT'da — bu hisobga kirmaydi. O'chirsangiz, takror vazifalar bazaga kirib keladi.

Ro'yxatdagi boshqalar ham "ishlatilmagan" emas:

• izohlar_pkey — PK, noyoblik va FK uchun kerak.
• vazifa_teglari_teg_id_idx — FK indeksi: teg o'chirilganda CASCADE uni ishlatadi (tarkibiy indeks darsida ko'rgandik). Bu bazada teg hali o'chirilmagan.
• izohlar_vazifa_id_yaratilgan_vaqt_idx — izohlar ro'yxati uchun; API hali bu bazaga so'rov yubormagan.

Xulosa: idx_scan = 0 — savol, javob emas. O'chirishdan oldin tekshiring:

1. UNIQUE yoki PK emasmi?
2. FK ustunining yagona indeksi emasmi?
3. Statistika qancha vaqtdan beri yig'ilmoqda? (pg_stat_reset() dan yoki server qayta ishga tushganidan keyin hisoblagichlar nolga qaytishi mumkin.)
4. Oyda bir marta ishlaydigan hisobot uni ishlatmaydimi?

Faqat shundan keyin — DROP INDEX CONCURRENTLY bilan (jadvalni qulflamasdan).

5. Loglar

5.1 log_min_duration_statement

Statistika — yig'ma raqam. Ba'zan aniq hodisa kerak: "soat 14:03 da qaysi so'rov 5 soniya ishladi?". Buning uchun log:

• log_min_duration_statement = 100 — 100 ms dan uzun har so'rov matni va vaqti logga yoziladi.
• 0 — hamma so'rovlar (faqat qisqa muddatga, disk to'lib ketadi).
• -1 — o'chiq (sukut).

5.2 auto_explain

auto_explain moduli sekin so'rovning rejasini ham logga yozadi — xuddi o'sha paytdagi haqiqiy parametrlar bilan. Kanon superuser sessiyasida yoqdi (chegara 10 ms):

SET log_min_duration_statement = 10;
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 10;
SET auto_explain.log_analyze = on;
SELECT count(*) FROM izohlar WHERE matn = 'Izoh 4243';

Konteyner logida (docker logs):

2026-10-24 05:43:58.169 UTC [5451] LOG:  duration: 20.451 ms  plan:
	Query Text: SELECT count(*) FROM izohlar WHERE matn = 'Izoh 4243';
	Aggregate  (cost=7771.27..7771.28 rows=1 width=8) (actual time=18.320..20.439 rows=1.00 loops=1)
	  ->  Gather  (cost=1000.00..7771.27 rows=1 width=0) (actual time=2.264..20.430 rows=1.00 loops=1)
	        Workers Planned: 2
	        Workers Launched: 2
	        ->  Parallel Seq Scan on izohlar  (cost=0.00..6771.17 rows=1 width=0) (actual time=9.170..14.354 rows=0.33 loops=3)
	              Filter: (matn = 'Izoh 4243'::text)
	              Rows Removed by Filter: 166666
2026-10-24 05:43:58.169 UTC [5451] LOG:  duration: 21.707 ms  statement: SELECT count(*) FROM izohlar WHERE matn = 'Izoh 4243';

Ikki yozuv:

1. auto_explain dan — duration: 20.451 ms plan: va to'liq reja. Endi EXPLAIN ni qo'lda takrorlash shart emas: Parallel Seq Scan, 500 000 izohdan 1 tasi topilgan.
2. log_min_duration_statement dan — duration: 21.707 ms statement: va so'rov matni.

Logdagi [5451] — server jarayoni raqami (pid), UTC — log vaqti zonasi. Ilova logidagi correlation ID bilan bog'lash uchun so'rovga izoh qo'shish odati bor (/* so'rov:abc123 */ SELECT …) — 25-qismdagi correlation ID g'oyasi.

Kanon 10 ms chegara qo'ydi — 22 ms lik so'rov logga tushdi. 100 ms chegarada bu so'rov yozilmasdi. Chegara past bo'lsa — log to'lib ketadi, baland bo'lsa — muammolar ko'rinmaydi. Odatiy boshlanish: 200–500 ms.

Diqqat: log_analyze = on — har so'rovni o'lchaydi (EXPLAIN ANALYZE kabi), bu barcha so'rovlarni sekinlashtiradi. Hujjat buni ogohlantiradi. Production'da ehtiyotkorlik bilan, auto_explain.sample_rate bilan so'rovlarning bir qismiga yoqing. Kanon buni o'lchamagan.

Logda so'rov matni — haqiqiy qiymatlar bilan ('Izoh 4243'). Agar so'rovlarda email yoki telefon bo'lsa — loglar ham shaxsiy ma'lumot. Ularni saqlash muddati va kimga ochiqligini o'ylang.

6. Hozir nima ishlayapti: pg_stat_activity

Sayt "hozir" qotib qolgan bo'lsa — pg_stat_activity:

SELECT pid, state, wait_event_type, left(query, 50) AS sorov
FROM pg_stat_activity
WHERE datname = 'katta' AND pid <> pg_backend_pid();

Natija:

 pid | state | wait_event_type | sorov 
-----+-------+-----------------+-------
(0 rows)

Kanon tajribasida bazada boshqa ulanish yo'q edi — bo'sh. Real serverda qarash kerak bo'lgan ustunlar:

idle in transaction — tranzaksiya darsidagi pool.query('BEGIN') tuzog'ining belgisi: ulanish tranzaksiyani ochib qo'yib unutgan, qatorlar qulflangan.

Osilib qolgan so'rovni to'xtatish: SELECT pg_cancel_backend(pid); (so'rovni bekor qiladi), pg_terminate_backend(pid) (ulanishni uzadi). Bu — production ma'lumotiga ta'sir qiluvchi amal, faqat sababini bilganda.

7. Node qadami: so'rovni sanash

Bu darsda loyiha kodi o'zgarmaydi. Lekin API'ni Postgres'ga ulaganda bir kichik sozlama aynan shu dars uchun qo'yilgan edi (Connection pooling darsida): hovuz application_name: "vazifalar-api" bilan ochiladi (kanondan):

return new pg.Pool({ connectionString: url,
  application_name: "vazifalar-api", types: TURLAR, ...sozlama });

Shu nom pg_stat_activity da (va log_line_prefix da %a bo'lsa — logda) ko'rinadi. Bitta serverda bir nechta ilova ishlasa, "bu ulanish kimniki?" degan savolga darhol javob. Kanon yakuniy tekshiruvida jonli server ishlab turganda so'radi (tekshiruv-64.txt):

SELECT application_name, state, count(*) FROM pg_stat_activity
WHERE application_name = 'vazifalar-api' GROUP BY 1, 2;

Natija:

 application_name | state | count
------------------+-------+-------
 vazifalar-api    | idle  |     1
(1 row)

Nima bo'ldi? 9 ta curl so'rovidan keyin hovuzda bitta ulanish qolgan va u idle — bo'sh, keyingi so'rovni kutyapti. idle in transaction yo'q: tranzaksiya yordamchisi ulanishni ochiq tranzaksiya bilan qaytarmagan (Tranzaksiya asoslari).

Kanon bazasida pg_stat_statements doim yoqilgan (shared_preload_libraries), shuning uchun API yuborgan so'rovlar ham u yerga tushadi — $1 lar bilan normallashgan holda (hujjatga ko'ra; API so'rovlari bo'yicha alohida hisobot olinmagan).

Ilova tomonida ham oddiy kuzatuv foydali: har so'rov vaqtini o'lchab, chegaradan oshganini ilova logiga yozish (logging darsi g'oyasi). Bu bazaga kirish huquqi bo'lmagan dasturchiga ham sekin so'rovni ko'rsatadi. Bunday o'ram — keyingi darsda N+1 ni aniqlashda ishlatiladi: kanon tajribasi so'rovlarni aynan shu usul bilan sanagan.

8. Ko'p uchraydigan xatolar

9. Mashqlar

Mashqlar kurs/mashqlar/26/62-sekin/ da. SQL yechimlar holat 40 bazasida tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6; server shared_preload_libraries = pg_stat_statements bilan).

1-mashq (oson): to'ldiring

• So'rovlarni shakli bo'yicha yig'adigan kengaytma —
• Chegaradan uzun so'rovlarni logga yozadigan sozlama — log_min_duration_
• Sekin so'rov rejasini logga yozadigan modul —
• Hozir ishlayotgan ulanishlar ko'rinishi — pg_stat_
• Indeks necha marta o'qilganini ko'rsatadigan ustun —

2-mashq (o'rta): normallashtirishni ko'ring

Holat 40 bazasida statistikani tozalang, vazifalar dan uch xil egasi uchun count(*) va bitta ILIKE '%bozor%' so'rovini yuboring. Keyin pg_stat_statements dan shu so'rovlarning shakli, calls va rows ini chiqaring.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS tozalandi;

SELECT count(*) FROM vazifalar WHERE egasi_id = 1;
SELECT count(*) FROM vazifalar WHERE egasi_id = 2;
SELECT count(*) FROM vazifalar WHERE egasi_id = 3;
SELECT count(*) FROM vazifalar WHERE matn ILIKE '%bozor%';

SELECT query, calls, rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database
              WHERE datname = current_database())
  AND query LIKE 'SELECT count(*) FROM vazifalar%'
ORDER BY calls DESC;

                       query                        | calls | rows 
----------------------------------------------------+-------+------
 SELECT count(*) FROM vazifalar WHERE egasi_id = $1 |     3 |    3
 SELECT count(*) FROM vazifalar WHERE matn ILIKE $1 |     1 |    1
(2 rows)

3-mashq (qiyin): ishlatilmayotgan indeks

100 000 buyurtmali jadval yarating, mijoz va holat ga indeks qo'ying. Faqat mijoz bo'yicha va id bo'yicha (indekssiz) so'rovlar yuboring. Keyin qaysi indeks ishlatilmaganini va jadvalda Seq Scan bo'lganini statistikadan ko'rsating. Ishora: statistika hisoblagichlari sessiya oxirida yoziladi — pg_stat_force_next_flush() (PostgreSQL 15+) va qayta ulanish (\c) bilan uni darhol ko'rasiz.

CREATE TABLE t62_buyurtma AS
SELECT g AS id, 1 + g % 1000 AS mijoz, g % 2 AS holat
FROM generate_series(1, 100000) g;
CREATE INDEX t62_mijoz_idx ON t62_buyurtma (mijoz);
CREATE INDEX t62_holat_idx ON t62_buyurtma (holat);
ANALYZE t62_buyurtma;

SELECT count(*) FROM t62_buyurtma WHERE mijoz = 5;
SELECT count(*) FROM t62_buyurtma WHERE mijoz = 6;
-- holat bo'yicha hech kim qidirmaydi; id bo'yicha indeks yo'q
SELECT count(*) FROM t62_buyurtma WHERE id = 500;

SELECT pg_stat_force_next_flush();
\c
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE relname = 't62_buyurtma'
ORDER BY indexrelname;

SELECT relname, seq_scan > 0 AS seq_scan_bor
FROM pg_stat_user_tables WHERE relname = 't62_buyurtma';
DROP TABLE t62_buyurtma;

 indexrelname  | idx_scan 
---------------+----------
 t62_holat_idx |        0
 t62_mijoz_idx |        2
(2 rows)

   relname    | seq_scan_bor 
--------------+--------------
 t62_buyurtma | t
(1 row)

10. Real ishda

• Monitoring tizimlari (pganalyze, Datadog, Grafana + postgres_exporter) pg_stat_statements ni har necha daqiqada o'qib, grafik chizadi — "kecha 14:00 dan beri shu so'rov 3 marta sekinlashdi". Bular sinalmagan, hujjat bo'yicha.
• Managed Postgres (AWS RDS, Supabase, Neon) da pg_stat_statements odatda tayyor yoqilgan; Supabase'da "Query Performance" sahifasi aynan shu ko'rinishni ko'rsatadi (hujjat bo'yicha).
• Indekslarni tozalash — yiliga bir-ikki marta: ishlatilmayotgan katta indekslar disk va yozish tezligini yeydi.
• Intervyuda: "Production'da sekin so'rovni qanday topasiz?", "pg_stat_statements nima beradi?", "idx_scan = 0 bo'lsa indeksni o'chirasizmi?"

Xulosa

• Avval topish, keyin tahlil: pg_stat_statements (jami vaqt bo'yicha) → EXPLAIN (ANALYZE, BUFFERS) → tuzatish → qayta o'lchash.
• pg_stat_statements so'rovlarni $1 li shaklga normallashtiradi; shared_preload_libraries talab qiladi.
• log_min_duration_statement — sekin so'rov matni, auto_explain — uning rejasi (kanonda 20 ms li Parallel Seq Scan logda ko'rindi).
• pg_stat_activity — hozirgi holat; idle in transaction — ochiq qolgan tranzaksiya belgisi.
• idx_scan = 0 — savol: UNIQUE, PK va FK indekslari o'qishsiz ham kerak (kanondagi 169 MB li indeks — noyoblik kafolati).

Keyingi dars: N+1 muammosi — bitta sahifa uchun 101 ta so'rov: kanonda 152 ms, bitta so'rov bilan esa 5 ms.

Manbalar

• PostgreSQL 18 hujjati — pg_stat_statements: https://www.postgresql.org/docs/18/pgstatstatements.html
• PostgreSQL 18 hujjati — auto_explain: https://www.postgresql.org/docs/18/auto-explain.html
• PostgreSQL 18 hujjati — Error Reporting and Logging (log_min_duration_statement): https://www.postgresql.org/docs/18/runtime-config-logging.html
• PostgreSQL 18 hujjati — The Cumulative Statistics System (pg_stat_activity, pg_stat_user_indexes, pg_stat_force_next_flush): https://www.postgresql.org/docs/18/monitoring-stats.html
• IlmHamroh kanon o'lchovi (PostgreSQL 18.6, 2 mln vazifa, 2026-10-24); mashqlar — biz ishga tushirdik (PostgreSQL 17.6, Windows 11, i5-12500H).