Postgres ертөнцөд индексүүд нь мэдээллийн баазыг ("овоолон" гэж нэрлэдэг) үр ашигтай удирдахад зайлшгүй шаардлагатай байдаг. Postgres нь кластер хийхийг дэмждэггүй бөгөөд MVCC-ийн архитектур нь таныг ижил tuple-ийн олон хувилбартай болгоход хүргэдэг. Тиймээс програмуудыг дэмжих үр дүнтэй индексүүдийг үүсгэж, хадгалах чадвартай байх нь маш чухал юм.
Индексүүдийн ашиглалтыг оновчтой болгох, сайжруулах зарим зөвлөмжийг энд оруулав.
Тайлбар: Доор үзүүлсэн асуулга нь өөрчлөгдөөгүй дээр ажилладаг .
Хамгаалалтын индексүүдийг ашиглах
Идэвхгүй хэрэглэгчдийн имэйл хаягийг задлах хүсэлтийг харцгаая. Хүснэгт customer багана байна active, мөн асуулга нь энгийн:
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)
Асуулга нь хүснэгтийг бүрэн скан хийх дарааллыг дууддаг customer. Багана дээр индекс үүсгэцгээе 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)
Энэ нь тусалсан, дараагийн сканнер нь "болов.index scan". Энэ нь Postgres индексийг сканнердах болно гэсэн үг юм "idx_cust1", дараа нь бусад баганын утгыг уншихын тулд хүснэгтийн овоолгыг үргэлжлүүлэн хайна уу (энэ тохиолдолд багана email) асуулгад шаардлагатай.
Хамрах индексүүдийг PostgreSQL 11-д нэвтрүүлсэн. Тэд танд нэг буюу хэд хэдэн нэмэлт баганыг индекст оруулах боломжийг олгодог - тэдгээрийн утгууд нь индексийн мэдээллийн санд хадгалагддаг.
Хэрэв бид энэ боломжийн давуу талыг ашиглаж, индекс дотор имэйлийн утгыг нэмсэн бол Postgres хүснэгтийн овоолгын утгыг хайх шаардлагагүй болно. email. Энэ ажиллах эсэхийг харцгаая:
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' гэсэн нь асуулгад одоо зөвхөн индекс хэрэгтэй бөгөөд энэ нь хүснэгтийн овоолгыг унших бүх дискний оролт/гаралтаас зайлсхийхэд тусална.
Бүрхүүлгийн индексийг одоогоор зөвхөн B хэлбэрийн модонд ашиглах боломжтой. Гэхдээ энэ тохиолдолд засвар үйлчилгээний хүчин чармайлт илүү өндөр байх болно.
Хэсэгчилсэн индекс ашиглах
Хэсэгчилсэн индексүүд нь хүснэгтийн мөрүүдийн зөвхөн дэд олонлогийг индексжүүлдэг. Энэ нь индексийн хэмжээг хэмнэж, сканнердах ажиллагааг хурдан болгодог.
Калифорни дахь үйлчлүүлэгчдийнхээ имэйл хаягийн жагсаалтыг авахыг хүсч байна гэж бодъё. Хүсэлт нь дараах байдалтай байх болно.
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)Ердийн индексүүд бидэнд юу өгөх вэ:
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)
Скан хийх address индекс сканнераар солигдсон idx_address1дараа нь овоолгыг сканнердсан address.
Энэ нь байнга асуудаг бөгөөд оновчтой болгох шаардлагатай байдаг тул бид зөвхөн тухайн дүүргийн хаягтай мөрүүдийг индексжүүлдэг хэсэгчилсэн индексийг ашиглаж болно. ‘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)
Одоо асуулга зөвхөн уншиж байна idx_address2 мөн ширээнд хүрдэггүй address.
Олон утгатай индекс ашиглах
Индексжүүлэх зарим багана нь скаляр өгөгдлийн төрлийг агуулаагүй байж болно. Баганын төрөл гэх мэт jsonb, arrays и tsvector нийлмэл эсвэл олон утгыг агуулна. Хэрэв та ийм баганыг индексжүүлэх шаардлагатай бол эдгээр баганад байгаа бүх утгыг хайх хэрэгтэй.
Амжилтгүй зураг авалтаас авсан тайралт бүхий бүх киноны нэрийг олохыг хичээцгээе. Хүснэгт film гэж нэрлэгддэг текст багана байдаг special_features. Хэрэв кино ийм "тусгай шинж чанартай" бол баганад текстийн массив хэлбэрээр элемент агуулна Behind The Scenes. Ийм бүх киног хайхын тулд бид "Behind The Scenes" гэсэн бүх мөрийг сонгох хэрэгтэй ямар ч массивын утгууд special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Үүрлэх оператор @> баруун тал нь зүүн талын дэд хэсэг мөн эсэхийг шалгадаг.
Хүсэлтийн төлөвлөгөө:
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)Энэ нь 67-ийн өртөгтэй бүрэн хэмжээний скан хийхийг шаарддаг.
Ердийн В модны индекс бидэнд тусалдаг эсэхийг харцгаая.
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)Индексийг ч авч үзээгүй. В-модны индекс нь индексжүүлсэн утгууд дахь бие даасан элементүүд байгааг мэддэггүй.
Бидэнд GIN индекс хэрэгтэй.
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 индекс нь индексжүүлсэн нийлмэл утгуудын эсрэг дан утгуудын зураглалыг дэмждэг бөгөөд ингэснээр асуулгын төлөвлөгөөний өртөг хоёр дахин багасдаг.
Давхардсан индексүүдээс салах
Индексүүд цаг хугацааны явцад хуримтлагддаг бөгөөд заримдаа шинэ индекс өмнөхтэй ижил тодорхойлолтыг агуулж болно. Та каталогийн харагдацыг ашиглан индексийн хүний унших боломжтой SQL тодорхойлолтыг авах боломжтой. pg_indexes. Та мөн ижил төстэй тодорхойлолтуудыг хялбархан олох боломжтой:
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)
Суперсет индексүүд
Та олон индекстэй болж магадгүй бөгөөд тэдгээрийн нэг нь бусад индексүүдийг индексжүүлдэг баганын дээд багцыг индексжүүлдэг. Энэ нь хүсээгүй эсвэл хүсээгүй байж магадгүй—суперсет нь зөвхөн индексээр скан хийхэд хүргэж болох бөгөөд энэ нь сайн, гэхдээ энэ нь хэт их зай эзэлнэ, эсвэл суперсетийг оновчтой болгохоор зорьсон хайлтыг ашиглахаа больсон.
Хэрэв та ийм индексийн тодорхойлолтыг автоматжуулах шаардлагатай бол эхэлж болно ширээнээс pg_catalog.
Ашиглагдаагүй индексүүд
Өгөгдлийн санг ашигладаг программууд хөгжихийн хэрээр тэдний ашигладаг асуулга ч мөн адил хөгжиж байна. Өмнө нь нэмсэн индексийг ямар ч асуулгад ашиглах боломжгүй. Индексийг сканнердах бүрт статистикийн менежер тэмдэглэж, системийн каталогийн харагдац дээр pg_stat_user_indexes үнэ цэнийг харж болно idx_scan, энэ нь хуримтлагдсан тоолуур юм. Энэ утгыг тодорхой хугацааны туршид (сар гэж хэлэхэд) хянах нь ямар индексийг ашиглаагүй, хасагдах боломжтой талаар сайн санаа өгөх болно.
Схем дэх бүх индексийн одоогийн скан тооллогыг авах асуулга энд байна ‘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)Цөөн цоожтой индексүүдийг сэргээж байна
Индексүүд ихэвчлэн дүүрсэн үед дахин бүтээх шаардлагатай байдаг бөгөөд дахин бүтээх нь сканыг хурдасгадаг. Мөн индексүүд эвдэрч болзошгүй. Индексийн параметрүүдийг өөрчлөхөд үүнийг дахин бүтээх шаардлагатай байж магадгүй юм.
Зэрэгцээ индекс үүсгэхийг идэвхжүүлнэ
PostgreSQL 11-д B-Tree индекс үүсгэх нь нэгэн зэрэг хийгддэг. Бүтээлийн үйл явцыг хурдасгахын тулд хэд хэдэн зэрэгцээ ажилчдыг ашиглаж болно. Гэхдээ эдгээр тохиргооны сонголтуудыг зөв тохируулсан эсэхийг шалгаарай:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Өгөгдмөл утга нь хэтэрхий бага байна. Хамгийн тохиромжтой нь эдгээр тоо нь процессорын цөмийн тоогоор нэмэгдэх ёстой. Дэлгэрэнгүйг эндээс уншина уу .
Суурь индекс үүсгэх
Сонголтыг ашиглан арын дэвсгэр дээр индекс үүсгэж болно CONCURRENTLY баг CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXЭнэхүү индекс үүсгэх процедур нь ширээн дээр түгжээ шаарддаггүй, тиймээс бичих үйлдлийг блоклодоггүй гэдгээрээ ердийнхөөс ялгаатай. Нөгөөтэйгүүр, илүү их цаг зарцуулж, илүү их нөөц зарцуулдаг.
Postgres нь индекс үүсгэх уян хатан байдал, аливаа онцгой тохиолдлыг шийдвэрлэх арга замууд, мөн таны хэрэглүүр хурдацтай өсөх тохиолдолд мэдээллийн санг удирдах аргуудыг өгдөг. Эдгээр зөвлөмжүүд нь таны асуусан асуултуудыг хурдан авч, мэдээллийн санг өргөжүүлэхэд бэлэн болгоно гэж найдаж байна.
Эх сурвалж: www.habr.com