I Postgres-verdenen er indekser ekstremt vigtige for effektiv navigation i databasen (den kaldes "heap"). Postgres understøtter ikke klustering, og MVCC-arkitekturen fører til, at der ophober sig mange versioner af den samme tuple. Derfor er det meget vigtigt at kunne skabe og vedligeholde effektive indekser til at understøtte applikationer.
Jeg præsenterer her nogle råd til optimering og forbedring af brugen af indekser.
Bemærk: de nedenstående forespørgsler fungerer på en uændret .
Brug af dækkende indekser (Covering Indexes)
Lad os se på en forespørgsel for at hente e-mailadresser for inaktive brugere. I tabellen customer er der en kolonne active, og forespørgslen bliver ikke kompliceret:
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 udfører en fuld sekventiel scanning af tabellen. customerLad os oprette et indeks for kolonnen 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 en "index scan". Det betyder, at Postgres vil scanne indekset "idx_cust1" og derefter fortsætte med at lede i heap'en for at læse værdierne fra andre kolonner (i dette tilfælde kolonnen email), som er nødvendige for forespørgslen.
Dækkende indekser blev introduceret i PostgreSQL 11. De tillader at inkludere en eller flere ekstra kolonner i indekset — deres værdier gemmes i datalageret af indekset.
Hvis vi udnyttede denne mulighed og tilføjede e-mailadressen i indekset, ville Postgres ikke behøve at lede i heap'en for at finde værdien email. Lad os se, om det virker:
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 kræver én indeks, hvilket hjælper med at undgå alle I/O-diskoperationer for at læse en bunke tabel.
I dag er dækningsindekser kun tilgængelige for B-træer. I dette tilfælde vil vedligeholdelsesindsatsen dog være højere.
Brug af partielle indekser
Partielle indekser indekserer kun et undersæt af rækkerne i tabellen. Dette sparer plads for indekserne og gør scanning hurtigere.
Lad os antage, at vi skal have en liste over e-mailadresser fra vores kunder i Californien. Forespørgslen 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';
Som har en forespørgselsplan, der indebærer scanning af begge de sammenkoblede tabeller:
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 vil almindelige indekser 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) Scanningen address blev erstattet med indeksscanning idx_address1, og derefter blev bunken scannet address.
Da dette er en hyppig forespørgsel og skal optimeres, kan vi bruge et partielt indeks, der 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 tabellen address.
Brug af flerværdige indekser (Multi-Value Indexes)
Nogle kolonner, der skal indekseres, kan indeholde ikke-skalar datatyper. Kolonnetyper såsom jsonb, arrays og tsvector kan indeholde sammensatte eller multiple værdier. Hvis du skal indeksere sådanne kolonner, skal du normalt søge efter alle de enkelte værdier i disse kolonner.
Lad os prøve at finde titlerne på alle film, der indeholder klip fra mislykkede dubler. I tabellen film er der en tekstkolonne, der hedder special_features. Hvis en film har dette «særlige kendetegn», så findes der et element i form af et tekstarray Behind The Scenes. For at finde alle sådanne film skal vi vælge alle rækker med «Behind The Scenes» ved enhver værdi af arrayet special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Inklusionsoperatøren (containment operator) @> tjekker, om højre side er et delmængde af venstre side.
Spørgselsplan:
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 scanning af bunken til en omkostning 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 overvejet. B-træindekset aner ikke, at der findes enkeltstående 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 match af individuelle værdier med de indekserede sammensatte værdier, hvilket resulterer i, at omkostningerne ved forespørgselsplanen reduceres mere end halvdelen.
Lad os slippe for duplikering af indekser.
Indekser ophobes over tid, og nogle gange kan et nyt indeks indeholde den samme definition som et af de tidligere. For at få menneskeligt læselige SQL-definitioner for indekser kan man bruge katalogvisningen. pg_indexes. Du vil også nemt kunne finde identiske definitioner:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
Og her er resultatet, når det køres på den standard 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)
Superset Indekser
Det kan ske, at du får mange indekser, hvoraf én indekserer en overmængde af kolonner, som indekserer andre indekser. Dette kan være både ønskeligt og ikke — en overmængde kan føre til at scanne kun via indekserne, hvilket er godt, men det kan også optage for meget plads, eller den forespørgsel, som denne overmængde var beregnet til at optimere, anvendes ikke længere.
Hvis du har brug for at automatisere identifikationen af sådanne indekser, kan du begynde med fra tabellen pg_catalog.
Ubrugte indekser
Efterhånden som applikationer, der bruger databaser, udvikler sig, udvikler de anvendte forespørgsler sig også. Tidligere tilføjede indekser kan ikke længere anvendes af nogen forespørgsler. Ved hver scanning af indekset bliver det markeret af statistikbehandleren, og værdi kan ses i systemkatalogvistelsen pg_stat_user_indexes kan ses som værdi idx_scan, der er en kumulativ tæller. At overvåge denne værdi over en periode (for eksempel en måned) vil give et godt indblik i, hvilke indeks der ikke bruges og kan slettes.
Her er forespørgslen for at hente de nuværende scannings-tællere for alle indeks i skemaet ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
med output som dette:
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 rækker)Genoprettelse af indekser med færre låsninger
Indekser skal ofte genoprettes, når de for eksempel vokser i størrelse, og en genoprettelse kan fremskynde scanning. Indekser kan også blive beskadiget. Ændring af indeksparametrene kan også kræve en genoprettelse.
Aktivering af parallel oprettelse af indekser
I PostgreSQL 11 er oprettelsen af et B-Tree-indeks parallel. For at fremskynde oprettelsesprocessen kan flere arbejdere køre parallelt. Sørg dog for, at disse konfigurationsparametre 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 øges i takt med antallet af CPU-kerner. Læs mere i .
Baggrundsoprettelse af indekser
Du kan oprette et indeks i baggrunden ved at bruge parameteren CONCURRENTLY kommandoer CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenne indeksoprettelsesprocedure adskiller sig fra den almindelige ved, at den ikke kræver låsning af tabellen, hvilket betyder, at den ikke blokerer skriveoperationer. På den anden side tager den længere tid og bruger flere ressourcer.
Postgres tilbyder mange fleksible muligheder for at oprette indekser og løse specifikke tilfælde samt administrere databasen i tilfælde af eksplosiv vækst af din applikation. Vi håber, at disse råd vil hjælpe dig med at gøre forespørgsler hurtigere og databasen klar til at skalere.
Kilde: habr.com
