🥇Биз PostgreSQL |те индекстердин бардык мүмкүнчүлүктөрүн колдонобуз ProHoster

Postgres дүйнөсүндө индекстер маалымат базасынын сактагычын ("үймөк" деп аталат) натыйжалуу багыттоо үчүн абдан маанилүү. Postgres ал үчүн кластерлөөнү колдобойт жана MVCC архитектурасы сизди бир эле кортеждин көптөгөн версияларына алып келет. Ошондуктан, тиркемелерди колдоо үчүн эффективдүү индекстерди түзө жана кармап туруу өтө маанилүү.

Бул жерде индекстерди колдонууну оптималдаштыруу жана жакшыртуу боюнча бир нече кеңештер бар.

Эскертүү: төмөндө көрсөтүлгөн сурамдар өзгөртүлбөгөндө иштейт pagila үлгү базасы.

Жабуучу индекстерди колдонуу

Активдүү эмес колдонуучулар үчүн электрондук почта даректерин алуу өтүнүчүн карап көрөлү. Таблица 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)

Көрсөткүч да каралган эмес. В-дарактын индекси индекстелген баалуулуктардагы айрым элементтердин бар экендигин билбейт.

Бизге 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)

Superset Indexes

Сиз көптөгөн индекстерге ээ болушуңуз мүмкүн, алардын бири башка индекстерди индекстеген мамычалардын жогорку топтомун индекстейт. Бул каалашы мүмкүн же болбошу мүмкүн — суперсет индексти гана сканерлөө менен жыйынтыкталышы мүмкүн, бул жакшы, бирок ал өтө көп орунду ээлеши мүмкүн же суперсетти оптималдаштыруу үчүн арналган суроо мындан ары колдонулбайт.

Мындай индекстердин аныктамасын автоматташтыруу керек болсо, андан баштасаңыз болот 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 индекстерди түзүү үчүн көптөгөн ийкемдүүлүктөрдү жана кандайдыр бир өзгөчө учурларды чечүү жолдорун, ошондой эле тиркемеңиз жарылган учурда маалымат базасын башкаруу жолдорун камсыз кылат. Бул кеңештер сурооңузга тез жетүүгө жана маалымат базаңызды масштабга даярдоого жардам берет деп үмүттөнөбүз.

Source: www.habr.com

PostgreSQLдеги индекстердин бардык мүмкүнчүлүктөрүн колдонуу