Postgres dunyosida indekslar ma'lumotlar bazasini (uy deb ataladi) samarali navigatsiya qilish uchun juda muhimdir. Postgres buning uchun klasterlashni qo'llab-quvvatlamaydi va MVCC arxitekturasi sizni bir xil kortejning ko'plab versiyalariga olib keladi. Shuning uchun, ilovalarni qo'llab-quvvatlash uchun samarali indekslarni yaratish va saqlash imkoniyati juda muhimdir.
Men sizning e'tiboringizga indekslardan foydalanishni optimallashtirish va yaxshilash bo'yicha ba'zi maslahatlarni keltiraman.
Eslatma: Quyida ko'rsatilgan so'rovlar o'zgartirilmaganda ishlaydi .
Qoplama indekslaridan foydalanish
Keling, faol bo'lmagan foydalanuvchilar uchun elektron pochta manzillarini olish so'rovini ko'rib chiqaylik. Jadvalda customer ustun bor active, va so'rov oddiy:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
So'rov to'liq jadvalni skanerlash ketma-ketligini chaqiradi customer. Keling, ustunda indeks yarataylik active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Yordam berdi, keyingi skanerlash "ga aylandiindex scan". Bu Postgres indeksni skanerlashini anglatadi "idx_cust1", va keyin boshqa ustunlar qiymatlarini o'qish uchun jadval to'plamini qidirishni davom eting (bu holda, ustun email) so'rovga kerak bo'lgan.
PostgreSQL 11 qamrab oluvchi indekslarni taqdim etdi. Ular sizga bir yoki bir nechta qo'shimcha ustunlarni indeksning o'ziga qo'shishga imkon beradi - ularning qiymatlari indeks ma'lumotlar do'konida saqlanadi.
Agar biz ushbu xususiyatdan foydalangan bo'lsak va indeksga elektron pochta qiymatini qo'shsak, Postgres qiymat uchun jadval to'plamini qidirishi shart emas. email. Keling, bu ish yoki yo'qligini ko'rib chiqaylik:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Β«Index Only Scan" so'roviga endi faqat indeks kerak bo'lishini aytadi, bu esa jadval to'plamini o'qish uchun barcha disk kiritish-chiqarishlarini oldini olishga yordam beradi.
Bugungi kunda qoplama indekslari faqat B-daraxtlar uchun mavjud. Biroq, bu holda parvarishlash harakatlari yuqoriroq bo'ladi.
Qisman indekslardan foydalanish
Qisman indekslar jadval qatorlarining faqat bir qismini indekslaydi. Bu sizga indeks hajmini saqlash va skanerlashni tezroq bajarish imkonini beradi.
Aytaylik, Kaliforniyadagi mijozlarimiz uchun elektron pochta manzillari ro'yxatini olishimiz kerak. So'rov quyidagicha bo'ladi:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)Oddiy indekslar bizga nima beradi:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
Internet saytlarini ko'rish address indekslarni skanerlash bilan almashtirildi idx_address1, va keyin yig'ma skanerdan o'tkazildi address.
Bu tez-tez uchraydigan so'rov bo'lib, uni optimallashtirish kerak bo'lganligi sababli, biz qisman indeksdan foydalanishimiz mumkin, bu faqat mintaqa manzillari bo'lgan qatorlarni indekslaydi. βCaliforniaβ:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
Endi so'rov faqat o'qiladi idx_address2 va stolga tegmaydi address.
Ko'p qiymatli indekslardan foydalanish
Indekslanishi kerak bo'lgan ba'zi ustunlar skaler ma'lumotlar turini o'z ichiga olmaydi. Ustun turlari kabi jsonb, arrays ΠΈ tsvector qo'shma yoki ko'p ma'nolarni o'z ichiga oladi. Agar siz bunday ustunlarni indekslashingiz kerak bo'lsa, odatda ushbu ustunlardagi barcha individual qiymatlarni qidirishingiz kerak.
Keling, muvaffaqiyatsiz suratga olingan kliplarni o'z ichiga olgan barcha filmlarning nomlarini topishga harakat qilaylik. Jadvalda film deb nomlangan matn ustuni mavjud special_features. Agar film ushbu "maxsus xususiyatga" ega bo'lsa, u holda ustun matn massivi ko'rinishidagi elementni o'z ichiga oladi Behind The Scenes. Bunday filmlarning barchasini qidirish uchun biz "Sahna ortida" bilan barcha qatorlarni tanlashimiz kerak har qanday massiv qiymatlari special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Himoya operatori @> o'ng tomon chap tomonning kichik to'plami ekanligini tekshiradi.
So'rov rejasi:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Bu 67 xarajat bilan to'liq yig'ma skanerlashni talab qiladi.
Keling, oddiy B-daraxt indeksi bizga yordam beradimi yoki yo'qligini bilib olaylik:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)Indeks hatto hisobga olinmadi. B-daraxt indeksi indekslagan qiymatlarda alohida elementlarning mavjudligi haqida hech qanday tasavvurga ega emas.
Bizga GIN indeksi kerak.
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)GIN indeksi individual qiymatlarni indekslangan kompozit qiymatlarga solishtirishni qo'llab-quvvatlaydi, bu esa so'rovlar rejasining yarmidan ko'p narxiga olib keladi.
Ikki nusxadagi indekslardan xalos bo'lish
Indekslar vaqt o'tishi bilan to'planadi va ba'zida yangi indeks avvalgilaridan biri bilan bir xil ta'rifni o'z ichiga olishi mumkin. Inson o'qiy oladigan SQL indeksi ta'riflarini olish uchun katalog ko'rinishidan foydalanishingiz mumkin pg_indexes. Xuddi shu ta'riflarni osongina topishingiz mumkin:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And hereβs the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Superset indekslari
Siz ko'plab indekslarni to'plashingiz mumkin, ulardan biri boshqa indekslar indekslaydigan ustunlar to'plamini indekslaydi. Bu istalmagan boΚ»lishi ham mumkin β superset faqat indeksli skanerlashga olib kelishi mumkin, bu yaxshi, lekin u juda koΚ»p joy egallashi mumkin yoki superset optimallashtirish uchun moΚ»ljallangan soΚ»rov endi ishlatilmaydi.
Agar siz bunday indekslarning ta'rifini avtomatlashtirishingiz kerak bo'lsa, siz boshlashingiz mumkin stoldan pg_catalog.
Ishlatilmagan indekslar
Ma'lumotlar bazasidan foydalanadigan ilovalar rivojlanishi bilan ular foydalanadigan so'rovlar ham rivojlanadi. Ilgari qo'shilgan indekslar endi hech qanday so'rovlar tomonidan ishlatilmasligi mumkin. Har safar indeks skanerdan o'tkazilganda, u statistika menejeri tomonidan va tizim katalogi ko'rinishida qayd etiladi. pg_stat_user_indexes qiymatini ko'rishingiz mumkin idx_scan, bu kumulyativ hisoblagichdir. Ushbu qiymatni ma'lum vaqt oralig'ida (aytaylik, bir oy) kuzatish qaysi indekslar ishlatilmayotgani va o'chirilishi mumkinligi haqida yaxshi fikr beradi.
Bu erda sxemadagi barcha indekslarning joriy skanerlash sonlarini olish so'rovi βpublicβ:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)Indekslarni kamroq qulflar bilan tiklash
Ko'pincha indekslarni qayta yaratish kerak, masalan, ular shishib ketganda va qayta yaratish skanerlashni tezlashtirishi mumkin. Indekslar ham buzilishi mumkin. Indeks parametrlarini o'zgartirish uni qayta yaratishni ham talab qilishi mumkin.
Parallel indeks yaratishni yoqing
PostgreSQL 11 da B-Tree indeksini yaratish bir vaqtda amalga oshiriladi. Yaratish jarayonini tezlashtirish uchun bir nechta parallel ishchilardan foydalanish mumkin. Biroq, ushbu konfiguratsiya sozlamalari to'g'ri o'rnatilganligiga ishonch hosil qiling:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Standart qiymatlar juda kichik. Ideal holda, bu raqamlar protsessor yadrolari soni bilan birga ortishi kerak. Batafsil o'qing .
Fon indeksini yaratish
Variantdan foydalanib, fonda indeks yaratishingiz mumkin CONCURRENTLY jamoalari CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXUshbu indeks yaratish tartibi odatdagidan farq qiladi, chunki u jadvalni blokirovka qilishni talab qilmaydi va shuning uchun yozish operatsiyalarini bloklamaydi. Boshqa tomondan, bu ko'proq vaqt talab etadi va ko'proq resurslarni sarflaydi.
Postgres har qanday maxsus holatlar uchun indekslar va echimlarni yaratish uchun ko'plab moslashuvchan variantlarni taqdim etadi, shuningdek, agar ilovangiz tez o'sib borayotgan bo'lsa, ma'lumotlar bazasini boshqarish usullarini taqdim etadi. Umid qilamizki, ushbu maslahatlar sizning so'rovlaringizni tezlashtirishga va ma'lumotlar bazasini kengaytirishga tayyor bo'lishga yordam beradi.
Manba: www.habr.com