U svijetu Postgresa, indeksi su bitni za učinkovitu navigaciju pohrane baze podataka (nazvane "hrpa"). Postgres ne podržava klasteriranje za njega, a MVCC arhitektura uzrokuje da završite s mnogo verzija iste torke. Stoga je vrlo važno biti u mogućnosti stvoriti i održavati učinkovite indekse za podršku aplikacijama.
Evo nekoliko savjeta za optimizaciju i poboljšanje upotrebe indeksa.
Napomena: dolje prikazani upiti rade na neizmijenjenom .
Korištenje indeksa pokrivanja
Pogledajmo zahtjev za izdvajanje adresa e-pošte za neaktivne korisnike. Stol customer postoji stupac active, a upit je jednostavan:
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)
Upit pokreće sekvencu skeniranja cijele tablice customer. Kreirajmo indeks na stupcu 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)
Pomoglo je, naknadno skeniranje pretvorilo se u "index scan". To znači da će Postgres skenirati indeks "idx_cust1", a zatim nastavite pretraživati hrpu tablice da biste pročitali vrijednosti drugih stupaca (u ovom slučaju stupca email) koje upit treba.
Pokrivajući indeksi uvedeni su u PostgreSQL 11. Omogućuju vam da uključite jedan ili više dodatnih stupaca u sam indeks - njihove se vrijednosti pohranjuju u pohranu podataka indeksa.
Ako bismo iskoristili ovu značajku i dodali vrijednost e-pošte unutar indeksa, tada Postgres ne bi trebao pretraživati vrijednost u hrpi tablice. email. Da vidimo hoće li ovo uspjeti:
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' govori nam da je upitu sada potreban samo indeks, što pomaže u izbjegavanju svih I/O diska za čitanje hrpe tablice.
Indeksi pokrivanja trenutno su dostupni samo za B-stabla. Međutim, u ovom će slučaju napori održavanja biti veći.
Korištenje parcijalnih indeksa
Djelomični indeksi indeksiraju samo podskup redaka u tablici. Ovo štedi veličinu indeksa i čini skeniranje bržim.
Recimo da želimo dobiti popis adresa e-pošte naših klijenata u Kaliforniji. Zahtjev će biti ovakav:
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)Što će nam obični indeksi dati:
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)
skenirati address je zamijenjeno skeniranjem indeksa idx_address1a zatim skenirao hrpu address.
Budući da je ovo čest upit i treba ga optimizirati, možemo koristiti djelomični indeks, koji indeksira samo one retke s adresama u kojima 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';
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)
Sada upit samo glasi idx_address2 i ne dodiruje stol address.
Korištenje indeksa s više vrijednosti
Neki stupci koje treba indeksirati možda ne sadrže skalarni tip podataka. Vrste stupaca poput jsonb, arrays и tsvector sadrže složene ili višestruke vrijednosti. Ako trebate indeksirati takve stupce, obično morate pretraživati sve pojedinačne vrijednosti u tim stupcima.
Pokušajmo pronaći naslove svih filmova koji sadrže isječke iz neuspješnih kadrova. Stol film postoji tekstualni stupac tzv special_features. Ako film ima ovo "posebno svojstvo", tada stupac sadrži element kao tekstualno polje Behind The Scenes. Da bismo pretražili sve takve filmove, moramo odabrati sve retke s "Behind The Scenes" kada bilo koji vrijednosti polja special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator gniježđenja @> provjerava je li desna strana podskup lijeve strane.
Plan zahtjeva:
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)Što zahtijeva potpuno heap skeniranje uz cijenu od 67.
Da vidimo pomaže li nam regularni indeks B-stabla:
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 nije ni razmatran. Indeks B-stabla nije svjestan postojanja pojedinačnih elemenata u indeksiranim vrijednostima.
Trebamo 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 vrijednosti u odnosu na indeksirane kompozitne vrijednosti, što rezultira troškom plana upita koji je više nego prepolovljen.
Rješavanje dupliciranih indeksa
Indeksi se nakupljaju tijekom vremena, a ponekad novi indeks može sadržavati istu definiciju kao jedan od prethodnih. Možete koristiti kataloški pogled da dobijete SQL definicije indeksa čitljive ljudima. pg_indexes. Također možete 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;
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)
Indeksi superskupa
Može se dogoditi da završite s mnogo indeksa, od kojih jedan indeksira nadskup stupaca koji indeksiraju druge indekse. Ovo može, ali ne mora biti poželjno - nadskup može rezultirati skeniranjem samo indeksa, što je dobro, ali može zauzeti previše prostora ili se upit koji je nadskup trebao optimizirati više ne koristi.
Ako trebate automatizirati definiciju takvih indeksa, možete početi s sa stola pg_catalog.
Neiskorišteni indeksi
Kako se razvijaju aplikacije koje koriste baze podataka, tako se razvijaju i upiti koje koriste. Indeksi dodani ranije ne mogu se više koristiti ni za jedan upit. Svaki put kada se indeks skenira, označava ga upravitelj statistike iu prikazu kataloga sustava pg_stat_user_indexes možete vidjeti vrijednost idx_scan, što je kumulativni brojač. Praćenje ove vrijednosti tijekom određenog vremenskog razdoblja (recimo mjesec dana) dat će dobru ideju o tome koji se indeksi ne koriste i koji bi mogli biti odbačeni.
Ovdje je upit za dobivanje trenutnog broja skeniranja svih indeksa u shemi ‘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)Ponovna izgradnja indeksa s manje zaključavanja
Indekse je često potrebno ponovno izgraditi, na primjer kada postanu napuhani, a ponovna izgradnja može ubrzati skeniranje. Također se indeksi mogu oštetiti. Promjena parametara indeksa također može zahtijevati njegovu ponovnu izgradnju.
Omogući stvaranje paralelnog indeksa
U PostgreSQL 11, stvaranje B-Tree indeksa je istovremeno. Kako bi se ubrzao proces stvaranja, može se koristiti nekoliko paralelnih radnika. Međutim, provjerite jesu li ove konfiguracijske opcije ispravno postavljene:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Zadane vrijednosti su premale. U idealnom slučaju, ti bi se brojevi trebali povećavati zajedno s brojem jezgri procesora. Pročitajte više u .
Stvaranje pozadinskog indeksa
Možete stvoriti indeks u pozadini koristeći opciju CONCURRENTLY naredbe CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXOvaj postupak stvaranja indeksa razlikuje se od uobičajenog po tome što ne zahtijeva zaključavanje tablice i stoga ne blokira operacije pisanja. S druge strane, potrebno je više vremena i više resursa.
Postgres pruža puno fleksibilnosti za stvaranje indeksa i načine rješavanja posebnih slučajeva, kao i načine upravljanja bazom podataka u slučaju da vaša aplikacija eksplozivno raste. Nadamo se da će vam ovi savjeti pomoći da brzo postavite svoje upite i da vaša baza podataka bude spremna za povećanje.
Izvor: www.habr.com