Postgres әлемінде индекстер дерекқор қоймасын («үйме» деп аталады) тиімді шарлау үшін өте маңызды. Postgres бұл үшін кластерлеуді қолдамайды және MVCC архитектурасы сізді бір кортеждің көптеген нұсқаларымен аяқтауға әкеледі. Сондықтан қолданбаларды қолдау үшін тиімді индекстерді құру және қолдау мүмкіндігі өте маңызды.
Мұнда индекстерді пайдалануды оңтайландыру және жақсарту бойынша кейбір кеңестер берілген.
Ескерту: төменде көрсетілген сұраулар өзгертілмегенде жұмыс істейді .
Қамту индекстерін пайдалану
Белсенді емес пайдаланушылар үшін электрондық пошта мекенжайларын шығару сұрауын қарастырайық. Кесте 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. Осындай фильмдердің барлығын іздеу үшін біз «Сахна артында» бар барлық жолдарды қашан таңдауымыз керек кез келген массив мәндері 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 болатын толық үйме сканерлеуді сұрайды.
Кәдімгі B-ағаш индексі бізге көмектесетінін көрейік:
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)Көрсеткіш тіпті ескерілмеді. B-ағаш индексі индекстелген мәндерде жеке элементтердің бар екенін білмейді.
Бізге 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