83 / 100

EXPRESSION INDEX

Ifodali indekslar

Ushbu qo'llanmada PostgreSQLda ustun qiymatlariga emas, balki ifodalar yoki funksiyalar natijalariga indeks o'rnatish uchun EXPRESSION INDEX (ifodali indekslar) dan qanday foydalanishni o'rganasiz.

PostgreSQL ifodali indekslariga kirish

Odatda indekslar jadval ustunlarining toza qiymatlari asosida tuziladi. Ammo agar siz so'rovingizda ustun ustida biron-bir funksiya yoki arifmetik amal bajarsangiz (masalan, WHERE LOWER(product_name) = '...' yoki WHERE price * quantity > 1000), PostgreSQL oddiy ustun indeksidan foydalana olmaydi va yana to'liq jadvalni skanerlashga (Seq Scan) o'tadi.

PostgreSQL aynan shu muammoni hal qilish uchun ifodali indekslarni (Expression Indexes) taqdim etadi. Ifodali indeks yaratilganda, PostgreSQL ifodani hisoblab chiqadi va uning natijasini indeks daraxtida saqlaydi.

Sintaksisi:

query.sql

CREATE INDEX [index_name]
ON table_name (expression);

[!NOTE] Agar ifoda murakkab arifmetik yoki mantiqiy amal bo'lsa, uni qo'shimcha qavslar ichiga olish shart: CREATE INDEX ON table_name ((col1 * col2));.

Namunaviy jadval

query.sql

DROP TABLE IF EXISTS products CASCADE;

CREATE TABLE products (
    product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    safety_stock INT NOT NULL DEFAULT 0
);

INSERT INTO products (product_name, price, safety_stock) VALUES
('Apple iPhone 15', 899.99, 50),
('Samsung Galaxy S24', 999.99, 30),
('Sony Xperia 1 VI', 1199.99, 10),
('Dell XPS 15', 1499.99, 20);

Ifodali indeks amaliy misollari

1) Funksiya natijasiga indeks yaratish (LOWER)

Aytaylik, foydalanuvchilar qidiruvda katta-kichik harflarni aralashtirib yozishi mumkin, shuning uchun siz doimo LOWER(product_name) dan foydalanasiz:

query.sql

SELECT product_name, price
FROM products
WHERE LOWER(product_name) = 'apple iphone 15';

Agar biz faqat product_name ustuniga oddiy indeks qo'ysak, ushbu so'rov uni ishlata olmaydi. Keling, to'g'ridan-to'g'ri LOWER(product_name) ifodasiga indeks yaratamiz:

query.sql

CREATE INDEX idx_products_lower_name 
ON products (LOWER(product_name));

Endi so'rov rejasini tekshiramiz:

query.sql

EXPLAIN
SELECT product_name, price
FROM products
WHERE LOWER(product_name) = 'apple iphone 15';

Natija:

matn

                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (...)
   Recheck Cond: (lower((product_name)::text) = 'apple iphone 15'::text)
   ->  Bitmap Index Scan on idx_products_lower_name  (...)
         Index Cond: (lower((product_name)::text) = 'apple iphone 15'::text)

Ko'rib turganingizdek, PostgreSQL idx_products_lower_name ifodali indeksidan unumli foydalanmoqda.

2) Arifmetik hisob-kitob ifodasiga indeks yaratish

Aytaylik, tizimda zaxiradagi mahsulotlarning umumiy qiymati (price * safety_stock) bo'yicha tez-tez saralash yoki filtrlash amalga oshiriladi:

query.sql

CREATE INDEX idx_products_stock_value 
ON products ((price * safety_stock));

Endi ushbu ko'paytma bo'yicha so'rov bajaramiz:

query.sql

EXPLAIN
SELECT product_name, price, safety_stock
FROM products
WHERE price * safety_stock > 10000;

Natija:

matn

                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Bitmap Heap Scan on products  (...)
   Recheck Cond: ((price * (safety_stock)::numeric) > '10000'::numeric)
   ->  Bitmap Index Scan on idx_products_stock_value  (...)
         Index Cond: ((price * (safety_stock)::numeric) > '10000'::numeric)

PostgreSQL har bir qator uchun qayta-qayta ko'paytirib o'tirmasdan, bevosita indekslangan tayyor hisob-kitob natijalaridan foydalanadi.

Qachon ifodali indekslardan foydalanish kerak?

Katta-kichik harfga sezgir bo'lmagan (case-insensitive) matnli qidiruvlarda (LOWER(), UPPER()).
Sanadan yil yoki oyni ajratib olib qidirishda (EXTRACT(YEAR FROM order_date)).
Ustunlar ustida doimiy arifmetik ifodalar bajarilganda.

Xulosa

Ifodali indekslar (Expression Indexes) jadval ustunlariga emas, balki funksiyalar yoki formulalar natijasiga quriladi.
Ular WHERE va ORDER BY qismlarida formulalar ishlatilganda so'rov tezligini keskin oshiradi.
Ifodali indeks yaratishda murakkab ifodalarni ikkita qavs ((expression)) ichiga olish lozim.

Oldingi

Keyingi