Коришћење свих карактеристика индекса у ПостгреСКЛ-у

У свету Постгреса, индекси су неопходни за ефикасну навигацију складиштењем базе података (која се назива „гомила“). Постгрес не подржава груписање за њега, а МВЦЦ архитектура узрокује да на крају добијете много верзија истог тупле-а. Због тога је веома важно бити у стању да креирате и одржавате ефикасне индексе за подршку апликацијама.

Ево неколико савета за оптимизацију и побољшање употребе индекса.

Напомена: упити приказани у наставку раде на неизмењеном пагила узорак базе података.

Коришћење индекса покривања

Хајде да погледамо захтев за издвајање адреса е-поште за неактивне кориснике. Сто 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„. То значи да ће Постгрес скенирати индекс "idx_cust1“, а затим наставите да претражујете гомилу табеле да бисте прочитали вредности других колона (у овом случају колона email) које је потребно упиту.

Покривајући индекси су представљени у ПостгреСКЛ 11. Они вам омогућавају да укључите једну или више додатних колона у сам индекс - њихове вредности се чувају у складишту података индекса.

Ако бисмо користили ову функцију и додали вредност е-поште унутар индекса, Постгрес не би морао да тражи вредност у хрпи табеле. 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' нам говори да је упиту сада потребан само индекс, који помаже да се избегне сав И/О диск за читање гомиле табеле.

Индекси покривања су тренутно доступни само за Б-стабла. Међутим, у овом случају, напори одржавања ће бити већи.

Коришћење делимичних индекса

Делимични индекси индексирају само подскуп редова у табели. Ово штеди величину индекса и чини скенирање бржим.

Рецимо да желимо да добијемо листу адреса е-поште наших клијената у Калифорнији. Захтев ће бити овакав:

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.

Хајде да видимо да ли нам обичан индекс Б-стабла помаже:

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)

Индекс није ни разматран. Индекс Б-стабла није свестан постојања појединачних елемената у индексираним вредностима.

Потребан нам је ГИН индекс.

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)

ГИН индекс подржава мапирање појединачних вредности у односу на индексиране сложене вредности, што резултира трошком плана упита који је више него преполовљен.

Уклањање дупликата индекса

Индекси се акумулирају током времена, а понекад нови индекс може да садржи исту дефиницију као и један од претходних. Можете користити каталошки приказ да бисте добили читљиве СКЛ дефиниције индекса. 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)

Обнова индекса са мање брава

Индекси се често морају поново изградити, на пример када постану надувани, а поновна израда може убрзати скенирање. Такође, индекси се могу оштетити. Промена параметара индекса такође може захтевати поновну изградњу.

Омогући креирање паралелног индекса

У ПостгреСКЛ 11, креирање индекса Б-стабла је истовремено. Да би се убрзао процес креирања, може се користити неколико паралелних радника. Међутим, уверите се да су ове опције конфигурације исправно подешене:

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

Ова процедура креирања индекса се разликује од уобичајене по томе што не захтева закључавање табеле, па стога не блокира операције писања. С друге стране, потребно је више времена и троши више ресурса.

Постгрес пружа велику флексибилност за креирање индекса и начина за решавање посебних случајева, као и начина управљања базом података у случају да ваша апликација експлодира. Надамо се да ће вам ови савети помоћи да брзо добијете своје упите и да ваша база података буде спремна за скалирање.

Извор: ввв.хабр.цом