Indekslar

Indeks - so'rovni tezlashtirishning asosiy vositasi. Lekin u sehrli tayoqcha emas: noto'g'ri qo'yilgan indeks foyda bermaydi, ortiqchasi esa zarar keltiradi.

CREATE TABLE buyurtma (
    id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mijoz text NOT NULL,
    holat text NOT NULL,
    summa numeric(10,2) NOT NULL,
    sana  date NOT NULL
);

INSERT INTO buyurtma (mijoz, holat, summa, sana)
SELECT 'mijoz_' || (i % 500),
       CASE WHEN i % 100 = 0 THEN 'bekor' ELSE 'yakunlangan' END,
       (i % 900 + 100)::numeric,
       DATE '2026-01-01' + (i % 365)
FROM generate_series(1, 20000) AS i;

Indeks turlari #

PostgreSQL da bir nechta indeks turi bor va har biri boshqa vazifa uchun:

Nomi ko'rsatilmasa, PostgreSQL B-tree yaratadi:

CREATE INDEX buyurtma_mijoz_idx ON buyurtma (mijoz);

SELECT indexname FROM pg_indexes
WHERE tablename = 'buyurtma' ORDER BY indexname;

CREATE INDEX
     indexname
--------------------
 buyurtma_mijoz_idx
 buyurtma_pkey
(2 rows)

Birlamchi kalit uchun indeks avtomatik yaratilgan - buyurtma_pkey.

Indeks har doim ishlatilmaydi #

Bu eng muhim tushuncha. Indeks bor bo'lishi - u ishlatiladi degani emas.

holat ustuniga indeks qo'yamiz va bir xil indeksni ikki xil qiymat bilan sinaymiz:

CREATE INDEX buyurtma_holat_idx ON buyurtma (holat);

-- 'yakunlangan' - qatorlarning 99 foizi
EXPLAIN (COSTS OFF) SELECT * FROM buyurtma WHERE holat = 'yakunlangan';

               QUERY PLAN
-----------------------------------------
 Seq Scan on buyurtma
   Filter: (holat = 'yakunlangan'::text)

-- 'bekor' - qatorlarning 1 foizi
EXPLAIN (COSTS OFF) SELECT * FROM buyurtma WHERE holat = 'bekor';

                   QUERY PLAN
-------------------------------------------------
 Index Scan using buyurtma_holat_idx on buyurtma
   Index Cond: (holat = 'bekor'::text)

Bir xil ustun, bir xil indeks - lekin ikki xil reja.

Qisman indeks #

Faqat kerakli qatorlarni indekslash mumkin:

CREATE INDEX buyurtma_sana_idx  ON buyurtma (sana);
CREATE INDEX buyurtma_bekor_idx ON buyurtma (sana) WHERE holat = 'bekor';

SELECT pg_size_pretty(pg_relation_size('buyurtma_bekor_idx')) AS qisman,
       pg_size_pretty(pg_relation_size('buyurtma_sana_idx'))  AS toliq;

CREATE INDEX
CREATE INDEX
 qisman | toliq
--------+--------
 16 kB  | 160 kB
(1 row)

Farqqa qarang: qisman indeks ancha kichik, chunki u qatorlarning atigi bir foizini saqlaydi.

U kichik bo'lgani uchun xotiraga sig'adi, tez o'qiladi va yozishda kam yuk beradi.

Ifoda ustidan indeks #

Ustunga funksiya qo'llanilsa, oddiy indeks ishlamaydi:

EXPLAIN (COSTS OFF) SELECT * FROM buyurtma WHERE lower(mijoz) = 'mijoz_42';

                    QUERY PLAN
---------------------------------------------------
 Gather
   Workers Planned: 1
   ->  Parallel Seq Scan on buyurtma
         Filter: (lower(mijoz) = 'mijoz_42'::text)

Indeks mijoz ni saqlaydi, so'rov esa lower(mijoz) ni so'rayapti - baza ularni bog'lay olmaydi.

Yechim - aynan shu ifodani indekslash:

CREATE INDEX buyurtma_mijoz_lower_idx ON buyurtma (lower(mijoz));

SELECT replace(indexdef, 'public.', '') AS indeks
FROM pg_indexes
WHERE indexname = 'buyurtma_mijoz_lower_idx';

CREATE INDEX
                                    indeks
------------------------------------------------------------------------------
 CREATE INDEX buyurtma_mijoz_lower_idx ON buyurtma USING btree (lower(mijoz))
(1 row)

Ko'p ustunli indeks #

Bir nechta ustunni birga indekslash mumkin, lekin tartib muhim:

CREATE INDEX buyurtma_holat_sana_idx ON buyurtma (holat, sana);

SELECT replace(indexdef, 'public.', '') AS indeks
FROM pg_indexes
WHERE indexname = 'buyurtma_holat_sana_idx';

CREATE INDEX
                                   indeks
----------------------------------------------------------------------------
 CREATE INDEX buyurtma_holat_sana_idx ON buyurtma USING btree (holat, sana)
(1 row)

Bu indeks quyidagi so'rovlarga yaraydi:

Qoida: ko'p ustunli indeks chapdan boshlab ishlaydi. Uni telefon kitobiga o'xshating: familiya bo'yicha topish oson, faqat ism bo'yicha esa - yo'q.

Shuning uchun tenglik sharti qo'yiladigan ustunni oldinga, oraliq shartlisini keyinga qo'ying.

Indeksning narxi #

Indeks bepul emas:

SELECT indexrelname AS indeks,
       pg_size_pretty(pg_relation_size(indexrelid)) AS hajm
FROM pg_stat_user_indexes
WHERE relname = 'buyurtma'
ORDER BY pg_relation_size(indexrelid) DESC;

    indeks     |  hajm
---------------+--------
 buyurtma_pkey | 456 kB
(1 row)

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Xulosa #

• B-tree - standart indeks; tenglik va oraliq shartlari uchun.
• GIN massiv, JSONB va matn qidiruv uchun; BRIN juda katta tartiblangan jadval uchun.
• Indeks bor bo'lishi - u ishlatiladi degani emas.
• Ko'p qator mos kelsa, baza ketma-ket o'qishni afzal ko'radi - bu tezroq.
• Kam qiymatli ustunlarga (boolean, holat) oddiy indeks deyarli foydasiz.
• Qisman indeks faqat kerakli qatorlarni saqlaydi - kichik va tez.
• Ustunga funksiya qo'llanilsa, oddiy indeks ishlamaydi - ifoda indeksi kerak.
• Registrga sezgir bo'lmagan qidiruv uchun lower() ustidan indeks - to'g'ri yechim.
• Ko'p ustunli indeks chapdan boshlab ishlaydi; tenglik ustuni oldinga.
• Har indeks yozishni sekinlashtiradi - ishlatilmayotganini idx_scan = 0 bilan topib o'chiring.

Keyingi bo'limda rejalarni o'qishni - EXPLAIN ni batafsil o'rganamiz.