U svijetu Postgresa indeksi su izuzetno važni za učinkovitu navigaciju po skladištu baze podataka (koje se naziva „heap“). Postgres ne podržava klasterizaciju za njega, a MVCC arhitektura dovodi do nakupljanja mnogo verzija istog reda. Stoga je vrlo važno znati kako stvarati i održavati učinkovite indekse za podršku aplikacijama.
Predlažem vam nekoliko savjeta za optimizaciju i poboljšanje korištenja indeksa.
Napomena: u nastavku navedeni upiti rade na neizmijenjenom .
Korištenje pokrivnih indeksa (Covering Indexes)
Pogledajmo upit za dobivanje adresa elektroničke pošte neaktivnih korisnika. U tablici customer postoji kolona aktivno, a upit je jednostavan:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN UPITA
-----------------------------------------------------------
Seq Scan on customer (trošak=0.00..16.49 redova=15 širina=32)
Filter: (active = 0)
(2 reda) U upitu se poziva cijela sekvenca skeniranja tablice. customerStvorimo indeks za kolonu aktivno:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN UPITA
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (trošak=0.28..12.29 redova=15 širina=32)
Index Cond: (active = 0)
(2 reda) Pomoglo je, naknadno skeniranje postalo je „index scan“. To znači da će Postgres skenirati indeks „idx_cust1“, a zatim će nastaviti pretraživanje po hrpi tablice kako bi pročitao vrijednosti drugih kolona (u ovom slučaju, kolonu email), koje su potrebne za upit.
U PostgreSQL 11 uvedeni su pokrivni indeksi. Oni omogućuju uključivanje jedne ili više dodatnih kolona u sam indeks – njihovi se vrijednosti pohranjuju u skladištu podataka indeksa.
Da smo iskoristili ovu mogućnost i dodali vrijednost elektroničke pošte unutar indeksa, onda Postgres ne bi trebao tražiti u hrpi tablice 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 UPITA
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (trošak=0.28..12.29 redova=15 širina=32)
Index Cond: (active = 0)
(2 reda) «Index Only Scan» nam govori da je upitu sada dovoljan samo indeks, što pomaže izbjeći sve diskovne ulazno/izlazne operacije za čitanje hrpe tablice.
Trenutno su pokrivački indeksi dostupni samo za B-stabla. Međutim, u tom slučaju troškovi održavanja bit će veći.
Korištenje djelomičnih indeksa
Djelomični indeksi indeksiraju samo podskup redaka tablice. To pomaže u smanjenju veličine indeksa i bržem izvođenju skeniranja.
Pretpostavimo da nam treba popis e-mail adresa naših klijenata iz Kalifornije. Upit će biti sljedeći:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
što ima plan upita koji uključuje skeniranje obje povezane tablice:
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 redaka)Što nam donose 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)
Ponovno provjerite 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 redaka) Skeniranje address zamijenjeno je skeniranjem indeksa idx_address1, a zatim je skenirana gomila address.
Budući da je ovo čest upit koji treba optimizirati, možemo koristiti djelomični indeks koji indeksira samo one retke s adresama čija 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 redaka) Sada upit čita samo idx_address2 i ne dodiruje tablicu address.
Korištenje višeznačnih indeksa (Multi-Value Indexes)
Neke kolone koje je potrebno 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 trebate indeksirati takve kolone, obično je potrebno pretraživati sve pojedinačne vrednosti unutar tih kolona.
Pokušaćemo da pronađemo naslove svih filmova koji sadrže isečke iz neuspelih dupliranja. U tabeli film postoji tekstualna kolona koja se zove special_features. Ako film ima to „posebno svojstvo“, onda u koloni postoji element u obliku tekstualnog niza Behind The Scenes. Da bismo pronašli sve takve filmove, moramo 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 sadrži (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 po ceni od 67.
Hajde da vidimo može li nam uobičajeni B-drvo 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 ni razmatran. B-drvo indeks ne može pretpostaviti 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 poređenje pojedinačnih vrednosti sa indeksiranim složenim vrednostima, kao rezultat čega će se trošak plana upita smanjiti više od polovine.
Osvežavamo se od duplikacije indeksa
Indeksi se akumuliraju s vremenom, a ponekad novi indeks može sadržavati isto definiranje kao i jedan od prethodnih. Da biste dobili SQL definicije indeksa čitljive za ljude, možete koristiti katalogizirano prikazivanje pg_indexes. Također ćete lako pronaći ista definicija:
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 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 se akumulira mnogo indeksa, od kojih jedan indeksira nadmnoštvo kolona koje indeksiraju drugi indeksi. To može biti i poželjno i ne - nadmnoštvo može dovesti do skeniranja samo putem indeksa, što je dobro, ali može zauzimati previše prostora, ili upit za optimizaciju kojeg je nadmnoštvo bilo namijenjeno više nije korišten.
Ako trebate automatizirati određivanje takvih indeksa, možete početi s iz tablice pg_catalog.
Neiskorišteni indeksi
Kako se aplikacije koje koriste baze podataka razvijaju, razvijaju se i upiti koje koriste. Indeksi dodani ranije možda više nisu korišteni nijednim upitom. Prilikom svakog skeniranja indeksa, označava ga upravitelj statistike, a u prikazu sistemskog kataloga pg_stat_user_indexes možete vidjeti vrijednost idx_scan, koji je kumulativni brojčanik. Praćenje te vrijednosti tijekom određenog vremenskog razdoblja (recimo, mjesec) pružit će dobar uvid u to koji se indeksi ne koriste i mogu biti uklonjeni.
Evo upita za dobivanje trenutnih brojčanika skeniranja svih indeksa u shemi ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
izlaz može izgledati ovako:
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 s manje blokada
Često se indeksi moraju ponovno izraditi, na primjer, kada se prekomjerno povećaju, a njihovo ponovno stvaranje može ubrzati skeniranje. Također, indeksi mogu biti oštećeni. Promjena parametara indeksa također može zahtijevati ponovno stvaranje.
Omogućavanje paralelnog stvaranja indeksa
U PostgreSQL 11 stvaranje B-Tree indeksa je konkurentno. Za ubrzanje procesa stvaranja može se koristiti više radnika koji rade paralelno. Međutim, provjerite jesu li ovi konfiguracijski parametri postavljeni ispravno:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Zadane vrijednosti su premale. U idealnom bi slučaju, ove brojeve trebalo povećati zajedno s brojem CPU jezgri. Više informacija možete pronaći u .
Pozadinsko stvaranje indeksa
Možete stvoriti indeks u pozadini koristeći parametar CONCURRENTLY komandi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXOva procedura stvaranja indeksa razlikuje se od uobičajene jer ne zahtijeva blokiranje tablice, što znači da ne blokira operacije pisanja. S druge strane, traje dulje i koristi više resursa.
Postgres nudi mnoštvo fleksibilnih mogućnosti za stvaranje indeksa i rješavanje posebnih slučajeva, kao i načine za upravljanje bazom podataka u slučaju eksplozivnog rasta vaše aplikacije. Nadamo se da će vam ovi savjeti pomoći da vaše upite učinite bržima, a bazu spremnom za skaliranje.
Izvor: habr.com
