Користење на сите можности на индексите во PostgreSQL

Во светот на 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" ни кажува дека на барањето сега му треба само индексот, што помага да се избегнат сите В/И на дискот за да се прочита купот на табелата.

Денес, индексите за покривање се достапни само за Б-дрвата. Меѓутоа, во овој случај напорот за одржување ќе биде поголем.

Користење на парцијални индекси

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

Да речеме дека треба да добиеме список со адреси на е-пошта за нашите клиенти во Калифорнија. Барањето ќе биде вака:

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_index од масата 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