So'rov optimizatsiyasi: nega sekin va nima qilish kerak

18-bob 7 kod bloki 1781 so'z

"Hisobot ochilmayapti"

Ertalab xabar keladi:

— Direktor hisobotni ocholmayapti. Aylanib turibdi, keyin xato beradi.

Siz so'rovni ochasiz. Kecha ishlagan edi. Bugun ishlamayapti. Kod o'zgarmagan.

Bu holatda ko'pchilik nima qiladi? Taxmin qila boshlaydi: "indeks qo'shaylik", "serverni kattalashtiraylik", "keshlaylik".

Uchalasi ham taxmin. Ulardan biri to'g'ri bo'lishi mumkin — lekin qaysi biri ekanini bilmaysiz, shuning uchun uchalasini ham sinab ko'rasiz va kun o'tadi.

Bu bobda boshqa yo'l o'rganamiz: so'rovdan so'rash. Har bazada "sen buni qanday bajarmoqchisan?" deb so'rash imkoni bor. Javobni o'qishni bilsangiz, taxmin qilish kerak emas.

Shifokor va rentgen

Tasavvur qiling, oyog'ingiz og'riyapti. Ikki xil shifokorga borasiz.

Birinchisi qaraydi va aytadi: "Ko'pchilikda bu tovondan bo'ladi. Mana shu malham, bir hafta suring". Bir hafta o'tadi, o'zgarish yo'q. "Unda balki umurtqadandir..."

Ikkinchisi rentgen oladi. Rasmda suyak siniq ekani ko'rinadi. Muolaja aniq.

So'rov rejasi (query plan) — o'sha rentgen. U bazaning ichini ko'rsatadi: qaysi jadvalni qanday o'qimoqchi, nechta qator kutmoqda, qayerda saralash bor.

Bazani tayyorlaymiz

17-bobdagi ombordan foydalanamiz.

python3 -c "
import duckdb, pathlib
if not pathlib.Path('ombor.db').exists():
    import pandas as pd, sys; sys.path.insert(0, 'src')
    from quvur.ombor import OLCHOVLAR, olchov_yarat, fakt_yarat
    d = pd.read_csv('../datasets/buyurtma.csv', parse_dates=['vaqt'])
    con = duckdb.connect('ombor.db')
    con.execute('CREATE TABLE xom AS SELECT * FROM d')
    for nom, (kalit, _) in OLCHOVLAR.items():
        olchov_yarat(con, nom, kalit)
    fakt_yarat(con)
    con.close()
    print('ombor 17-bobdagidek qayta qurildi')
else:
    print('ombor.db allaqachon bor')

con = duckdb.connect('ombor.db')
for t in ('fakt_buyurtma','olchov_tuman','olchov_restoran','olchov_kuryer'):
    n = con.execute(f'SELECT count(*) FROM {t}').fetchone()[0]
    print(f'  {t:18s}: {n:>7,} qator')
con.close()
"

ombor 17-bobdagidek qayta qurildi
  fakt_buyurtma     :  60,000 qator
  olchov_tuman      :      10 qator
  olchov_restoran   :     259 qator
  olchov_kuryer     :     899 qator

Birinchi rentgen: EXPLAIN

python3 -c "
import duckdb
con = duckdb.connect('ombor.db')
reja = con.execute('''
EXPLAIN
SELECT t.tuman, count(*) AS n, round(avg(f.yol_daq),2) AS ort
FROM fakt_buyurtma f
JOIN olchov_tuman t ON t.id = f.tuman_id
WHERE f.summa_ming > 100
GROUP BY t.tuman
''').fetchall()
for r in reja:
    print(r[1])
con.close()
"

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           tuman           │
│             n             │
│            ort            │
│                           │
│        ~6,557 rows        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│__internal_decompress_strin│
│           g(#0)           │
│             #1            │
│             #2            │
│                           │
│        ~6,557 rows        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│       HASH_GROUP_BY       │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│        count_star()       │
│          avg(#1)          │
│                           │
│        ~6,557 rows        │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│           tuman           │
│          yol_daq          │
│                           │
│        ~10,909 rows       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│__internal_compress_string_│
│        uhugeint(#1)       │
│                           │
│        ~10,909 rows       │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│    ────────────────────   │
│      Join Type: INNER     │
│                           │
│        Conditions:        ├──────────────┐
│       tuman_id = id       │              │
│                           │              │
│        ~10,909 rows       │              │
└─────────────┬─────────────┘              │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││          SEQ_SCAN         │
│    ────────────────────   ││    ────────────────────   │
│           Table:          ││           Table:          │
│  ombor.main.fakt_buyurtma ││  ombor.main.olchov_tuman  │
│                           ││                           │
│   Type: Sequential Scan   ││   Type: Sequential Scan   │
│                           ││                           │
│        Projections:       ││        Projections:       │
│          tuman_id         ││             id            │
│          yol_daq          ││           tuman           │
│                           ││                           │
│          Filters:         ││                           │
│      summa_ming>100.0     ││                           │
│                           ││                           │
│        ~12,000 rows       ││          ~10 rows         │
└───────────────────────────┘└───────────────────────────┘

Rejani pastdan yuqoriga o'qing — bu daraxt, eng pastki tugunlar birinchi bajariladi.

Uch narsani qidiring:

Eng muhimi — Filters: qatori SEQ_SCAN ning ichida bo'lishi. Bu shart o'qish paytida qo'llanayotganini bildiradi (12-bobdagi PushedFilters bilan bir xil g'oya).

Rejadan raqam: EXPLAIN ANALYZE

Ikki buyruq bor va ularni chalkashtirish keng tarqalgan xato.

EXPLAIN so'rovni bajarmaydi. U faqat rejani ko'rsatadi: baza nima qilmoqchi. Bu — niyat.

EXPLAIN ANALYZE esa so'rovni haqiqatan bajaradi va har bosqich qancha vaqt olganini yozadi. Bu — haqiqat.

Farq nega muhim? Chunki reja va haqiqat mos kelmasligi mumkin.

Baza rejani statistikaga qarab tuzadi: "bu filtr 100 ta qator qoldiradi deb o'ylayman". Agar statistika eskirgan bo'lsa, u xato o'ylaydi — masalan 100 emas, 100 000 qator qoladi.

EXPLAIN da reja chiroyli ko'rinadi. EXPLAIN ANALYZE da esa o'sha bosqich butun vaqtni yeb qo'yganini ko'rasiz.

Shifokor misolini davom ettiring: EXPLAIN — bu davolash rejasi, EXPLAIN ANALYZE — operatsiyadan keyingi hisobot. Ikkinchisi haqiqatni aytadi.

Amaliy maslahat: sekin so'rovni qidirayotganda doim ANALYZE bilan ishlating. Rejaning o'zi sizni chalg'itishi mumkin.

python3 -c "
import duckdb, re
con = duckdb.connect('ombor.db')
r = con.execute('''
EXPLAIN ANALYZE
SELECT t.tuman, count(*) FROM fakt_buyurtma f
JOIN olchov_tuman t ON t.id = f.tuman_id
GROUP BY t.tuman
''').fetchall()
matn = r[0][1]
for qator in matn.split(chr(10)):
    if any(k in qator for k in ('Timing','Rows','SCAN','JOIN','AGG','Total')):
        print(qator.strip()[:88])
con.close()
"

EXPLAIN ANALYZE SELECT t.tuman, count(*) FROM fakt_buyurtma f JOIN olchov_tuman t ON t.i
││              Total Time: 0.0126s             ││
│         HASH_JOIN         │
│         TABLE_SCAN        ││         TABLE_SCAN        │

Mana nega bu muhim: reja sizga qaysi bosqich vaqtni yeyayotganini aytadi. Siz butun so'rovni emas, o'sha bitta bosqichni tuzatasiz.

Tajriba: filtr qayerda turishi muhimmi

Nazariy jihatdan filtrni erta qo'yish kerak. O'lchab ko'ramiz.

python3 -c "
import duckdb, time
con = duckdb.connect('ombor.db')

def olch(sql, n=7):
    con.execute(sql).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(sql).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return min(v)

erta = '''
SELECT t.tuman, count(*) FROM
  (SELECT * FROM fakt_buyurtma WHERE summa_ming > 150) f
  JOIN olchov_tuman t ON t.id = f.tuman_id
GROUP BY t.tuman'''

kech = '''
SELECT t.tuman, count(*) FROM fakt_buyurtma f
  JOIN olchov_tuman t ON t.id = f.tuman_id
WHERE f.summa_ming > 150
GROUP BY t.tuman'''

a, b = olch(erta), olch(kech)
print(f'filtr JOIN dan oldin : {a:6.2f} ms')
print(f'filtr JOIN dan keyin : {b:6.2f} ms')
print(f'farq: {abs(a-b)/min(a,b)*100:.1f}%')
print()
n1 = con.execute('SELECT count(*) FROM fakt_buyurtma').fetchone()[0]
n2 = con.execute('SELECT count(*) FROM fakt_buyurtma WHERE summa_ming > 150').fetchone()[0]
print(f'filtr {n1:,} qatordan {n2:,} tasini qoldiradi ({n2/n1*100:.1f}%)')
con.close()
"

filtr JOIN dan oldin :   1.17 ms
filtr JOIN dan keyin :   1.12 ms
farq: 4.9%

filtr 60,000 qatordan 31,109 tasini qoldiradi (51.8%)

Funksiyaga o'ralgan ustun

Yuqorida aytilgan birinchi tuzoqni o'lchaymiz.

python3 -c "
import duckdb, time
con = duckdb.connect('ombor.db')

def olch(sql, n=7):
    con.execute(sql).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(sql).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return min(v)

# yomon: ustun funksiya ichida
yomon = \"SELECT count(*) FROM fakt_buyurtma WHERE strftime(sana, '%Y-%m') = '2026-09'\"
# yaxshi: ustun toza, chegara hisoblangan
yaxshi = \"SELECT count(*) FROM fakt_buyurtma WHERE sana >= DATE '2026-09-01' AND sana < DATE '2026-10-01'\"

a, b = olch(yomon), olch(yaxshi)
print(f'funksiya ichida (strftime): {a:6.2f} ms -> {con.execute(yomon).fetchone()[0]:,} qator')
print(f'oraliq bilan (>= va <)    : {b:6.2f} ms -> {con.execute(yaxshi).fetchone()[0]:,} qator')
print()
print(f'oraliq {a/b:.1f}x tez')
con.close()
"

funksiya ichida (strftime):   0.39 ms -> 60,000 qator
oraliq bilan (>= va <)    :   0.26 ms -> 60,000 qator

oraliq 1.5x tez

Ikkalasi ham bir xil javob beradi (bizning ma'lumot butunlay sentabr oyiga tegishli, shuning uchun ikkalasi ham 60 000 qaytaradi) — lekin biri boshqasidan tez.

Sabab: strftime(sana, ...) har qator uchun funksiya chaqiradi, sanani matnga aylantiradi va matnni matn bilan solishtiradi. Ikkinchisi esa sanani sana bilan solishtiradi — bu ancha arzon amal.

Yana bir muhim jihat bor: funksiya ichidagi ustunda baza statistikadan foydalana olmaydi. U "bu faylda sentabrdan oldingi sana yo'q, demak o'qimayman" deb qaror qila olmaydi, chunki strftime natijasi nima bo'lishini oldindan bilmaydi. Kattaroq jadvalda bu farq bir necha foiz emas, bir necha barobar bo'ladi.

Qoida: ustunni yolg'iz qoldiring, hisobni chegaraga qiling.

Faqat kerakli ustun

python3 -c "
import duckdb, time
con = duckdb.connect('ombor.db')

def olch(sql, n=7):
    con.execute(sql).fetchall()
    v = []
    for _ in range(n):
        t0 = time.perf_counter()
        con.execute(sql).fetchall()
        v.append((time.perf_counter()-t0)*1000)
    return min(v)

hammasi = 'SELECT * FROM fakt_buyurtma'
kerakli  = 'SELECT sana, yol_daq FROM fakt_buyurtma'
a, b = olch(hammasi), olch(kerakli)
ustun = len(con.execute('SELECT * FROM fakt_buyurtma LIMIT 1').description)
print(f'SELECT *            ({ustun} ustun): {a:6.2f} ms')
print(f'SELECT sana, yol_daq (2 ustun): {b:6.2f} ms')
print(f'{a/b:.1f}x farq')
con.close()
"

SELECT *            (8 ustun):  21.10 ms
SELECT sana, yol_daq (2 ustun):   5.85 ms
3.6x farq

Bu 9-bobdagi ustunli saqlash darsining davomi: ustunli bazada o'qilmagan ustun diskdan ko'tarilmaydi. SELECT * yozish — o'sha afzallikni o'z qo'lingiz bilan tashlash.

Statistika eskirganda

Optimizator qaror qabul qilish uchun statistika ishlatadi: qaysi jadvalda nechta qator bor, qiymatlar qanday taqsimlangan.

Statistika noto'g'ri bo'lsa, qaror ham noto'g'ri bo'ladi — masalan kichik jadval katta deb hisoblanib, JOIN tartibi teskari tanlanadi.

python3 -c "
import duckdb
con = duckdb.connect('ombor.db')
r = con.execute('''
    SELECT column_name, count, approx_unique
    FROM (SUMMARIZE fakt_buyurtma)
    LIMIT 6
''').fetchall()
print(f'{\"ustun\":16s} {\"qatorlar\":>10s} {\"noyob\":>10s}')
for nom, n, u in r:
    print(f'{nom:16s} {n:>10,} {u:>10,}')
con.close()
"

ustun              qatorlar      noyob
buyurtma_id          60,000     55,316
sana                 60,000         29
tuman_id             60,000         11
restoran_id          60,000        289
kuryer_id            60,000      1,054
summa_ming           60,000      4,288

approx_unique — optimizator uchun eng muhim raqam. U JOIN dan keyin nechta qator qolishini taxmin qilish uchun ishlatiladi.

Xulosa

Talaba ishi

1. olchov_restoran bilan JOIN qo'shing va rejani qayta oling. Yangi HASH_JOIN qayerda paydo bo'ldi?
2. WHERE upper(t.tuman) = 'YUNUSOBOD' yozing va o'lchang. Toza ustun bilan solishtiring.
3. ORDER BY qo'shing va EXPLAIN ANALYZE da qanday yangi bosqich paydo bo'lganini toping.
4. fakt_buyurtma ni sana bo'yicha Parquet ga partitsiyalab yozing, keyin bitta oy uchun so'rovni solishtiring. Farq qancha?