PostgreSQL statistika va planner: ANALYZE, pg_stats va CREATE STATISTICS

Qisqacha: Planner har so'rov uchun "nechta qator chiqadi?" deb taxmin qiladi va shunga qarab reja tanlaydi. Taxmin manbai — ANALYZE yig'adigan statistika (pg_stats): har ustunning turli qiymatlari soni, eng ko'p uchraydigan qiymatlar, NULL ulushi. Statistika eskirsa yoki ustunlar bir-biriga bog'liq bo'lsa, taxmin yuzlab marta xato bo'ladi. Kanonda 200 000 yangi qatordan keyin planner 184 ta kutdi — ANALYZE dan keyin 199 794. Bog'liq ustunlar uchun CREATE STATISTICS bor: bizning sinovda taxmin 3 dan 299 ga to'g'rilandi (haqiqiy 300).

Bu darsda

• Planner taxminni qanday hisoblashini pg_stats misolida tushuntirasiz.
• ANALYZE, autovacuum va eskirgan statistika muammosini bilasiz.
• default_statistics_target va ustun darajasidagi SET STATISTICS ni ishlatasiz.
• Bog'liq ustunlar uchun CREATE STATISTICS yaratasiz va u qachon yordam bermasligini bilasiz.
• Prepared statement'ning custom va generic rejasi farqini ko'rsatasiz.

Oldin bilishingiz kerak: EXPLAIN ANALYZE'ni o'qish, Qaysi ustunga va qanday tartibda indeks, Parametrli so'rovlar va SQL injection.

1. Nega bu kerak?

Oldingi darsda "taxmin va haqiqat" ni solishtirishni o'rgandik va bunday qatorlarni ko'rdik: taxmin 186, haqiqat 416. Bu darsda "nega 186?" degan savolga javob beramiz.

Planner so'rovni bajarmasdan oldin qaror qiladi. U jadvalni o'qib "egasi 77 ning nechta vazifasi bor" ni sanamaydi — sanash so'rovning o'zidan qimmat bo'lardi. U oldindan yig'ilgan statistika ga qaraydi.

O'xshatish: avtobus haydovchisi. U har bekatda yo'lovchilarni sanamaydi, lekin tajribadan biladi: "Chorsu bekatida ertalab 40 kishi chiqadi". Shu bilan qaror qiladi: ikkinchi avtobus kerakmi yo'qmi. Bekat yonida yangi bozor ochildi-yu, haydovchi bilmasa — avtobus sig'maydi. Statistika eskirdi.

Taxmin noto'g'ri bo'lsa nima bo'ladi? Planner "10 ta qator" deb Nested Loop tanlaydi, aslida 100 000 qator chiqadi — ichki jadval 100 000 marta o'qiladi. 5 ms lik so'rov 30 soniyaga aylanadi.

Raqamlar: kanon o'lchovi (PostgreSQL 18.6, Docker, 2 mln vazifa; sxemaning 012-fayldagi holati, 61-statistika.txt) va "biz ishga tushirdik" (PostgreSQL 17.6, Windows 11, kichik sinov jadvallari).

2. pg_stats: planner nimani biladi

2.1 Jadval darajasida

SELECT relname, reltuples::bigint, relpages
FROM pg_class WHERE relname = 'vazifalar';
SHOW default_statistics_target;

Natija:

  relname  | reltuples | relpages 
-----------+-----------+----------
 vazifalar |   2000000 |    39493
(1 row)

 default_statistics_target 
---------------------------
 100
(1 row)

reltuples — taxminiy qatorlar soni, relpages — sahifalar. Bu Seq Scan narxining asosi: 39 493 sahifa.

2.2 Ustun darajasida

SELECT attname, n_distinct, null_frac, avg_width, correlation,
       left(most_common_vals::text, 60) AS mcv,
       left(most_common_freqs::text, 60) AS mcf
FROM pg_stats
WHERE tablename = 'vazifalar'
  AND attname IN ('egasi_id', 'bajarildi', 'ustuvorlik', 'muddat')
ORDER BY 1;

Natija:

  attname   | n_distinct  | null_frac | avg_width |  correlation  |                             mcv                              |                             mcf                              
------------+-------------+-----------+-----------+---------------+--------------------------------------------------------------+--------------------------------------------------------------
 bajarildi  |           2 |         0 |         1 |    0.52899563 | {t,f}                                                        | {0.5977,0.4023}
 egasi_id   |       10428 |         0 |         8 | -0.0039972365 | {927,27,976,1519,2538,3305,3472,3707,175,892,1081,1097,1526, | {0.00056666665,0.0005,0.0005,0.0005,0.0005,0.0005,0.0005,0.0
 muddat     | -0.49839997 |    0.5016 |         8 |     0.6196889 |                                                              | 
 ustuvorlik |           3 |         0 |         4 |    0.37109822 | {orta,yuqori,past}                                           | {0.49706668,0.25236666,0.25056666}
(4 rows)

Tarkibiy indeks darsida bu jadvalning bir qismini ko'rgandik. Endi hamma ustunini:

2.3 "186" qayerdan keldi

Endi WHERE egasi_id = 77 uchun taxminni o'zimiz hisoblaymiz. Planner shunday o'ylaydi:

1. 77 MCV ro'yxatidami? Ro'yxatda 100 ta qiymat (default_statistics_target = 100). 77 — yo'q.
2. Demak, 77 — "boshqa qiymatlar" dan biri. Ular qatorlarning 1 − (MCV ulushlari yig'indisi) qismini egallaydi.
3. Bu qism n_distinct − 100 ta qiymatga teng taqsimlangan deb hisoblanadi.

MCV ulushlari har biri ~0.0005, yig'indisi ~0.05. Hisob: 2 000 000 × 0.95 ÷ (10 428 − 100) ≈ 184–186. Rejadagi taxmin shu.

Haqiqat esa boshqa. Kanon katta to'plami ataylab notekis yasalgan: vazifalarning 80 % i 4 000 "faol" foydalanuvchida, 20 % i qolgan 16 000 tasida. Faol foydalanuvchida ~400 vazifa, kam faolida ~25. Planner uchun ikkalasi bir xil:

EXPLAIN SELECT * FROM vazifalar WHERE egasi_id = 5;
EXPLAIN SELECT * FROM vazifalar WHERE egasi_id = 15000;
SELECT egasi_id, count(*) FROM vazifalar
WHERE egasi_id IN (5, 15000) GROUP BY 1;

Natija:

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=104)
   Recheck Cond: (egasi_id = 5)
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0)
         Index Cond: (egasi_id = 5)
(4 rows)

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Bitmap Heap Scan on vazifalar  (cost=5.87..713.90 rows=186 width=104)
   Recheck Cond: (egasi_id = 15000)
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.82 rows=186 width=0)
         Index Cond: (egasi_id = 15000)
(4 rows)

 egasi_id | count 
----------+-------
        5 |   415
    15000 |    28
(2 rows)

4 000 faol foydalanuvchi 100 qiymatlik MCV ro'yxatiga sig'maydi. Bu yerda xato zararsiz — 28 ham, 415 ham indeks uchun mos. Lekin shu xato JOIN ichida bo'lsa (masalan, 415 qatorni 10 ta jadval bilan birlashtirish), planner noto'g'ri algoritm tanlashi mumkin.

Tekshirib ko'ring: Ustunda 3 ta qiymat bor: past 25 %, orta 50 %, yuqori 25 %. WHERE ustuvorlik = 'yuqori' uchun planner 2 mln qatordan nechta kutadi va nega aniq?

3. ANALYZE va eskirgan statistika

3.1 Statistika qachon yig'iladi

ANALYZE — jadvaldan tasodifiy namuna olib, pg_stats ni yangilaydigan buyruq. Namuna hajmi — 300 × default_statistics_target, ya'ni sukut bo'yicha 30 000 qator. 2 mln qatorli jadval uchun ham — shuning uchun tez.

Uni kim chaqiradi?

• Autovacuum — fonda ishlaydigan jarayon. Jadvalda taxminan 10 % qator o'zgarganda (autovacuum_analyze_scale_factor = 0.1 + 50 qator) ANALYZE qiladi.
• Siz — katta import yoki o'chirishdan keyin qo'lda.

3.2 Kanon tajribasi: 200 000 yangi qator

Kanon tranzaksiya ichida bitta foydalanuvchiga 200 000 vazifa qo'shdi va darhol so'radi:

BEGIN;
INSERT INTO vazifalar (egasi_id, matn, ustuvorlik)
SELECT 19999, 'yangi ' || g, 'yuqori'
FROM generate_series(1, 200000) g;

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM vazifalar WHERE egasi_id = 19999;

Natija (asosiy qatorlar):

INSERT 0 200000
 Bitmap Heap Scan on vazifalar  (cost=5.85..706.48 rows=184 width=104) (actual rows=200028.00 loops=1)
   Recheck Cond: (egasi_id = 19999)
   Heap Blocks: exact=11192
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..5.81 rows=184 width=0) (actual rows=200028.00 loops=1)
         Index Cond: (egasi_id = 19999)
 Execution Time: 106.805 ms

Taxmin 184, haqiqat 200 028 — 1 000 marta xato. ANALYZE dan keyin (o'sha tranzaksiya ichida):

ANALYZE vazifalar;
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM vazifalar WHERE egasi_id = 19999;
ROLLBACK;

Natija (asosiy qatorlar):

ANALYZE
 Bitmap Heap Scan on vazifalar  (cost=2704.83..44695.26 rows=199794 width=102) (actual rows=200028.00 loops=1)
   Recheck Cond: (egasi_id = 19999)
   Heap Blocks: exact=11192
   ->  Bitmap Index Scan on vazifalar_egasi_id_idx  (cost=0.00..2654.88 rows=199794 width=0) (actual rows=200028.00 loops=1)
         Index Cond: (egasi_id = 19999)
 Execution Time: 68.112 ms

Endi 199 794 — deyarli aniq. 19999 MCV ro'yxatiga tushdi. Reja shakli bu safar o'zgarmadi (ikkala holatda Bitmap), lekin narx (cost) 706 dan 44 695 ga oshdi — bu so'rov boshqa jadvallar bilan JOIN qilinganda planner endi boshqacha qaror qilardi.

Maslahat: Katta import (COPY, ommaviy ma'lumot darsi) yoki ko'p qatorli o'chirishdan keyin skriptning oxirida ANALYZE jadval_nomi; yozing. Autovacuum ulguguncha bo'lgan oraliqda keladigan so'rovlar to'g'ri reja oladi.

Diqqat: ANALYZE ni tranzaksiya ichida chaqirish mumkin, lekin u yangi statistikani faqat shu tranzaksiyaga ko'rsatmaydi — pg_statistic katalogi yangilanadi va COMMIT bo'lsa hammaga ko'rinadi. Kanon oxirida ROLLBACK qilib, so'ng ANALYZE ni qayta chaqirgan — statistika toza holatga qaytsin.

4. Statistikani aniqroq qilish

4.1 SET STATISTICS: ko'proq MCV

default_statistics_target — MCV ro'yxati va gistogramma qancha bo'lakdan iborat bo'lishi (sukut 100). Butun server uchun oshirsa bo'ladi, lekin har ANALYZE sekinlashadi va planner har so'rovda ko'proq raqam o'qiydi. Odatda faqat muammoli ustun uchun oshiriladi.

Biz ishga tushirdik (PostgreSQL 17.6). Kanondagidek notekis taqsimot: 200 000 qator, 80 % i 4 000 "faol" egada:

CREATE TABLE t_egasi AS
SELECT g AS id,
       CASE WHEN g % 5 > 0 THEN 1 + g % 4000
            ELSE 4001 + g % 16000 END AS egasi_id
FROM generate_series(1, 200000) g;
ANALYZE t_egasi;
EXPLAIN SELECT * FROM t_egasi WHERE egasi_id = 5;

ALTER TABLE t_egasi ALTER COLUMN egasi_id SET STATISTICS 1000;
ANALYZE t_egasi;
EXPLAIN SELECT * FROM t_egasi WHERE egasi_id = 5;
SELECT count(*) FROM t_egasi WHERE egasi_id = 5;
DROP TABLE t_egasi;

Natija:

SELECT 200000
ANALYZE
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t_egasi  (cost=0.00..3396.00 rows=32 width=8)
   Filter: (egasi_id = 5)
(2 rows)

ALTER TABLE
ANALYZE
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on t_egasi  (cost=0.00..3396.00 rows=50 width=8)
   Filter: (egasi_id = 5)
(2 rows)

 count 
-------
    50
(1 row)

Sukut bilan taxmin 32, SET STATISTICS 1000 dan keyin 50 — aniq. 1000 × 300 = 300 000 qatorlik namuna bu jadvalning hammasini qamradi. Blok nocheck: birinchi taxmin tasodifiy namunaga bog'liq va har ANALYZE da biroz farq qiladi.

4.2 Bog'liq ustunlar

Planner har shartni mustaqil deb hisoblaydi va ulushlarni ko'paytiradi. Ko'pincha bu to'g'ri emas. Kanon misoli: bajarildi va bajarilgan_vaqt IS NOT NULL — bir xil narsa (CHECK cheklovi buni talab qiladi). EXPLAIN darsida ko'rgandik: taxmin 297 705 × 3 ≈ 893 000, haqiqat ≈ 1 199 000.

Buning davosi — kengaytirilgan statistika (extended statistics): CREATE STATISTICS. U bir nechta ustunni birga o'rganadi. Uch turi bor:

Biz ishga tushirdik (PostgreSQL 17.6). Klassik misol — manzil: tuman ma'lum bo'lsa, viloyat ham ma'lum. 300 000 qator, 100 viloyat, 1 000 tuman:

CREATE TABLE t_manzil (
  id int GENERATED ALWAYS AS IDENTITY,
  viloyat text,
  tuman text
);
INSERT INTO t_manzil (viloyat, tuman)
SELECT 'viloyat ' || (g % 100), 'tuman ' || (g % 1000)
FROM generate_series(1, 300000) g;
ANALYZE t_manzil;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM t_manzil
WHERE viloyat = 'viloyat 7' AND tuman = 'tuman 107';

Natija:

CREATE TABLE
INSERT 0 300000
ANALYZE
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..5802.36 rows=3 width=23) (actual rows=300 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on t_manzil  (cost=0.00..4802.06 rows=2 width=23) (actual rows=150 loops=2)
         Filter: ((viloyat = 'viloyat 7'::text) AND (tuman = 'tuman 107'::text))
         Rows Removed by Filter: 149850
(6 rows)

Taxmin 3, haqiqat 300. Planner hisobi: 300 000 × (1/100) × (1/1000) = 3. Lekin "tuman 107" faqat "viloyat 7" da bor — ikkinchi shart birinchisini toraytirmaydi.

Endi kengaytirilgan statistika:

CREATE STATISTICS t_manzil_bog (dependencies)
  ON viloyat, tuman FROM t_manzil;
ANALYZE t_manzil;

SELECT statistics_name, dependencies
FROM pg_stats_ext WHERE statistics_name = 't_manzil_bog';

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM t_manzil
WHERE viloyat = 'viloyat 7' AND tuman = 'tuman 107';

Natija:

CREATE STATISTICS
ANALYZE
 statistics_name |     dependencies     
-----------------+----------------------
 t_manzil_bog    | {"3 => 2": 1.000000}
(1 row)

                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..5831.96 rows=299 width=23) (actual rows=300 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   ->  Parallel Seq Scan on t_manzil  (cost=0.00..4802.06 rows=176 width=23) (actual rows=150 loops=2)
         Filter: ((viloyat = 'viloyat 7'::text) AND (tuman = 'tuman 107'::text))
         Rows Removed by Filter: 149850
(6 rows)

{"3 => 2": 1.000000} — "3-ustun (tuman) 2-ustunni (viloyat) 100 % aniqlaydi". Taxmin 299 — deyarli aniq. (Uch marta ishga tushirdik: 299, 299, 298 — namuna tasodifiy, shuning uchun blok nocheck.)

4.3 Halol topilma: kanonda yordam bermadi

Kanon xuddi shuni vazifalar da sinadi — dependencies va mcv, hatto ifoda bilan:

CREATE STATISTICS t61_stat (dependencies, mcv)
  ON bajarildi, bajarilgan_vaqt FROM vazifalar;
CREATE STATISTICS t61_ifoda
  ON bajarildi, (bajarilgan_vaqt IS NOT NULL) FROM vazifalar;
ANALYZE vazifalar;
EXPLAIN (ANALYZE, TIMING OFF)
SELECT count(*) FROM vazifalar
WHERE bajarildi AND bajarilgan_vaqt IS NOT NULL;

Natija (asosiy qator):

               ->  Parallel Seq Scan on vazifalar  (cost=0.00..47828.20 rows=296080 width=0) (actual rows=399680.33 loops=3)

Taxmin 297 705 dan 296 080 ga — deyarli o'zgarmadi. Nega? PostgreSQL hujjatiga ko'ra funksional bog'liqliklar faqat oddiy tenglik shartlariga (ustun = doimiy va IN (…)) qo'llanadi. bajarildi (yalang'och mantiqiy ustun) va IS NOT NULL — boshqa shakl. Bizning t_manzil misolida ikkala shart ham = edi — shuning uchun ishladi.

Xulosa: CREATE STATISTICS — sehrli tayoqcha emas. Uni yaratgach, har doim EXPLAIN ANALYZE bilan taxmin o'zgarganini tekshiring.

Tekshirib ko'ring: Jadvalda shahar va pochta_indeksi ustunlari bor. So'rov: WHERE shahar = 'Toshkent' AND pochta_indeksi = '100000'. Taxmin haqiqatdan 50 marta kam. Nima qilasiz?

5. Prepared statement: custom va generic reja

5.1 Muammo

Parametrli so'rovlar darsida $1 bilan so'rov yuborishni o'rgandik. Postgres bunday so'rovni tayyorlab (prepare) qo'yishi mumkin — har safar qayta rejalashtirmaslik uchun. Savol: reja qaysi qiymat uchun tuziladi?

Ikki xil reja bor:

• Custom (maxsus) reja — har chaqiruvda $1 ning haqiqiy qiymati bilan tuziladi. Aniq, lekin har safar rejalashtirish vaqti ketadi.
• Generic (umumiy) reja — $1 ni bilmasdan, "o'rtacha" qiymat uchun bir marta tuziladi va qayta ishlatiladi.

Postgres birinchi 5 marta custom reja tuzadi. Keyin generic rejaning narxini custom'larning o'rtachasi bilan solishtiradi: generic arzonroq yoki teng bo'lsa — keyingi chaqiruvlarda generic ishlatiladi.

5.2 Kanon: 7 marta custom

PREPARE t61_q(int) AS
  SELECT count(*) FROM vazifalar WHERE egasi_id = $1;
EXECUTE t61_q(5); EXECUTE t61_q(5); EXECUTE t61_q(5);
EXECUTE t61_q(5); EXECUTE t61_q(5);
EXPLAIN EXECUTE t61_q(5);
EXPLAIN EXECUTE t61_q(5);
SELECT name, generic_plans, custom_plans
FROM pg_prepared_statements;

Natija (oxirgi so'rov):

 name  | generic_plans | custom_plans 
-------+---------------+--------------
 t61_q |             0 |            7
(1 row)

7 chaqiruvning hammasi custom: bu jadvalda generic reja arzonroq chiqmadi.

5.3 Biz ishga tushirdik: generic reja "ko'r"

Generic reja qachon xavfli? Qiymatlar juda notekis bo'lsa. Sinov jadvali: 300 000 qatorning 90 % i bitta egada (1), qolgani 5 000 egaga sochilgan. plan_cache_mode sozlamasi bilan generic rejani majburlaymiz:

CREATE TABLE t_notekis (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  egasi int, matn text
) WITH (autovacuum_enabled = off);
-- 90 % qator egasi 1 da, qolganlari 5 000 egaga sochilgan
INSERT INTO t_notekis (egasi, matn)
SELECT CASE WHEN g % 10 = 0 THEN g % 5000 ELSE 1 END, 'matn ' || g
FROM generate_series(1, 300000) g;
CREATE INDEX ON t_notekis (egasi);
ANALYZE t_notekis;

PREPARE q(int) AS
  SELECT count(*) FROM t_notekis WHERE egasi = $1;
EXPLAIN (COSTS OFF) EXECUTE q(1);
EXPLAIN (COSTS OFF) EXECUTE q(500);
SET plan_cache_mode = force_generic_plan;
EXPLAIN (COSTS OFF) EXECUTE q(1);
RESET plan_cache_mode;
SELECT name, generic_plans, custom_plans FROM pg_prepared_statements;
DEALLOCATE q;
DROP TABLE t_notekis;

Natija:

CREATE TABLE
INSERT 0 300000
CREATE INDEX
ANALYZE
PREPARE
                    QUERY PLAN                    
--------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Seq Scan on t_notekis
                     Filter: (egasi = 1)
(6 rows)

                          QUERY PLAN                          
--------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using t_notekis_egasi_idx on t_notekis
         Index Cond: (egasi = 500)
(3 rows)

SET
                          QUERY PLAN                          
--------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using t_notekis_egasi_idx on t_notekis
         Index Cond: (egasi = $1)
(3 rows)

RESET
 name | generic_plans | custom_plans 
------+---------------+--------------
 q    |             1 |            2
(1 row)

DEALLOCATE
DROP TABLE

Uch rejaga qarang:

1. Custom, egasi = 1 (270 000 qator) — Parallel Seq Scan: deyarli butun jadval kerak.
2. Custom, egasi = 500 (~60 qator) — Index Only Scan.
3. Generic — egasi = $1, qiymatni bilmaydi. "O'rtacha egada kam qator" deb indeks tanladi — 270 000 qatorli egasi 1 uchun ham.

Generic reja qiymatga "ko'r". Bu sinov jadvali to'liq xotirada, shuning uchun farq kichik chiqdi. Biz uch marta o'lchadik: generic reja egasi 1 uchun hatto biroz tezroq bo'ldi (43–65 ms va 53–168 ms; o'lchov tarqoq). Lekin katta, diskdagi jadvalda indeks orqali 270 000 ta sochilgan sahifani o'qish Seq Scan'dan ancha sekin bo'lishi mumkin. Shuning uchun Postgres generic rejani faqat u custom'lardan qimmat bo'lmaganda tanlaydi.

plan_cache_mode:

Maslahat: pg drayveri oddiy pool.query(text, values) da nomsiz prepared statement ishlatadi — u har safar qayta rejalashtiriladi (custom). Generic reja muammosi asosan nomlangan so'rovlarda ({ name: '…', text, values } — pg asoslari darsida) va ORM'larning prepared statement keshida uchraydi. Bu xatti-harakat node-postgres hujjatiga asoslangan, kanon alohida o'lchamagan.

6. Node qadami: import skriptiga ANALYZE

Bu darsda loyiha kodi o'zgarmaydi. Kanon katta to'plamni yasaganda (2 mln vazifa) skriptning oxirida VACUUM ANALYZE chaqirilgan — aks holda keyingi o'lchovlar eskirgan statistika bilan o'tardi. Sizning loyihangizda ham xuddi shu qoida:

• Seed yoki katta import skripti (COPY bilan) — oxirida ANALYZE.
• CREATE STATISTICS yoki SET STATISTICS kerak bo'lsa — u ham migratsiya fayliga yoziladi, xuddi indeks kabi. Bu sxemaning bir qismi.

Hozircha vazifalar da bunday ehtiyoj yo'q. Kanon API'ni Postgres'ga ulagach, PgOmbori yuborgan haqiqiy so'rovlarni katta bazada (013/014 bilan qayta qurilgan, katta-malumot.sql oxirida ham VACUUM ANALYZE;) tekshirdi (reja-64.txt). Hamma holatda egasi_id sharti rejani boshidanoq bitta egasiga toraytiradi — eng ko'p vazifali egasida ham 479 qator. Bunday kichik to'plamda taxmin 2–3 marta xato bo'lsa ham, reja o'zgarmaydi: sukut ro'yxat 0.039 ms, teg filtri bilan 1.3 ms.

Prepared statement masalasi ham API'ga tegmaydi. PgOmbori hamma so'rovni pool.query(sql, qiymatlar) bilan yuboradi — nomsiz. Kodda name: li so'rov yo'q, demak "Prepared statement: custom va generic reja" bo'limidagi generic reja tuzog'i bu loyihada uchramaydi.

7. Ko'p uchraydigan xatolar

8. Mashqlar

Mashqlar kurs/mashqlar/26/61-statistika/ da. SQL yechimlar holat 40 bazasida tekshirilgan (biz ishga tushirdik, PostgreSQL 17.6).

1-mashq (oson): to'ldiring

• Statistikani yangilaydigan buyruq —
• Eng ko'p uchraydigan qiymatlar ro'yxati — most common
• Sukut statistika maqsadi (MCV ro'yxati hajmi) —
• Bir nechta ustunni birga o'rganadigan buyruq — CREATE
• $1 qiymatini bilmasdan tuzilgan reja —

2-mashq (o'rta): eskirgan statistika

100 000 to'lovli jadval yarating (10 000 mijozga teng taqsimlangan, autovacuum o'chiq), ANALYZE qiling. Keyin bitta yirik mijoz (777) 50 000 ta to'lov qilsin. 777 MCV ro'yxatida bormi — ANALYZE dan oldin va keyin tekshiring. Ishora: most_common_vals turi anyarray, uni ::text::int[] bilan massivga aylantirish mumkin; MCV bo'sh bo'lsa — NULL.

CREATE TABLE t61_tolov (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  mijoz int, summa int
) WITH (autovacuum_enabled = off);

INSERT INTO t61_tolov (mijoz, summa)
SELECT 1 + g % 10000, g % 700 * 1000
FROM generate_series(1, 100000) g;
ANALYZE t61_tolov;

INSERT INTO t61_tolov (mijoz, summa)
SELECT 777, 5000 FROM generate_series(1, 50000);

SELECT coalesce(777 = ANY (most_common_vals::text::int[]), false)
         AS mcvda_bormi
FROM pg_stats
WHERE tablename = 't61_tolov' AND attname = 'mijoz';

ANALYZE t61_tolov;

SELECT coalesce(777 = ANY (most_common_vals::text::int[]), false)
         AS mcvda_bormi
FROM pg_stats
WHERE tablename = 't61_tolov' AND attname = 'mijoz';
DROP TABLE t61_tolov;

 mcvda_bormi 
-------------
 f
(1 row)

 mcvda_bormi 
-------------
 t
(1 row)

3-mashq (qiyin): taxminni SQL bilan o'lchang

EXPLAIN (FORMAT JSON) dan taxminni (Plan Rows) qaytaradigan vaqtinchalik funksiya yozing (pg_temp sxemasida). Uni "Bog'liq ustunlar" bo'limidagi viloyat–tuman jadvalida ishlating: CREATE STATISTICS dan oldin taxmin 30 dan kamligini, keyin 100…1000 orasida ekanini mantiqiy qiymat bilan ko'rsating. Ishora: PL/pgSQL'da EXECUTE 'EXPLAIN (FORMAT JSON) ' || sorov INTO reja;.

CREATE FUNCTION pg_temp.taxmin(sorov text) RETURNS bigint
LANGUAGE plpgsql AS $$
DECLARE reja json;
BEGIN
  EXECUTE 'EXPLAIN (FORMAT JSON) ' || sorov INTO reja;
  RETURN (reja -> 0 -> 'Plan' ->> 'Plan Rows')::bigint;
END $$;

CREATE TABLE t61_manzil AS
SELECT g AS id,
       'viloyat ' || (g % 100) AS viloyat,
       'tuman ' || (g % 1000) AS tuman
FROM generate_series(1, 300000) g;
ANALYZE t61_manzil;

SELECT count(*) AS haqiqiy,
       pg_temp.taxmin($q$SELECT * FROM t61_manzil
         WHERE viloyat = 'viloyat 7' AND tuman = 'tuman 107'$q$)
         < 30 AS taxmin_juda_kam
FROM t61_manzil
WHERE viloyat = 'viloyat 7' AND tuman = 'tuman 107';

CREATE STATISTICS t61_bog (dependencies)
  ON viloyat, tuman FROM t61_manzil;
ANALYZE t61_manzil;

SELECT pg_temp.taxmin($q$SELECT * FROM t61_manzil
         WHERE viloyat = 'viloyat 7' AND tuman = 'tuman 107'$q$)
         BETWEEN 100 AND 1000 AS taxmin_yaqin;
DROP TABLE t61_manzil;

 haqiqiy | taxmin_juda_kam 
---------+-----------------
     300 | t
(1 row)

 taxmin_yaqin 
--------------
 t
(1 row)

9. Real ishda

• "Kecha tez edi, bugun sekin" — production'dagi eng ko'p uchraydigan shikoyatlardan. Ko'pincha sababi: katta import yoki o'chirishdan keyin statistika eskirgan, planner boshqa reja tanlagan. Birinchi qadam — EXPLAIN ANALYZE da taxmin va haqiqatni solishtirish, keyin ANALYZE.
• Multi-tenant (ko'p mijozli) SaaS'larda — bitta yirik mijoz va minglab kichiklar. Aynan kanondagi notekis taqsimot. Generic reja va MCV muammolari shu yerda chiqadi.
• Managed Postgres (AWS RDS, Supabase, Neon) da autovacuum sozlamalari sukut bo'yicha — katta jadvallar uchun ularni sozlash DBA ishining bir qismi (hujjat bo'yicha, sinalmagan).
• Intervyuda: "Planner qatorlar sonini qanday biladi?", "Nega ANALYZE kerak?", "Prepared statement qachon sekin bo'ladi?"

Xulosa

• Planner taxminni pg_stats dan oladi: n_distinct, MCV, gistogramma, NULL ulushi; ANALYZE 30 000 qatorlik namuna bilan yig'adi.
• MCV ro'yxatidan tashqaridagi qiymatlar teng taqsimlangan deb hisoblanadi — notekis ma'lumotda xato (kanon: 186 vs 415 va 28).
• Eskirgan statistika — 1 000 marta xato (184 vs 200 028); ANALYZE uni tuzatadi.
• SET STATISTICS — muammoli ustun uchun kattaroq namuna va MCV.
• CREATE STATISTICS bog'liq tenglik shartlarida ishlaydi (3 → 299), boshqa shakllarda yordam bermasligi mumkin (kanon: deyarli o'zgarmadi).
• Generic reja $1 ni bilmaydi; notekis ma'lumotda custom reja xavfsizroq.

Keyingi dars: Sekin so'rovlarni topish — bitta so'rovni emas, butun serverni tahlil qilish: pg_stat_statements, sekin so'rovlar logi va ishlatilmayotgan indekslar.

Manbalar

• PostgreSQL 18 hujjati — Statistics Used by the Planner (extended statistics cheklovlari): https://www.postgresql.org/docs/18/planner-stats.html
• PostgreSQL 18 hujjati — Row Estimation Examples: https://www.postgresql.org/docs/18/row-estimation-examples.html
• PostgreSQL 18 hujjati — CREATE STATISTICS: https://www.postgresql.org/docs/18/sql-createstatistics.html
• PostgreSQL 18 hujjati — PREPARE (custom va generic reja): https://www.postgresql.org/docs/18/sql-prepare.html
• PostgreSQL 18 hujjati — Automatic Vacuuming (autovacuum_analyze_scale_factor): https://www.postgresql.org/docs/18/runtime-config-autovacuum.html
• node-postgres hujjati — Queries (prepared statements): https://node-postgres.com/features/queries
• IlmHamroh kanon o'lchovi (PostgreSQL 18.6, 2 mln vazifa, 2026-10-24); SET STATISTICS, CREATE STATISTICS, generic reja va mashqlar — biz ishga tushirdik (PostgreSQL 17.6, Windows 11, i5-12500H).