EXPLAIN bilan so'rovni tahlil qilish - PostgreSQL

So'rov sekin. Nima qilish kerak? Taxmin qilish o'rniga bazadan so'rash kerak - u so'rovni qanday bajarishini aytib beradi.

SQL

CREATE TABLE mijoz (
    id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism    text NOT NULL,
    shahar text NOT NULL
);

CREATE TABLE buyurtma (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mijoz_id int REFERENCES mijoz(id),
    summa    numeric(10,2) NOT NULL
);

INSERT INTO mijoz (ism, shahar)
SELECT 'mijoz_' || i, (ARRAY['Toshkent','Namangan','Samarqand'])[1 + i % 3]
FROM generate_series(1, 2000) i;

INSERT INTO buyurtma (mijoz_id, summa)
SELECT 1 + (i % 2000), (i % 500 + 50)::numeric
FROM generate_series(1, 50000) i;

ANALYZE mijoz;
ANALYZE buyurtma;

Birinchi reja #

Natija

EXPLAIN SELECT * FROM buyurtma WHERE summa > 500;

                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on buyurtma  (cost=0.00..896.00 rows=4990 width=13)
   Filter: (summa > '500'::numeric)

Har qavsdagi uchta son alohida ma'no beradi:

Qism	Ma'nosi
`cost=0.00..896.00`	Birinchi qatorgacha va hammasigacha shartli narx
`rows=4990`	Baza taxmin qilgan qatorlar soni
`width=13`	Bir qatorning o'rtacha hajmi (bayt)

"Cost" - bu soniya emas

Narx - bu shartli birlik. U diskdan ketma-ket sahifa o'qish narxini 1.0 deb olib, qolgan amallarni shunga nisbatan baholaydi.

cost=0.00..896.00 "896 millisekund" degani emas. Bu shunchaki "shu rejaning nisbiy og'irligi".

Bu sonlar bir rejani boshqa reja bilan solishtirish uchun kerak - haqiqiy vaqtni bilish uchun emas. Haqiqiy vaqt uchun EXPLAIN ANALYZE bor.

Rejani qanday o'qish kerak #

Reja - bu daraxt, va uni pastdan yuqoriga, ichkaridan tashqariga o'qish kerak:

Natija

EXPLAIN SELECT m.shahar, count(*)
FROM mijoz m JOIN buyurtma b ON b.mijoz_id = m.id
GROUP BY m.shahar;

                                  QUERY PLAN
------------------------------------------------------------------------------
 HashAggregate  (cost=1211.53..1211.56 rows=3 width=17)
   Group Key: m.shahar
   ->  Hash Join  (cost=59.00..961.53 rows=50000 width=9)
         Hash Cond: (b.mijoz_id = m.id)
         ->  Seq Scan on buyurtma b  (cost=0.00..771.00 rows=50000 width=4)
         ->  Hash  (cost=34.00..34.00 rows=2000 width=13)
               ->  Seq Scan on mijoz m  (cost=0.00..34.00 rows=2000 width=13)

Har tugun o'z bolalaridan qator oladi va natijani yuqoriga uzatadi

Asosiy tugun turlari #

Tugun	Ma'nosi
`Seq Scan`	Butun jadvalni ketma-ket o'qish
`Index Scan`	Indeks orqali qator topish
`Bitmap Heap Scan`	Ko'p qator uchun indeks - avval manzillar yig'iladi
`Nested Loop`	Har qator uchun ichki qidiruv - kichik jadvallarda tez
`Hash Join`	Kichik jadvaldan xesh-jadval qurib, kattasini o'tkazish
`Merge Join`	Ikkala tomon tartiblangan bo'lsa
`Sort`	Saralash - xotira yetmasa diskka tushadi
`HashAggregate`	Guruhlash

EXPLAIN va EXPLAIN ANALYZE #

Farq juda muhim:

	`EXPLAIN`	`EXPLAIN ANALYZE`
So'rovni bajaradimi	Yo'q	Ha
Ko'rsatadi	Faqat baho	Baho va haqiqat
Xavfsizmi	Har doim	`UPDATE`/`DELETE` da ehtiyot bo'ling

Natija

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT count(*)
FROM buyurtma WHERE summa > 500;

                        QUERY PLAN
----------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=271
   ->  Seq Scan on buyurtma (actual rows=4900.00 loops=1)
         Filter: (summa > '500'::numeric)
         Rows Removed by Filter: 45100
         Buffers: shared hit=271

Endi haqiqiy sonlar bor:

Ko'rsatkich	Ma'nosi
`actual rows=4900`	Haqiqatan qaytgan qatorlar
`loops=1`	Tugun necha marta ishga tushgan
`Rows Removed by Filter: 45100`	Bekorga o'qilgan qatorlar
`Buffers: shared hit=271`	Keshdan o'qilgan sahifalar

EXPLAIN ANALYZE so'rovni haqiqatan bajaradi

SELECT uchun bu xavfsiz. Lekin:

SQL

EXPLAIN ANALYZE DELETE FROM buyurtma WHERE summa < 100;

Bu qatorlarni haqiqatan o'chiradi. "Men shunchaki rejani ko'rmoqchi edim" degan gap yordam bermaydi.

Xavfsiz usul - tranzaksiya ichida bajarish:

SQL

BEGIN;
EXPLAIN ANALYZE DELETE FROM buyurtma WHERE summa < 100;
ROLLBACK;

ROLLBACK hamma o'zgarishni bekor qiladi, lekin reja qo'lda qoladi.

Eng muhim ko'rsatkich: baho va haqiqat farqi #

So'rov sekin bo'lsa, birinchi tekshiriladigan narsa - rows (baho) va actual rows (haqiqat) bir-biriga yaqinmi.

Holat	Nimani anglatadi
Yaqin	Statistika yaxshi, reja ishonchli
Baho ancha kam	Baza sekin rejani tanlagan bo'lishi mumkin
Baho ancha ko'p	Indeksdan bekorga voz kechgan bo'lishi mumkin

Baho noto'g'ri bo'lsa, sabab odatda eskirgan statistika:

SQL

ANALYZE buyurtma;

SELECT relname, reltuples::bigint AS taxminiy_qator
FROM pg_class
WHERE relname IN ('mijoz','buyurtma')
ORDER BY relname;

Natija

ANALYZE
 relname  | taxminiy_qator
----------+----------------
 buyurtma |          50000
 mijoz    |           2000
(2 rows)

pg_class.reltuples - bu rejalashtiruvchi ishonadigan son. ANALYZE dan keyin u haqiqatga yaqin bo'ladi, shuning uchun reja ham to'g'ri chiqadi.

ANALYZE statistikani yangilaydi. Odatda buni autovacuum avtomatik bajaradi, lekin katta yuklamadan keyin uni qo'lda chaqirish foydali - aks holda baza hali ham eski sonlarga qarab reja tuzadi.

Rows Removed by Filter #

Bu ko'rsatkich sekin so'rovlarni topishning eng tez yo'li:

Natija

 Seq Scan on buyurtma (actual rows=0.00 loops=1)
   Filter: ((summa = '123'::numeric) AND (mijoz_id = 7))
   Rows Removed by Filter: 50000

Nol qator qaytdi, lekin buning uchun 50 000 qator o'qildi va tashlandi.

Bu aniq belgi: shu shart uchun indeks kerak.

Optimizatsiyaning to'g'ri tartibi

O'lchang - \timing bilan qancha vaqt ketayotganini biling.
Rejani oling - EXPLAIN (ANALYZE, BUFFERS).
Eng ichkaridan boshlang - sekin tugunni toping.
Rows Removed by Filter katta bo'lsa - indeks o'ylang.
Baho va haqiqat farq qilsa - ANALYZE qiling.
Qayta o'lchang - haqiqatan tezlashdimi?

Oxirgi qadamni tashlab ketmang. "Indeks qo'shdim, endi tez bo'lishi kerak" degan taxmin ko'pincha noto'g'ri chiqadi.

Amaliy topshiriq

Oddiy SELECT uchun EXPLAIN ni bajaring.
cost sonlari nimani anglatishini yozing - bu soniyami?
JOIN li so'rov rejasini oling va tugunlarni sanang.
Qaysi tugun birinchi bajarilishini aniqlang.
EXPLAIN va EXPLAIN ANALYZE farqini yozing.
EXPLAIN ANALYZE DELETE nima uchun xavfli ekanini tushuntiring.
Uni xavfsiz bajarish usulini yozing.
Rows Removed by Filter katta bo'lgan so'rov toping.
Unga indeks qo'shing va rejani qaytadan oling.
ANALYZE dan keyin baho o'zgardimi - tekshiring.

Xulosa #

EXPLAIN so'rovni bajarmasdan rejani ko'rsatadi.
cost - shartli birlik, soniya emas; u rejalarni solishtirish uchun.
Reja daraxt: uni pastdan yuqoriga, ichkaridan tashqariga o'qing.
Seq Scan butun jadvalni, Index Scan esa indeks orqali o'qiydi.
EXPLAIN ANALYZE so'rovni haqiqatan bajaradi - DELETE bilan ehtiyot bo'ling.
Xavfsiz usul: BEGIN ... EXPLAIN ANALYZE ... ROLLBACK.
Eng muhim tekshiruv - rows (baho) va actual rows (haqiqat) farqi.
Katta farq odatda eskirgan statistika belgisi - ANALYZE yordam beradi.
Rows Removed by Filter katta bo'lsa - indeks kerakligining aniq belgisi.
Optimizatsiyadan keyin qayta o'lchang - taxminga ishonmang.

Keyingi bo'limda tranzaksiyalar va PostgreSQL ning MVCC mexanizmini ko'ramiz.

Birinchi reja #

Rejani qanday o'qish kerak #

Asosiy tugun turlari #

EXPLAIN va EXPLAIN ANALYZE #

Eng muhim ko'rsatkich: baho va haqiqat farqi #

Rows Removed by Filter #

Xulosa #

Xatolik topdingizmi?