Iskoristite sve mogućnosti indeksa u PostgreSQL

Iskoristite sve mogućnosti indeksa u PostgreSQL
U svijetu Postgres indeksi su izuzetno važni za efikasnu navigaciju po skladištu baze podataka (koje se naziva „heap“). Postgres ne podržava klasterizaciju za njega, a MVCC arhitektura dovodi do toga da se akumulira mnogo verzija istog tuplja. Stoga je veoma važno umjeti kreirati i održavati efikasne indekse za podršku aplikacijama.

Predlažem nekoliko savjeta za optimizaciju i poboljšanje korištenja indeksa.

Napomena: ispod prikazani upiti rade na neizmijenjenom uzorku baze podataka pagila..

Korištenje pokrivajućih indeksa (Covering Indexes)

Pogledajmo upit za izdvajanje adresa e-pošte za neaktivne korisnike. U tabeli customer ima kolonu aktivno, a upit je prilično jednostavan:

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        PLAN OUPITA
-----------------------------------------------------------
 Sekvencijalno skeniranje na customer  (trošak=0.00..16.49 redova=15 širina=32)
   Filter: (active = 0)
(2 reda)

Upit poziva cjelokupno sekvencijalno skeniranje tabele customer. Kreirajmo indeks za kolonu aktivno:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 PLAN OUPITA
-----------------------------------------------------------------------------
 Indeks skeniranje koristeći idx_cust1 na customer  (trošak=0.28..12.29 redova=15 širina=32)
   Indeks uvjet: (active = 0)
(2 reda)

Pomoglo je, sljedeće skeniranje se pretvorilo u „indeks skeniranje“. To znači da će Postgres skenirati indeks „idx_cust1“, a zatim će nastaviti pretragu po heap-u tabele, kako bi pročitao vrijednosti drugih kolona (u ovom slučaju, kolonu email), koje su potrebne upitu.

U PostgreSQL 11 su uvedeni pokrivajući indeksi. Oni omogućavaju uključivanje jedne ili više dodatnih kolona u sam indeks — njihove vrijednosti se čuvaju u skladištu podataka indeksa.

Da smo iskoristili ovu mogućnost i dodali vrijednost e-pošte unutar indeksa, Postgresu ne bi bilo potrebno tražiti u heap-u tabele vrijednost email. Pogledajmo hoće li to raditi:

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    PLAN OUPITA
----------------------------------------------------------------------------------
 Indeks samo skeniranje koristeći idx_cust2 na customer  (trošak=0.28..12.29 redova=15 širina=32)
   Indeks uvjet: (active = 0)
(2 reda)

«Indeks samo skeniranje“ nam govori da je upitu sada dovoljno samo jedan indeks, što pomaže izbjegavanju svih operacija ulaza/izlaza za čitanje heap-a tabele.

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

Korišćenje delimičnih indeksa

Delimični indeksi indeksiraju samo podskup redova tabele. To omogućava uštedu na veličini indeksa i brže izvođenje skeniranja.

Pretpostavimo da nam je potreban spisak e-mail adresa 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 spojene tabele:
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  (trošak=15.65..32.22 redova=9 širine=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (trošak=0.00..14.99 redova=599 širine=34)
   ->  Hash  (trošak=15.54..15.54 redova=9 širine=4)
         ->  Seq Scan on address a  (trošak=0.00..15.54 redova=9 širine=4)
               Filter: (district = 'California'::text)
(6 redova)

Šta će nam dati 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  (trošak=12.98..29.55 redova=9 širine=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (trošak=0.00..14.99 redova=599 širine=34)
   ->  Hash  (trošak=12.87..12.87 redova=9 širine=4)
         ->  Bitmap Heap Scan on address a  (trošak=4.34..12.87 redova=9 širine=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (trošak=0.00..4.34 redova=9 širine=0)
                     Index Cond: (district = 'California'::text)
(8 redova)

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

Pošto je ovo čest upit i potrebno ga je optimizovati, možemo koristiti delimični indeks koji indeksira samo one redove sa adresama gde je okrug ‘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  (trošak=12.38..28.96 redova=9 širine=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (trošak=0.00..14.99 redova=599 širine=34)
   ->  Hash  (trošak=12.27..12.27 redova=9 širine=4)
         ->  Index Only Scan using idx_address2 on address a  (trošak=0.14..12.27 redova=9 širine=4)
(5 redova)

Sada upit čita samo idx_address2 i ne dodiruje tabelu address.

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

Neke kolone koje treba indeksirati možda neće sadržavati skalarni tip podataka. Tipovi kolona poput jsonb, nizova i tsvector mogu sadržavati složene ili višestruke vrednosti. Ako potrebno indeksirati takve kolone, obično se mora pretraživati svaka pojedinačna vrednost u tim kolonama.

Pokušajmo pronaći nazive svih filmova koji sadrže isječke iz neuspelih dupliranja. U tabeli film postoji tekstualna kolona koja se zove special_features. Ako film ima ovo "posebno svojstvo", tada kolona sadrži element u obliku tekstualnog niza Behind The Scenes. Da bismo pronašli sve takve filmove, trebamo izabrati sve redove sa "Behind The Scenes" pri bilo kojim vrednostima niza special_features:

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

Operator umetnosti (containment operator) @> proverava da li je desna strana podskup leve strane.

Plan upita:

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)

Koji zahteva potpuno skeniranje hrpe sa troškovima 67.

Pogledajmo da li će nam običan B-stablo indeks pomoći:

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 čak nije razmatran. B-stablo indeks ne prepoznaje postojanje pojedinačnih elemenata u indeksiranim vrednostima.

Potrebna nam je 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 podržava mapiranje pojedinačnih vrednosti na indeksirane složene vrednosti, kao rezultat što će troškovi plana upita smanjiti više od polovine.

Rešavamo se dupliranja indeksa

Indeksi se akumuliraju tokom vremena, i ponekad novi indeks može sadržavati isto definiciju kao i jedan od prethodnih. Za čitljive SQL definicije indeksa možete koristiti katalog predstave pg_indexes. Takođe ćete moći lako pronaći identične definicije:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
I evo rezultata kada se izvrši na osnovnoj 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 dogoditi da vam se nakupi mnogo indeksa, od kojih jedan indeksira nadmnožak kolona, koje indeksiraju drugi indeksi. To može biti poželjno ili ne — nadmnožak može dovesti do skeniranja samo kroz indekse, što je dobro, ali može zauzimati previše prostora, ili zahtev, za optimizaciju kojeg je ovaj nadmnožak bio namenjen, više se ne koristi.

Ako želite automatizovati identifikaciju ovakvih indeksa, možete početi sa pg_index iz tabele pg_catalog.

Neiskorišćeni indeksi

Kako se razvijaju aplikacije koje koriste baze podataka, razvijaju se i njihovi zahtevi. Indeksi dodati ranije možda više nisu korišćeni ni jednim zahtevom. Prilikom svake analize indeksa, on se označava od strane menadžera statistike, i u prikazu sistemskog kataloga pg_stat_user_indexes možete proveriti vrednost idx_scan, koji je akumulativni brojač. Praćenje ove vrijednosti tokom određenog vremenskog perioda (recimo, mjesec dana) pružit će dobar uvid u to koje indekse nije moguće koristiti i koji se mogu ukloniti.

Evo upita za dobijanje trenutnih brojača skeniranja svih indeksa u šemi ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
izlaz kao što je ovo:
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 redova)

Ponovno kreiranje indeksa uz manje zaključavanja

Često se indeksi moraju ponovo kreirati, na primjer, kada se prošire i ponovo kreiranje može ubrzati skeniranje. Takođe, indeksi mogu biti oštećeni. Promjena parametara indeksa takođe može zahtijevati njegovo ponovno kreiranje.

Omogućavanje paralelnog kreiranja indeksa

U PostgreSQL 11, kreiranje B-Tree indeksa je konkurentno. Za ubrzanje procesa kreiranja može se koristiti nekoliko paralelno aktivnih radnika. Međutim, pobrinite se da su ovi konfiguracijski parametri ispravno postavljeni:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Zadane vrijednosti su preniske. Idealno, ove brojke treba povećavati zajedno s brojem jezgri procesora. Više informacija možete pročitati u dokumentaciji.

Pozadinsko kreiranje indeksa

Možete kreirati indeks u pozadini koristeći parametar CONCURRENTLY komandi CREATE INDEX:

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

Ova procedura kreiranja indeksa se razlikuje od obične po tome što ne zahtijeva zaključavanje tabele, što znači da ne blokira operacije pisanja. S druge strane, traje duže i troši više resursa.

Postgres pruža mnoge fleksibilne mogućnosti za kreiranje indeksa i rješavanje bilo kojih specifičnih slučajeva, a također nudi načine za upravljanje bazom podataka u slučaju eksplozivnog rasta vaše aplikacije. Nadamo se da će vam ovi savjeti pomoći da učinite upite bržim, a bazu spremnom za skaliranje.

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster