Barcha yozuvlar
Dasturlash

PostgreSQL indekslari va EXPLAIN ANALYZE: 20 ta qator uchun 400 ming qator o'qigan so'rov

Index bor, lekin katta merchant kabineti baribir sekin. EXPLAIN ANALYZE plan'ini qanday o'qish, B-tree ichida nima borligi va bitta kompozit index so'rovni qanday tuzatishini real plan'lar bilan ko'ramiz.

Tasavvur qiling: to'lov tizimida merchant kabineti bor. Bosh sahifada oxirgi 20 ta muvaffaqiyatli to'lov ko'rsatiladi. Kod juda oddiy, ORM bitta so'rov yuboradi, lokal muhitda sahifa bir zumda ochiladi. Production'da ham ko'pchilik merchantlar hech narsa sezmaydi. Faqat eng katta merchant shikoyat qiladi: uning kabineti sekin ochiladi, yuklama oshgan paytda esa bazaning CPU grafigi tepaga chiqadi.

Kod bir xil, so'rov bir xil, merchant_id ustunida index ham bor. Unda muammo qayerda? Bu maqolada shu savolga EXPLAIN ANALYZE yordamida javob topamiz: plan'dagi raqamlarni qanday o'qish, B-tree index ichida aslida nima saqlanishi, index qachon umuman yordam bermasligi va oxirida to'g'ri index bilan so'rovni qanday tuzatishni bosqichma-bosqich ko'rib chiqamiz.

Muammo: bitta so'rov, ikki xil tezlik

Model odatiy ko'rinishda:

# billing/models.py
from django.db import models


class Merchant(models.Model):
    name = models.CharField(max_length=100)


class Payment(models.Model):
    class Status(models.TextChoices):
        PENDING = "pending", "Pending"
        PAID = "paid", "Paid"
        REFUNDED = "refunded", "Refunded"

    merchant = models.ForeignKey(
        Merchant, on_delete=models.PROTECT, related_name="payments"
    )
    status = models.CharField(max_length=16, choices=Status.choices)
    amount = models.DecimalField(max_digits=12, decimal_places=2)
    created_at = models.DateTimeField(auto_now_add=True)

Kabinet uchun selector:

# billing/selectors.py
from billing.models import Payment


def latest_paid(merchant_id: int):
    return (
        Payment.objects
        .filter(merchant_id=merchant_id, status=Payment.Status.PAID)
        .order_by("-created_at")[:20]
    )

Django PostgreSQL'ga taxminan quyidagi SQL'ni yuboradi:

SELECT "billing_payment"."id", "billing_payment"."merchant_id",
       "billing_payment"."status", "billing_payment"."amount",
       "billing_payment"."created_at"
FROM "billing_payment"
WHERE ("billing_payment"."merchant_id" = 42
       AND "billing_payment"."status" = 'paid')
ORDER BY "billing_payment"."created_at" DESC
LIMIT 20;

merchant — ForeignKey, Django esa ForeignKey ustunlariga avtomatik ravishda index yaratadi (db_index=True default). Ya'ni billing_payment.merchant_id bo'yicha index bor. Birinchi qarashda hammasi joyida.

Natijalar haqiqiy bo'lishi uchun sintetik ma'lumot tayyorlaymiz: 2 million to'lov, ularning taxminan 20 foizi bitta katta merchant'ga (id = 42) tegishli, 80 foizi paid holatida. Real tizimlarda ham shunday notekis taqsimot ko'p uchraydi: bir-ikkita yirik mijoz va minglab kichiklari.

INSERT INTO billing_merchant (name)
SELECT 'merchant ' || g FROM generate_series(1, 5000) AS g;

-- 2M payments: ~20% belong to merchant 42, ~80% are 'paid'
INSERT INTO billing_payment (merchant_id, status, amount, created_at)
SELECT
    CASE WHEN random() < 0.2 THEN 42 ELSE 1 + (random() * 4999)::int END,
    CASE WHEN r < 0.80 THEN 'paid'
         WHEN r < 0.97 THEN 'pending'
         ELSE 'refunded' END,
    (random() * 1000)::numeric(12, 2),
    now() - random() * interval '365 days'
FROM (SELECT random() AS r FROM generate_series(1, 2000000)) AS s;

VACUUM ANALYZE billing_payment;

Maqoladagi barcha plan'lar shu ma'lumotlarda PostgreSQL 16 da olingan. Millisekundlar sizning serveringizda boshqacha bo'ladi — muhimi ularning nisbati va plan shakli.

B-tree ichida nima bor

Plan'ni o'qishdan oldin index'ning o'zini tushunib olish kerak. PostgreSQL'da CREATE INDEX default holatda B-tree yaratadi. Bu muvozanatlangan daraxt: diskda 8 KB lik sahifalardan iborat, tepada root sahifa, o'rtada ichki (internal) sahifalar, pastda esa leaf sahifalar.

                    [ root: 2500 ]
                   /              \
       [ 900 | 1700 ]            [ 3300 | 4100 ]        internal pages
      /      |       \          /      |       \
 [1..899] [900..1699] [1700..] [2500..] [3300..] [4100..]   leaf pages
     <->        <->        <->      <->      <->           (linked list)

 leaf entry:  key = 42  ->  TID (block 1834, item 7)  ->  row in the heap

Muhim jihatlari:

  • Leaf sahifalarda kalitlar tartiblangan holda turadi va har bir yozuv kalit hamda TID'dan (jadvaldagi qatorning fizik manzili: blok raqami va undagi pozitsiya) iborat.
  • Qo'shni leaf sahifalar bir-biriga bog'langan. Shuning uchun BETWEEN, >/< va ORDER BY uchun PostgreSQL bir marta kerakli joyga tushadi, keyin leaf'lar bo'ylab ketma-ket yuradi. Leaf'lar bo'ylab teskari yo'nalishda ham yura oladi, shuning uchun DESC saralash ham index'dan foydalana oladi.
  • Daraxt chuqurligi logarifmik o'sadi: millionlab qatorli jadvalda ham root'dan leaf'gacha bir necha sahifa o'qiladi.
  • Index'da qator ko'rinuvchanligi (MVCC) haqida ma'lumot yo'q. Index topgan TID bo'yicha PostgreSQL baribir jadvalning o'ziga (heap'ga) borib, qator joriy transaction uchun ko'rinadimi-yo'qmi tekshiradi va qolgan ustunlarni o'qiydi. Bu heap'ga "sakrashlar" tartibsiz (random) o'qish bo'ladi va aynan ular qimmat turadi.

Shu sababli PostgreSQL jadvalni o'qishning bir nechta usuliga ega va har safar ulardan birini xarajat (cost) bo'yicha tanlaydi:

Usul Qanday ishlaydi Qachon yaxshi
Seq Scan Jadvalni boshidan oxirigacha sahifama-sahifa o'qiydi Qatorlarning katta qismi kerak bo'lsa, kichik jadvallarda
Index Scan Index'dan TID oladi va har biri uchun heap'ga boradi; natija index tartibida chiqadi Kam qator kerak bo'lsa, ayniqsa ORDER BY ... LIMIT bilan
Bitmap Index Scan + Bitmap Heap Scan Avval barcha mos TID'larni bitmap'ga yig'adi, keyin heap sahifalarini fizik tartibda o'qiydi O'rtacha miqdordagi qatorlar; bir nechta index'ni AND/OR qilish
Index Only Scan Kerakli ustunlarning hammasi index'da bo'lsa, visibility map'da "all-visible" deb belgilangan sahifalar uchun heap'ga umuman bormaydi Covering index'lar, yaqinda VACUUM qilingan jadvallar

Planner xarajatni statistika (ANALYZE yig'adigan pg_stats: eng ko'p uchraydigan qiymatlar, histogrammalar, n_distinct) va sozlamalar (seq_page_cost = 1, random_page_cost = 4 default) asosida hisoblaydi. Random o'qish ketma-ket o'qishdan to'rt barobar qimmat deb hisoblanadi, shuning uchun ko'p qator kerak bo'lsa, planner index'dan voz kechishi mutlaqo normal.

EXPLAIN ANALYZE chiqishini o'qish

EXPLAIN faqat plan'ni va taxminiy raqamlarni ko'rsatadi. EXPLAIN ANALYZE esa so'rovni haqiqatan bajaradi va har bir tugun uchun real vaqt va real qatorlar sonini qo'shadi. BUFFERS qancha sahifa o'qilganini ko'rsatadi (PostgreSQL 18 dan boshlab ANALYZE bilan avtomatik yoqiladi, 16–17 da qo'lda yozing). Django'dan to'g'ridan-to'g'ri chaqirsa bo'ladi:

from billing.selectors import latest_paid

qs = latest_paid(42)
print(qs.query)  # approximate SQL, parameters are not quoted
print(qs.explain(analyze=True, buffers=True))

So'rov haqiqatan bajarilgani uchun UPDATE/DELETE tekshirayotganda doim transaction ichida qilib, oxirida bekor qiling:

-- EXPLAIN ANALYZE really executes the statement.
-- For UPDATE/DELETE always wrap it in a transaction and roll back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE billing_payment SET status = 'refunded' WHERE id = 1;
ROLLBACK;

Endi katta merchant uchun plan (ikkinchi darajali qatorlar qisqartirilgan):

Limit  (cost=28546.98..28549.32 rows=20 width=35) (actual time=117.443..119.435 rows=20 loops=1)
  Buffers: shared hit=1008 read=15993 written=1
  ->  Gather Merge  (cost=28546.98..59875.34 rows=268510 width=35) (actual time=117.441..119.430 rows=20 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=27546.96..27882.60 rows=134255 width=35) (actual time=112.583..112.592 rows=15 loops=3)
              Sort Key: created_at DESC
              Sort Method: top-N heapsort  Memory: 27kB
              ->  Parallel Bitmap Heap Scan on billing_payment  (cost=4789.98..23974.48 rows=134255 width=35) (actual time=13.114..102.134 rows=106657 loops=3)
                    Recheck Cond: (merchant_id = 42)
                    Filter: ((status)::text = 'paid'::text)
                    Rows Removed by Filter: 26632
                    ->  Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a  (cost=0.00..4709.43 rows=402800 width=0) (actual time=12.912..12.912 rows=399869 loops=1)
                          Index Cond: (merchant_id = 42)
Planning Time: 0.380 ms
Execution Time: 119.498 ms

Uni o'qish qoidalari:

  1. Daraxtni ichkaridan tashqariga o'qing. -> bilan boshlangan eng chuqur tugun birinchi ishlaydi va natijasini ota tuguniga beradi. Bu yerda tartib: Bitmap Index Scan → Bitmap Heap Scan → Sort → Gather Merge → Limit.
  2. cost=A..B — planner'ning taxmini, millisekund emas, shartli birliklar. A — birinchi qatorni berishgacha bo'lgan xarajat (startup), B — hammasini berish xarajati. rows — taxminiy qatorlar soni, width — qatorning o'rtacha hajmi baytlarda.
  3. actual time=A..B — millisekundlarda real vaqt, lekin bitta loop uchun. rows ham bitta loop uchun. loops=3 bo'lsa, tugun uch marta ishlagan (bu yerda leader va ikkita parallel worker), demak haqiqiy qatorlar soni 106657 × 3 ≈ 320 ming.
  4. Taxminiy va haqiqiy rows'ni solishtiring. Bu yerda 402800 va 399869 — juda yaqin, statistika yaxshi. Agar farq o'nlab-yuzlab barobar bo'lsa, planner noto'g'ri qaror qabul qilayotgan bo'lishi mumkin; avvalo ANALYZE qiling.
  5. Rows Removed by Filter — o'qilib, keyin tashlab yuborilgan qatorlar. Bu eng muhim signallardan biri: index shartni to'liq qamrab olmayapti.
  6. Buffers — 8 KB lik sahifalar. hit — shared buffers'dan topilgan, read — undan tashqaridan (OS cache yoki disk) o'qilgan. Taxminan 17 ming sahifa, ya'ni 130 MB atrofida ma'lumot — atigi 20 ta qator uchun.

Endi tashxis aniq. Index faqat merchant_id = 42 shartini bajaradi va 400 mingga yaqin TID qaytaradi. Heap scan shu qatorlarning hammasini o'qiydi, status bo'yicha 80 mingga yaqinini tashlab yuboradi, qolgan 320 mingni created_at bo'yicha saralaydi va eng yangi 20 tasini oladi. status sharti ham, ORDER BY ham index'dan foyda ko'rmayapti.

Xuddi shu so'rov kichik merchant uchun:

Limit  (cost=1151.95..1152.00 rows=20 width=35) (actual time=0.799..0.802 rows=20 loops=1)
  ->  Sort  (cost=1151.95..1152.59 rows=256 width=35) (actual time=0.798..0.799 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Bitmap Heap Scan on billing_payment  (cost=6.88..1145.14 rows=256 width=35) (actual time=0.115..0.763 rows=241 loops=1)
              Recheck Cond: (merchant_id = 1777)
              Filter: ((status)::text = 'paid'::text)
              Rows Removed by Filter: 68
              ->  Bitmap Index Scan on billing_payment_merchant_id_6d2f0c1a  (cost=0.00..6.82 rows=319 width=0) (actual time=0.075..0.075 rows=309 loops=1)
                    Index Cond: (merchant_id = 1777)
Execution Time: 0.824 ms

Plan shakli bir xil, lekin index atigi 309 ta qator qaytaradi va hammasi millisekunddan kam vaqtda tugaydi. Lokal muhitda hamma narsa tez ishlashining sababi shu: u yerda "katta merchant" yo'q.

Index borligi emas, so'rov nechta qatorni o'qib, nechtasini tashlab yuborayotgani muhim. Rows Removed by Filter va Sort ustidagi katta rows — birinchi qaraladigan joylar.

Tuzatish: so'rov shakliga mos kompozit index

Kompozit index uchun asosiy qoida: avval tenglik (=) shartlaridagi ustunlar, keyin saralash yoki diapazon ustuni. Bizning so'rovda merchant_id = ?, status = ? va ORDER BY created_at DESC. Demak index (merchant_id, status, created_at DESC) bo'lishi kerak. Bunday index'da 42-merchant'ning barcha paid to'lovlari leaf sahifalarda yonma-yon va allaqachon created_at bo'yicha tartiblangan holda turadi. PostgreSQL kerakli joyga tushadi, birinchi 20 ta yozuvni oladi va to'xtaydi.

# billing/models.py
class Payment(models.Model):
    # ... fields as above ...

    class Meta:
        indexes = [
            models.Index(
                fields=["merchant", "status", "-created_at"],
                name="pay_merch_status_created_idx",
            ),
        ]

makemigrations oddiy AddIndex yaratadi, u esa oddiy CREATE INDEX bajaradi. Oddiy CREATE INDEX jadvalga SHARE lock qo'yadi: o'qish ishlaydi, lekin INSERT/UPDATE/DELETE index qurilib bo'lguncha kutib turadi. To'lovlar jadvali uchun bu qabul qilib bo'lmaydi. Shuning uchun yaratilgan migratsiyani AddIndexConcurrently ga almashtiramiz:

# billing/migrations/0008_payment_merchant_status_created.py
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    atomic = False  # CREATE INDEX CONCURRENTLY cannot run inside a transaction

    dependencies = [
        ("billing", "0007_previous"),
    ]

    operations = [
        AddIndexConcurrently(
            "payment",
            models.Index(
                fields=["merchant", "status", "-created_at"],
                name="pay_merch_status_created_idx",
            ),
        ),
    ]

Bu migratsiya quyidagi SQL'ni bajaradi:

CREATE INDEX CONCURRENTLY "pay_merch_status_created_idx"
    ON "billing_payment" ("merchant_id", "status", "created_at" DESC);

Natija:

Limit  (cost=0.43..5.36 rows=20 width=35) (actual time=0.045..0.104 rows=20 loops=1)
  Buffers: shared hit=15 read=11
  ->  Index Scan using pay_merch_status_created_idx on billing_payment  (cost=0.43..79470.67 rows=322213 width=35) (actual time=0.043..0.100 rows=20 loops=1)
        Index Cond: ((merchant_id = 42) AND ((status)::text = 'paid'::text))
        Buffers: shared hit=15 read=11
Planning Time: 0.233 ms
Execution Time: 0.124 ms

Nimalar o'zgardi:

  • Sort tuguni yo'qoldi: qatorlar index'dan tayyor tartibda keladi.
  • Filter va Rows Removed by Filter yo'q: ikkala shart ham Index Cond ichida.
  • Index Scan'ning to'liq cost'i 79470, lekin Limit'niki 5.36. Planner LIMIT 20 sababli skan erta to'xtashini biladi va startup xarajati kichik plan'ni tanlaydi.
  • Buffers: 17 ming sahifa o'rniga 26 ta sahifa.

E'tibor bering: bu yerda bajarilish vaqtidan ham Buffers ishonchliroq ko'rsatkich. Vaqt cache holatiga, serverning yuklamasiga qarab o'zgaradi, o'qilgan sahifalar soni esa so'rov qancha ish qilayotganini to'g'ridan-to'g'ri ko'rsatadi.

Index qachon yordam bermaydi

Past selektivlik

status ustuniga alohida index qo'shib ko'ramiz. paid jadvalning 80 foizini tashkil qiladi:

-- index on (status) exists; 'paid' is ~80% of the table
Seq Scan on billing_payment  (cost=0.00..41667.00 rows=1591800 width=35) (actual time=0.017..300.312 rows=1600569 loops=1)
  Filter: ((status)::text = 'paid'::text)
  Rows Removed by Filter: 399431

-- same index, 'refunded' is ~3% of the table
Bitmap Heap Scan on billing_payment  (cost=663.75..18071.58 rows=59267 width=35) (actual time=7.013..81.979 rows=59797 loops=1)
  Recheck Cond: ((status)::text = 'refunded'::text)
  ->  Bitmap Index Scan on billing_payment_status_idx  (cost=0.00..648.93 rows=59267 width=0) (actual time=3.815..3.817 rows=59797 loops=1)
        Index Cond: ((status)::text = 'refunded'::text)

Index bor, lekin paid uchun planner uni ataylab ishlatmaydi: 1,6 million qatorni TID orqali random o'qishdan butun jadvalni ketma-ket o'qish arzonroq. refunded (3 foiz) uchun esa xuddi shu index ishlatiladi. Planner taqsimotni pg_stats dagi most_common_vals dan biladi. Xulosa: faqat kam uchraydigan qiymat bo'yicha qidirsangiz, bunday ustunga to'liq index emas, partial index (models.Index(..., condition=Q(status="refunded"))) qo'yish mantiqan to'g'riroq.

Ustun ustidagi funksiya

B-tree ustunning o'z qiymatlari bo'yicha tartiblangan. Shart ustunning o'ziga emas, undan hisoblangan ifodaga qo'yilsa, oddiy index ishlamaydi. Django'da buni sezmay qolish oson, chunki funksiyani ORM o'zi qo'shadi:

User.objects.filter(email__iexact="User777@Example.com").first()
-- what Django sends to PostgreSQL:
SELECT "accounts_user"."id", "accounts_user"."email"  -- and the other columns
FROM "accounts_user"
WHERE UPPER("accounts_user"."email"::text) = UPPER('User777@Example.com')
ORDER BY "accounts_user"."id" ASC
LIMIT 1;
-- email has a UNIQUE index, but the plan is:
Parallel Seq Scan on accounts_user  (cost=0.00..6802.00 rows=1042 width=30) (actual time=62.314..95.115 rows=0 loops=3)
  Filter: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
  Rows Removed by Filter: 166666

email ustunida UNIQUE index bor, lekin u email qiymatlari bo'yicha qurilgan, UPPER(email) bo'yicha emas. Yechim — expression index:

# accounts/models.py
from django.contrib.auth.models import AbstractUser
from django.db import models
from django.db.models.functions import Upper


class User(AbstractUser):
    email = models.EmailField(unique=True)

    class Meta:
        indexes = [
            models.Index(Upper("email"), name="user_email_upper_idx"),
        ]
Index Scan using user_email_upper_idx on accounts_user  (cost=0.42..8.44 rows=1 width=30) (actual time=0.031..0.032 rows=1 loops=1)
  Index Cond: (upper((email)::text) = 'USER777@EXAMPLE.COM'::text)
Execution Time: 0.039 ms

__date, __year, __month lookup'lari bilan ham xuddi shu holat: Django ularni AT TIME ZONE ... va ::date ga aylantiradi. Bunday hollarda shartni ustunning o'zi ustidagi diapazonga qayta yozish yaxshiroq:

from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo

# Wrong: becomes ("created_at" AT TIME ZONE 'Asia/Tashkent')::date = '2026-05-01'
Payment.objects.filter(merchant_id=42, created_at__date=date(2026, 5, 1))

# Right: a plain range on the column itself, the index can be used
start = datetime(2026, 5, 1, tzinfo=ZoneInfo("Asia/Tashkent"))
Payment.objects.filter(
    merchant_id=42,
    created_at__gte=start,
    created_at__lt=start + timedelta(days=1),
)

Kompozit index'ning birinchi ustuni yo'q

(merchant_id, status, created_at) index'i WHERE status = 'paid' ga samarali yordam bermaydi, chunki leaf'lar avvalo merchant_id bo'yicha tartiblangan. PostgreSQL 18 da B-tree uchun skip scan qo'shildi va u ba'zan birinchi ustunda turli qiymatlar kam bo'lsa yordam beradi, lekin bunga tayanib index dizayn qilmang. Shu qoida teskari tomonga ham ishlaydi: yangi index merchant_id bilan boshlangani uchun faqat merchant_id bo'yicha qidiruvlarga ham xizmat qila oladi.

Boshidagi wildcard

LIKE '%foo%' (Django'da __contains/__icontains) uchun B-tree yordam bermaydi. __startswith uchun esa, agar baza collation'i C bo'lmasa, varchar_pattern_ops index kerak. Django buni db_index=True yoki unique=True bo'lgan CharField uchun avtomatik yaratadi (nomi _like bilan tugaydi). Ixtiyoriy joydan qidirish uchun pg_trgm va GIN index kerak, bu alohida maqola mavzusi.

Production'dagi tuzoqlar

  • EXPLAIN ANALYZE so'rovni bajaradi. Production'da og'ir so'rovni bunday ishga tushirish uni yana bir marta bajarish demak. Imkon bo'lsa, replica'da yoki production'ga yaqin hajmdagi nusxada ishlating.
  • Dev ma'lumotlari plan'ni yolg'on ko'rsatadi. 1000 qatorli jadvalda planner haqli ravishda Seq Scan'ni tanlaydi va siz index kerak emas degan xulosaga kelasiz. Plan ma'lumotlar hajmi va taqsimotiga bog'liq.
  • Eskirgan statistika. Katta import yoki migratsiyadan keyin autovacuum hali ANALYZE qilmagan bo'lishi mumkin. Taxminiy va haqiqiy rows keskin farq qilsa, qo'lda ANALYZE table_name; bajaring.
  • CONCURRENTLY yiqilishi mumkin. Masalan, unique index qurilayotganda dublikat chiqsa. Shunda jadvalda INVALID index qoladi: u yozishlarda yangilanib turadi, lekin o'qishda ishlatilmaydi. Uni DROP INDEX CONCURRENTLY bilan o'chirib, qayta yarating.
  • Har bir index yozishni sekinlashtiradi va disk egallaydi. Ishlatilmayotgan index'larni pg_stat_user_indexes orqali toping, lekin statistika har bir server uchun alohida ekanini va oxirgi reset'dan beri hisoblanishini unutmang: replica'dagi so'rovlar ishlatayotgan index primary'da idx_scan = 0 ko'rsatishi mumkin.
  • Birinchi ishga tushirish sovuq cache bilan. Bir necha marta bajarib ko'ring va vaqtdan ko'ra Buffers'ga qarang.

Qaysi so'rovni birinchi navbatda ko'rish kerakligini topish uchun pg_stat_statements eng qulay vosita:

-- requires shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       round(total_exec_time::numeric)    AS total_ms,
       left(query, 120)                   AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- indexes that are never used (on this server, since the last stats reset)
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC
LIMIT 20;

total_exec_time bo'yicha saralash muhim: bir marta 2 soniya ishlaydigan hisobotdan ko'ra, har bir sahifada 50 ms dan minglab marta chaqiriladigan so'rov bazaga ko'proq yuk beradi.

Tekshiruv ro'yxati

  • Sekin so'rovni pg_stat_statements yoki log_min_duration_statement orqali toping, taxmin qilmang.
  • qs.explain(analyze=True, buffers=True) ni production'ga yaqin ma'lumotlarda ishga tushiring.
  • Plan'ni ichkaridan tashqariga o'qing; actual rows × loops ni hisoblang.
  • Rows Removed by Filter, katta Sort va taxminiy/haqiqiy rows farqini qidiring.
  • Kompozit index: avval = ustunlari, keyin ORDER BY yoki diapazon ustuni.
  • Ustun ustida funksiya bo'lsa (iexact, __date) — expression index yoki so'rovni qayta yozish.
  • Index'ni AddIndexConcurrently va atomic = False bilan qo'shing.
  • O'zgarishdan keyin plan'ni qayta tekshiring va Buffers'ni solishtiring.

Xulosa

Sekin so'rovning sababi deyarli har doim plan'da yozilgan bo'ladi: qayerda ko'p qator o'qilib, ko'p qator tashlab yuborilayotganini EXPLAIN ANALYZE aniq ko'rsatadi. B-tree tartiblangan kalitlar va TID'lar ekanini tushunsangiz, qaysi index qaysi so'rovga yordam berishini oldindan aytish oson bo'ladi. Index'ni ustunga emas, aniq so'rov shakliga qarab loyihalang va natijani taxmin bilan emas, plan bilan tekshiring.

// izohlar

Izohlar yuklanmoqda...

    // keyingi qadam

    Keling, birga nimadir quramiz.

    Telegramda yozish
    aslbek@asldev: ~
    ❯