ProHoster > Blog > Administrasyon an > Sèvi ak tout karakteristik endèks yo nan PostgreSQL

Sèvi ak tout karakteristik endèks yo nan PostgreSQL

Sèvi ak tout karakteristik endèks yo nan PostgreSQL
Nan mond lan Postgres, endèks yo esansyèl pou navigasyon efikas nan depo baz done a (yo rele yon "tach"). Postgres pa sipòte clustering pou li, ak achitekti MVCC la lakòz ou fini ak anpil vèsyon nan menm tuple la. Se poutèt sa, li trè enpòtan pou kapab kreye epi kenbe endèks efikas pou sipòte aplikasyon yo.

Men kèk konsèy pou optimize ak amelyore itilizasyon endèks yo.

Remak: demann yo montre anba a travay sou yon non modifye pagila echantiyon baz done.

Sèvi ak endèks ki kouvri yo

Ann gade nan yon demann yo ekstrè adrès imel pou itilizatè inaktif. Tablo customer gen yon kolòn active, ak rechèch la se senp:

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)

Rekèt la envoke tout sekans eskanè tab la customer. Ann kreye yon endèks sou yon kolòn 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)

Li te ede, eskanè ki vin apre a te tounen "index scan". Sa vle di ke Postgres pral eskane endèks la "idx_cust1", epi kontinye chèche pil tab la pou li valè lòt kolòn yo (nan ka sa a, kolòn nan email) ke rechèch la bezwen.

Endis ki kouvri yo prezante nan PostgreSQL 11. Yo pèmèt ou mete youn oswa plis kolòn adisyonèl nan endèks la tèt li - valè yo estoke nan magazen done endèks la.

Si nou te pran avantaj de karakteristik sa a epi ajoute valè imel la andedan endèks la, Lè sa a, Postgres pa ta bezwen chèche pil tab la pou valè a. email. Ann wè si sa a pral travay:

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' di nou ke rechèch la kounye a sèlman bezwen yon endèks, ki ede evite tout disk I/O pou li pil tab la.

Kouvèti endèks yo disponib kounye a sèlman pou B-pyebwa. Sepandan, nan ka sa a, efò antretyen an pral pi wo.

Sèvi ak Endis Pasyèl

Endis pasyèl endèks sèlman yon sou-ansanm nan ranje yo nan yon tablo. Sa a sove gwosè a nan endèks epi fè analiz pi vit.

Ann di nou vle jwenn yon lis adrès imel kliyan nou yo nan Kalifòni. Rekèt la pral tankou sa a:

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)

Ki sa ki endèks òdinè pral ban nou:

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)

Fè enspeksyon address te ranplase pa eskanè endèks idx_address1ak Lè sa a, tcheke pil la address.

Piske sa a se yon rechèch souvan epi li bezwen optimize, nou ka itilize yon endèks pasyèl, ki endèks sèlman ranje sa yo ak adrès kote distri a. ‘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)

Koulye a, rechèch la sèlman li idx_address2 epi li pa manyen tab la address.

Sèvi ak endèks milti-valè

Gen kèk kolòn yo dwe endis pa gen yon kalite done eskalè. Kalite kolòn tankou jsonb, arrays и tsvector genyen valè konpoze oswa miltip. Si ou bezwen endèks kolòn sa yo, anjeneral ou oblije chèche nan tout valè endividyèl yo nan kolòn sa yo.

Ann eseye jwenn tit yo nan tout fim ki gen koupe soti nan pran san siksè. Tablo film gen yon kolòn tèks ki rele special_features. Si fim nan gen sa a "pwopriyete espesyal", Lè sa a, kolòn nan gen eleman nan kòm yon etalaj tèks Behind The Scenes. Pou fè rechèch pou tout fim sa yo, nou bezwen chwazi tout ranje ak "Dèyè sèn yo" lè nenpòt ki valè etalaj special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Operatè nidifikasyon @> tcheke si bò dwat la se yon pati nan bò gòch la.

Plan demann:

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)

Ki mande yon eskanè plen pil ak yon pri 67.

Ann wè si yon endèks B-tree regilye ede nou:

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)

Endèks la pa te menm konsidere. Endèks B-tree a pa okouran de egzistans eleman endividyèl yo nan valè endis yo.

Nou bezwen yon endèks 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)

Endèks GIN a sipòte kat valè sèl kont valè konpoze endèks, sa ki lakòz yon pri plan rechèch ki plis pase mwatye.

Lè w debarase m de endis kopi

Endèks akimile sou tan, epi pafwa yon nouvo endèks ka genyen menm definisyon ak youn nan sa yo anvan yo. Ou ka sèvi ak katalòg la pou jwenn definisyon SQL moun lizib nan endèks. pg_indexes. Ou ka jwenn tou fasilman definisyon ki idantik:

 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)

Endis Superset

Li ka rive ke ou fini ak anpil endis, youn nan yo ki endis yon superset nan kolòn ki endèks lòt endèks. Sa a ka dezirab oswa pa ka-sipèsè a ka lakòz analiz endèks sèlman, ki se yon bon bagay, men li ka pran twòp espas, oswa rechèch la ke superset la te gen entansyon optimize pa itilize ankò.

Si ou bezwen otomatize definisyon endèks sa yo, ou ka kòmanse avèk yo pg_index soti nan tab la pg_catalog.

Endis ki pa itilize yo

Kòm aplikasyon ki sèvi ak baz done evolye, se konsa fè demann yo itilize yo. Endèks ki te ajoute pi bonè pa ka itilize ankò pa nenpòt rechèch. Chak fwa yo analize yon endèks, li make pa manadjè estatistik, ak nan katalòg sistèm lan pg_stat_user_indexes ou ka wè valè a idx_scan, ki se yon kontwa kimilatif. Swiv valè sa a sou yon peryòd de tan (di yon mwa) pral bay yon bon lide sou ki endèks yo pa itilize epi yo ka tonbe.

Isit la se yon rechèch pou jwenn konte aktyèl yo eskanè nan tout endèks nan chema a ‘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)

Rebati endèks ak mwens kadna

Endèks yo souvan bezwen rebati, pou egzanp lè yo vin gonfle, ak rekonstriksyon ka pi vit eskanè a. Epitou endèks ka vin pèvèti. Chanje paramèt endèks yo ka mande pou rebati li tou.

Pèmèt kreyasyon endèks paralèl

Nan PostgreSQL 11, kreye yon endèks B-Tree se konkouran. Pou pi vit pwosesis kreyasyon an, yo ka itilize plizyè travayè paralèl. Sepandan, asire w ke opsyon konfigirasyon sa yo tabli kòrèkteman:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Valè default yo twò piti. Idealman, nimewo sa yo ta dwe ogmante ansanm ak kantite nwayo processeur. Li plis nan dokiman.

Kreyasyon endèks background

Ou ka kreye yon endèks nan background nan lè l sèvi avèk opsyon an CONCURRENTLY kòmandman CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Pwosedi kreyasyon endèks sa a diferan de abityèl la paske li pa mande pou yon kadna sou tab la, kidonk li pa bloke operasyon ekriti yo. Nan lòt men an, li pran plis tan ak konsome plis resous.

Postgres bay anpil fleksibilite pou kreye endèks ak fason pou rezoud nenpòt ka espesyal, osi byen ke fason pou jere baz done a si aplikasyon w lan ap grandi anpil. Nou espere konsèy sa yo pral ede w jwenn demann ou yo rapid epi baz done w la pare pou echèl.

Sous: www.habr.com

Add nouvo kòmantè