I Postgres-verdenen er indekser avgjørende for å effektivt navigere i databaselagringen (kalt haugen). Postgres støtter ikke clustering for det, og MVCC-arkitekturen fører til at du ender opp med mange versjoner av samme tuppel. Derfor er det svært viktig å kunne lage og vedlikeholde effektive indekser for å støtte applikasjoner.
Jeg gjør deg oppmerksom på noen tips for å optimalisere og forbedre bruken av indekser.
Merk: Spørringene vist nedenfor fungerer på uendret .
Bruke dekkende indekser
La oss se på en forespørsel om å hente e-postadresser for inaktive brukere. I bordet customer det er en kolonne active, og spørringen 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)
Spørringen kaller opp hele tabellskanningssekvensen customer. La oss lage en indeks på 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)
Hjelpet, påfølgende skanning ble til "index scan". Dette betyr at Postgres vil gjennomsøke indeksen "idx_cust1", og fortsett å søke i tabellhaugen for å lese verdiene til andre kolonner (i dette tilfellet kolonnen email) som forespørselen trenger.
PostgreSQL 11 introduserte dekker indekser. De lar deg inkludere en eller flere ekstra kolonner i selve indeksen - verdiene deres er lagret i indeksdatalageret.
Hvis vi brukte denne funksjonen og la til e-postverdien i indeksen, ville ikke Postgres trenge å se i tabellhaugen etter verdien email. La oss se om dette fungerer:
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" forteller oss at spørringen nå bare trenger indeksen, noe som bidrar til å unngå all disk I/O for å lese tabellens haug.
I dag er dekningsindekser kun tilgjengelig for B-trær. Men i dette tilfellet vil vedlikeholdsinnsatsen være høyere.
Bruke delvise indekser
Delvise indekser indekserer bare et undersett av tabellens rader. Dette lar deg lagre indeksstørrelse og utføre skanninger raskere.
La oss si at vi trenger å få en liste over e-postadresser til våre kunder i California. Forespørselen vil være slik:
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)Hva vanlige indekser vil gi oss:
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)
skanning address er erstattet av indeksskanning idx_address1, og så ble haugen skannet address.
Siden dette er en hyppig spørring og må optimaliseres, kan vi bruke en delvis indeks, som kun indekserer de radene med adresser der området ‘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)
Nå lyder forespørselen bare idx_address2 og berører ikke bordet address.
Bruke multi-verdiindekser
Noen kolonner som må indekseres, inneholder kanskje ikke en skalardatatype. Kolonnetyper som jsonb, arrays и tsvector inneholder sammensatte eller flere betydninger. Hvis du trenger å indeksere slike kolonner, må du vanligvis søke etter alle de individuelle verdiene i disse kolonnene.
La oss prøve å finne titlene på alle filmene som inneholder klipp fra mislykkede opptak. I bordet film det er en tekstkolonne som heter special_features. Hvis en film har denne "spesielle egenskapen", så inneholder kolonnen et element i form av en tekstmatrise Behind The Scenes. For å søke etter alle slike filmer, må vi velge alle rader med "Behind The Scenes" på noen matriseverdier special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Inneslutningsoperatør @> sjekker om høyre side er en delmengde av venstre side.
Søkeplan:
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 ber om en full heap-skanning med en kostnad på 67.
La oss se om en vanlig B-treindeks vil hjelpe oss:
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)Indeksen ble ikke engang vurdert. B-treeindeksen har ingen anelse om eksistensen av individuelle elementer i verdiene den indekserer.
Vi trenger en 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-indeksen støtter kartlegging av individuelle verdier til indekserte sammensatte verdier, noe som resulterer i mer enn halvparten av kostnadene for spørringsplanen.
Bli kvitt dupliserte indekser
Indekser akkumuleres over tid, og noen ganger kan en ny indeks inneholde samme definisjon som en av de forrige. Du kan bruke katalogvisningen til å få menneskelesbare SQL-indeksdefinisjoner pg_indexes. Du kan også enkelt finne de samme definisjonene:
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)
Supersett-indekser
Det kan skje at du samler mange indekser, hvorav en indekserer et supersett av kolonner som andre indekser indekserer. Dette kan være ønskelig eller ikke - et supersett kan resultere i en kun indeksskanning, noe som er bra, men det kan ta for mye plass, eller spørringen supersettet var ment å optimalisere er ikke lenger i bruk.
Hvis du trenger å automatisere definisjonen av slike indekser, kan du begynne med fra bordet pg_catalog.
Ubrukte indekser
Etter hvert som applikasjoner som bruker databaser utvikler seg, utvikler også søkene de bruker. Tidligere lagt til indekser kan ikke lenger brukes av noen søk. Hver gang en indeks skannes, noteres den av statistikkbehandleren og i systemkatalogvisningen pg_stat_user_indexes du kan se verdien idx_scan, som er en kumulativ teller. Å spore denne verdien over en periode (for eksempel en måned) vil gi en god ide om hvilke indekser som ikke brukes og kan bli droppet.
Her er en forespørsel om å få gjeldende skanningstall for alle indekser i skjemaet ‘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)Gjenoppbygging av indekser med færre låser
Indekser må ofte gjenskapes, for eksempel når de blir oppblåste, og gjenskaping kan øke hastigheten på skanningen. Indekser kan også bli ødelagt. Endring av indeksparametere kan også kreve at de gjenskapes.
Aktiver oppretting av parallell indeks
I PostgreSQL 11 er B-Tree-indeksoppretting samtidig. For å få fart på opprettelsesprosessen kan flere parallelle arbeidere brukes. Pass imidlertid på at disse konfigurasjonsinnstillingene er riktig angitt:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Standardverdiene er for små. Ideelt sett bør disse tallene øke sammen med antall prosessorkjerner. Les mer i .
Oppretting av bakgrunnsindeks
Du kan lage indeksen i bakgrunnen ved å bruke alternativet CONCURRENTLY kommandoer CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenne indeksopprettingsprosedyren skiller seg fra den vanlige ved at den ikke krever låsing av tabellen, og blokkerer derfor ikke skriveoperasjoner. På den annen side tar det lengre tid og bruker mer ressurser.
Postgres gir mange fleksible alternativer for å lage indekser og løsninger for eventuelle spesielle tilfeller, og gir også måter å administrere databasen på hvis applikasjonen din vokser eksplosivt. Vi håper disse tipsene vil hjelpe deg med å gjøre spørringene dine raske og databasen klar til å skalere.
Kilde: www.habr.com