I Postgres-verdenen er indekser afgørende for effektiv navigation af en databases lager (kaldet en "heap"). Postgres understøtter ikke clustering for det, og MVCC-arkitekturen får dig til at ende med mange versioner af den samme tuple. Derfor er det meget vigtigt at være i stand til at oprette og vedligeholde effektive indekser til at understøtte applikationer.
Her er nogle tips til optimering og forbedring af brugen af indekser.
Bemærk: forespørgslerne vist nedenfor fungerer på en uændret .
Brug af dækkende indekser
Lad os se på en anmodning om at udtrække e-mailadresser for inaktive brugere. Bord customer der er en søjle active, og forespørgslen er enkel:
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)
Forespørgslen påkalder den fulde tabelscanningssekvens customer. Lad os oprette et indeks på en kolonne 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)
Det hjalp, den efterfølgende scanning blev til "index scan". Det betyder, at Postgres vil scanne indekset "idx_cust1", og fortsæt derefter med at søge i tabelbunken for at læse værdierne af andre kolonner (i dette tilfælde kolonnen email), som forespørgslen har brug for.
Dækkende indekser introduceres i PostgreSQL 11. De giver dig mulighed for at inkludere en eller flere yderligere kolonner i selve indekset - deres værdier gemmes i indeksdatalageret.
Hvis vi udnyttede denne funktion og tilføjede e-mail-værdien i indekset, ville Postgres ikke behøve at søge i tabelbunken efter værdien. email. Lad os se, om dette vil virke:
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' fortæller os, at forespørgslen nu kun behøver et indeks, hvilket hjælper med at undgå al disk I/O for at læse tabelbunken.
Dækkende indekser er i øjeblikket kun tilgængelige for B-træer. Men i dette tilfælde vil vedligeholdelsesindsatsen være højere.
Brug af delvise indekser
Delvise indekser indekserer kun en delmængde af rækkerne i en tabel. Dette gemmer størrelsen af indekser og gør scanninger hurtigere.
Lad os sige, at vi ønsker at få en liste over vores kunders e-mailadresser i Californien. Anmodningen vil være sådan:
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)Hvad almindelige indekser vil give os:
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)
scanning address er blevet erstattet af indeksscanning idx_address1og scannede derefter bunken address.
Da dette er en hyppig forespørgsel og skal optimeres, kan vi bruge et delvist indeks, som kun indekserer de rækker med adresser, hvor distriktet ‘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)
Nu læser forespørgslen kun idx_address2 og rører ikke ved bordet address.
Brug af multi-værdiindekser
Nogle kolonner, der skal indekseres, indeholder muligvis ikke en skalær datatype. Kolonnetyper som jsonb, arrays и tsvector indeholde sammensatte eller flere værdier. Hvis du skal indeksere sådanne kolonner, skal du normalt søge gennem alle de individuelle værdier i disse kolonner.
Lad os prøve at finde titlerne på alle film, der indeholder klip fra mislykkede optagelser. Bord film der hedder en tekstkolonne special_features. Hvis filmen har denne "særlige egenskab", så indeholder kolonnen elementet som et tekstarray Behind The Scenes. For at søge efter alle sådanne film skal vi vælge alle rækker med "Behind The Scenes" hvornår nogen matrixværdier special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nesting operatør @> kontrollerer, om højre side er en delmængde af venstre side.
Anmod om plan:
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)Som anmoder om en fuld heap-scanning med en pris på 67.
Lad os se, om et almindeligt B-træ-indeks hjælper os:
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)Indekset blev ikke engang taget i betragtning. B-træindekset er ikke opmærksom på eksistensen af individuelle elementer i de indekserede værdier.
Vi har brug for et 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-indekset understøtter kortlægning af enkeltværdier mod indekserede sammensatte værdier, hvilket resulterer i en forespørgselsplanomkostning, der er mere end halveret.
At slippe af med duplikerede indekser
Indekser akkumuleres over tid, og nogle gange kan et nyt indeks indeholde den samme definition som et af de tidligere. Du kan bruge katalogvisningen til at få menneskelæselige SQL-definitioner af indekser. pg_indexes. Du kan også nemt finde identiske definitioner:
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)
Supersæt-indekser
Det kan ske, at du ender med mange indekser, hvoraf det ene indekserer et supersæt af kolonner, der indekserer andre indekser. Dette kan være ønskeligt eller måske ikke - supersættet kan resultere i kun indeksscanninger, hvilket er godt, men det kan tage for meget plads, eller den forespørgsel, som supersættet var beregnet til at optimere, bruges ikke længere.
Hvis du har brug for at automatisere definitionen af sådanne indekser, kan du starte med fra bordet pg_catalog.
Ubrugte indekser
Efterhånden som applikationer, der bruger databaser, udvikler sig, så udvikler de forespørgsler, de bruger. Indekser tilføjet tidligere må ikke længere bruges af nogen forespørgsel. Hver gang et indeks scannes, markeres det af statistikadministratoren og i systemkatalogvisningen pg_stat_user_indexes du kan se værdien idx_scan, som er en kumulativ tæller. Sporing af denne værdi over en periode (f.eks. en måned) vil give en god idé om, hvilke indekser der ikke bliver brugt og kan blive droppet.
Her er en forespørgsel for at få det aktuelle scanningstal for alle indekser i skemaet ‘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)Genopbygning af indekser med færre låse
Indekser skal ofte genopbygges, for eksempel når de bliver oppustede, og genopbygning kan fremskynde scanningen. Også indekser kan blive beskadiget. Ændring af indeksparametrene kan også kræve genopbygning af det.
Aktiver oprettelse af parallel indeks
I PostgreSQL 11 er oprettelse af et B-Tree-indeks samtidig. For at fremskynde oprettelsesprocessen kan der bruges flere parallelarbejdere. Sørg dog for, at disse konfigurationsindstillinger er indstillet korrekt:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Standardværdierne er for små. Ideelt set bør disse tal stige sammen med antallet af processorkerner. Læs mere i .
Oprettelse af baggrundsindeks
Du kan oprette et indeks i baggrunden ved hjælp af indstillingen CONCURRENTLY hold CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenne indeksoprettelsesprocedure adskiller sig fra den sædvanlige ved, at den ikke kræver en lås på bordet og derfor ikke blokerer skriveoperationer. På den anden side tager det mere tid og bruger flere ressourcer.
Postgres giver en masse fleksibilitet til at oprette indekser og måder at løse eventuelle specielle tilfælde på, samt måder at administrere databasen i tilfælde af, at din applikation vokser eksploderende. Vi håber, at disse tips vil hjælpe dig med at få dine forespørgsler hurtigt og din database klar til at skalere.
Kilde: www.habr.com