ProHoster > Blog > Administracija > Izkoristite vse možnosti indeksov v PostgreSQL

Izkoristite vse možnosti indeksov v PostgreSQL

Izkoristite vse možnosti indeksov v PostgreSQL
V svetu Postgres so indeksi izjemno pomembni za učinkovito navigacijo po skladišču podatkov (to imenujejo "heap"). Postgres ne podpira klastrizacije zanj, arhitektura MVCC pa privede do tega, da se nabere veliko različic istega tupla. Zato je zelo pomembno znati ustvarjati in vzdrževati učinkovite indekse za podporo aplikacijam.

Predlagam vam nekaj nasvetov za optimizacijo in izboljšanje uporabe indeksov.

Opomba: spodnji poizvedbe delujejo na nepreurejenem vzoru podatkovne baze pagila..

Uporaba pokrivnih indeksov (Covering Indexes)

Poglejmo poizvedbo za pridobitev e-poštnih naslovov neaktivnih uporabnikov. V tabeli customer je stolpec aktivno, in poizvedba ni zapletena:

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)

V poizvedbi se izvede polno zaporedno skeniranje tabele. customerUstvarimo indeks za stolpec aktivno:

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)

Pomagalo je, naslednje skeniranje se je spremenilo v "index scan". To pomeni, da bo Postgres skeniral indeks "idx_cust1", nato pa bo nadaljeval iskanje po kupu tabele, da prebere vrednosti drugih stolpcev (v tem primeru stolpec email), ki so potrebni za poizvedbo.

V PostgreSQL 11 so se pojavili pokrivni indeksi. Ti omogočajo vključitev ene ali več dodatnih stolpcev v sam indeks — njihove vrednosti so shranjene v skladišču podatkov indeksa.

Če bi uporabili to možnost in dodali vrednost e-pošte v indeks, bi Postgresu ne bilo treba iskati v kupu tabele vrednosti email. Poglejmo, ali bo to delovalo:

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» nam pove, da je poizvedbi zdaj dovolj le indeks, kar pomaga preprečiti vse operacije I/O pri branju kupa tabele.

Trenutno su pokrivački indeksi dostupni samo za B-stabla. Međutim, u tom slučaju će napori za održavanje biti veći.

Korišćenje delimičnih indeksa

Delimični indeksi indeksiraju samo podskup redova tabele. Ovo omogućava smanjenje veličine indeksa i brže obavljanje skeniranja.

Pretpostavimo da želimo da dobijemo spisak adresa e-pošte naših klijenata iz Kalifornije. Upit će izgledati ovako:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
koji ima plan upita koji uključuje skeniranje obe tabele koje su spojene:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              PLAN UPITA
----------------------------------------------------------------------
 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)

Šta nam nude obični indeksi:

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';
                                      PLAN UPITA
---------------------------------------------------------------------------------------
 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)

Skeniranje naslov je zamenjeno skeniranjem indeksa idx_address1, a zatim je skenirana hrpa naslov.

Pošto je ovo čest upit i treba ga optimizovati, možemo koristiti delimični indeks koji indeksira samo one redove sa adresama u kojima je oblast ‘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';
                                           PLAN UPITA
------------------------------------------------------------------------------------------------
 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)

Sada upit čita samo idx_address2 i ne dira tabelu naslov.

Korišćenje višekratnih indeksa (Multi-Value Indexes)

Nekateri stolpci, ki jih je treba indeksirati, morda ne vsebujejo skalarnih podatkovnih tipov. Stolpci tipa, kot so jsonb, nizi in tsvector lahko vsebujejo sestavljene ali več vrednosti. Če morate indeksirati take stolpce, je običajno treba iskati po vseh posameznih vrednostih v teh stolpcih.

Poskusimo najti naslove vseh filmov, ki vključujejo posnetke neuspešnih zvočnih trakov. V tabeli film je besedilni stolpec, imenovan special_features. Če film vsebuje to 'posebno lastnost', bo v stolpcu element v obliki besedilnega niza Behind The Scenes. Za iskanje vseh takih filmov moramo izbrati vse vrstice z 'Behind The Scenes' pri katerikoli vrednosti niza special_features:

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

Operater vsebnosti (containment operator) @> preverja, ali je desna stran podmnožica leve strani.

Načrt poizvedbe:

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)

Kateri zahteva popolno skeniranje kupov s stroškom 67.

Poglejmo, ali nam lahko običajni B-tree indeks pomaga:

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)

Indeks niti ni bil upoštevan. B-tree indeks ne domneva obstoja posameznih elementov v indeksiranih vrednostih.

Potrebujemo GIN indeks.

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 indeks podpira ujemanje posameznih vrednosti s prosto indeksiranimi sestavljenimi vrednostmi, kar posledično zmanjša strošek načrta poizvedbe za več kot polovico.

Znebimo se podvajanja indeksov

Indeksi se akumuliraju tokom vremena, i ponekad novi indeks može sadržavati isto definiciju kao jedan od prethodnih. Da biste dobili ljudima čitljive SQL definicije indeksa, možete koristiti katalog prikaz. pg_indexes. Takođe ćete lako moći da pronađete iste definicije:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
A evo rezultata kada se pokrene na stock pagila bazi podataka:
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)

Indeksi nadmnožstava (Superset Indexes)

Može se desiti da skupite mnogo indeksa, od kojih jedan indeksira nadmnožak kolona koje indeksiraju drugi indeksi. Ovo može biti poželjno ili ne — nadmnožak može dovesti do skeniranja samo po indeksima, što je dobro, ali može takođe zauzeti previše prostora, ili upit, za optimizaciju kojeg je ovaj nadmnožak bio namenjen, više nije u upotrebi.

Ako je potrebno automatizovati identifikaciju takvih indeksa, možete početi sa pg_index iz tabele pg_catalog.

Neiskorišćeni indeksi

Kako se razvijaju aplikacije koje koriste baze podataka, tako se razvijaju i upiti koje koriste. Indeksi dodati ranije više možda neće biti korišćeni nijednim upitom. Pri svakom skeniranju indeksa, on se označava od strane upravitelja statistike, i u prikazu sistemskog kataloga pg_stat_user_indexes možete videti vrednost idx_scan, koja je akumulativni broj. Praćenje ove vrednosti tokom nekog vremenskog perioda (recimo, mesec dana) će dati dobar uvid u to koji indeksi se ne koriste i mogu biti obrisani.

Tukaj je poizvedba za pridobitev trenutnih števcov skeniranja vseh indeksov v shemi ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
izhod naj bo takšen:
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 vrstic)

Ponovna izdelava indeksov z manj blokadami

Pogosto je treba indekse ponovno ustvariti, na primer, ko se povečajo v velikosti, in ponovna izdelava lahko pospeši skeniranje. Indeksi so lahko tudi poškodovani. Sprememba parametrov indeksa lahko prav tako zahteva njegovo ponovno ustvaritev.

Omogočite sočasno ustvarjanje indeksov

V PostgreSQL 11 je ustvarjanje indeksa B-Tree konkurenčno. Za pospešitev procesa ustvarjanja se lahko uporablja več sočasno delujočih delavcev. Vendar pa se prepričajte, da so ti parametri konfiguracije pravilno nastavljeni:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Privzete vrednosti so prenizke. Idealno bi bilo, da se ti številki povečata skupaj s številom procesorskih jeder. Več informacij preberite v dokumenti.

Ozadje pri ustvarjanju indeksov

Ustvarite lahko indeks v ozadju z uporabo parametra CONCURRENTLY ukazov CREATE INDEX:

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

Ta postopek ustvarjanja indeksov se razlikuje od običajnega, saj ne zahteva blokade tabele, kar pomeni, da ne blokira zapisovalnih operacij. Po drugi strani pa traja dlje časa in porabi več virov.

Postgres ponuja številne prilagodljive možnosti za ustvarjanje indeksov in reševanje vseh posebnih primerov ter načine upravljanja z bazo podatkov v primeru eksplozivne rasti vaše aplikacije. Upamo, da vam bodo ti nasveti pomagali, da bodo vaša poizvedbe hitre in baza pripravljena na širitev.

Izvor: habr.com

Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki 🔥 Kupite zanesljivo gostovanje za spletne strani z zaščito pred DDoS, VPS VDS strežniki | ProHoster