I Postgres-verden er indekser ekstremt viktige for effektiv navigering i databaselagringen (den kalles "heap"). Postgres støtter ikke for klustring, og MVCC-arkitekturen fører til at mange versjoner av den samme tuppen akkumuleres. Derfor er det veldig viktig å kunne lage og vedlikeholde effektive indekser for å støtte applikasjoner.
Jeg vil gjerne gi deg noen tips for optimalisering og forbedring av indekbruk.
Merk: Spørsmålene nedenfor fungerer på en uendret .
Bruk av dekkende indekser (Covering Indexes)
La oss se på en forespørsel for å hente e-postadresser for inaktive brukere. I tabellen customer finnes det en kolonne active, og forespørselen blir enkel:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Sekvensiell skanning på customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows) I forespørselen aktiveres en full sekvensiell skanning av tabellen customer. La oss opprette en 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
-----------------------------------------------------------------------------
Indeks-skanne ved bruk av idx_cust1 på customer (cost=0.28..12.29 rows=15 width=32)
Indeksbetingelse: (active = 0)
(2 rows) Det hjalp, den påfølgende skanningen ble til «index scan». Dette betyr at Postgres vil skanne indeksen «idx_cust1«, og deretter fortsette å søke i heapen i tabellen for å lese verdier fra andre kolonner (i dette tilfellet kolonnen email), som kreves av forespørselen.
I PostgreSQL 11 ble dekkende indekser introdusert. De gjør det mulig å inkludere en eller flere ekstra kolonner i indeksen selv - deres verdier lagres i datalagringen av indeksen.
Hvis vi hadde brukt denne muligheten og lagt til e-postverdien i indeksen, ville ikke Postgres trengt å søke i heapen i tabellen for 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
----------------------------------------------------------------------------------
Indeks bare skanne ved bruk av idx_cust2 på customer (cost=0.28..12.29 rows=15 width=32)
Indeksbetingelse: (active = 0)
(2 rows) «Indeks bare skanne» forteller oss at forespørselen nå kun trenger indeksen, noe som bidrar til å unngå alle diskoperasjoner for input/output for å lese heapen i tabellen.
I dag er de dekkende indeksene kun tilgjengelige for B-trær. Men i så fall vil vedlikeholdskostnadene være høyere.
Bruk av delvise indekser
Delvise indekser indekserer kun en undergruppe av radene i tabellen. Dette sparer plass og gjør skanningene raskere.
La oss si at vi trenger å få en liste over e-postadresser til kundene våre fra California. Spørsmålet vil være slik:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
med en spørringsplan som involverer å skanne begge tabellene som er koblet sammen:
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 gir oss vanlige indekser:
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 ble erstattet med indeksskanne idx_address1, og deretter ble en haug skannet address.
Siden dette er en hyppig spørring og må optimaliseres, kan vi bruke en delvis indeks som indekserer bare de radene med adresser der 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) Nå leser spørringen kun idx_address2 og berører ikke tabellen address.
Bruk av multivalue-indekser (Multi-Value Indexes)
Noen kolonner som må indekseres, kan mangle skalar datatyper. Kolonnetyper som jsonb, arrays og tsvector kan inneholde sammensatte eller multiværdi verdier. Hvis du må indeksere slike kolonner, må man vanligvis søke blant alle de individuelle verdiene i disse kolonnene.
La oss prøve å finne titlene på alle filmer som inneholder klipp fra mislykkede dublinger. I tabellen film finnes det en tekstkolonne som heter special_features. Hvis filmen har dette "særlige trekket", inneholder kolonnen et element i form av et tekstarray Behind The Scenes. For å søke etter alle slike filmer må vi velge alle rader med "Behind The Scenes" uansett verdier i arrayet. SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; special_features:
Containment-operatøren @> sjekker om høyre side er et delsett av venstre side. Spørringsplan:
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 skanning av bunken, med en kostnad på 67.La oss se om en vanlig B-tre indeks kan 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-tre indeksen er ikke klar over eksisterende elementer i de indekserte verdiene.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 samsvar av individuelle verdier med indekserte sammensatte verdier, noe som reduserer kostnaden for spørringsplanen med mer enn halvparten.Vi eliminerer duplisering av indekser.
Избавляемся от дублирования индексов
Indekser samles over tid, og noen ganger kan et nytt indeks ha samme definisjon som en av de tidligere. For å få SQL-definisjoner av indekser som er menneskelig lesbare, kan man bruke katalogvisningen pg_indexes. Du vil også kunne enkelt finne like definisjoner:
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 kjøres på den originale pagila-databasen:
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)
Indekser for overmengder (Superset Indexes)
Det kan hende at du har samlet mange indekser, der en av dem indekserer en overmenge av kolonner som andre indekserer. Dette kan være ønskelig eller ikke — overmengden kan føre til at det bare skannes gjennom indeksene, noe som er bra, men det kan også ta for mye plass, eller forespørselen som overmengden var ment å optimalisere, kan allerede ikke være i bruk.
Hvis du trenger å automatisere identifiseringen av slike indekser, så kan du begynne med fra tabellen pg_catalog.
Ubrukte indekser
Etter hvert som applikasjoner som bruker databaser utvikler seg, utvikler også de forespørslene de bruker seg. Indekser som ble lagt til tidligere, kan allerede ikke være i bruk av noen forespørsel. Hver gang en indeks skannes, blir den merket av statistikkbehandleren, og i systemkatalogen pg_stat_user_indexes kan man se verdien idx_scan, som er en kumulativ teller. Å spore dette tallet over en viss periode (for eksempel en måned) vil gi en god forståelse av hvilke indekser som ikke brukes og kan fjernes.
Her er forespørselen for å hente de nåværende skannerne til alle indekser i skjemaet 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
med utdata 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 rader)Rekonstruksjon av indekser med færre låser
Ofte må indekser rekonstruktureres, for eksempel når de vokser i størrelse, og rekonstruksjon kan øke skanningshastigheten. Indekser kan også bli ødelagt. Endring av indeksets parametere kan også kreve rekonstruksjon.
Vi aktiverer parallell indeksering
I PostgreSQL 11 er oppretting av B-Tree-indekser parallelt. For å akselerere prosessen kan flere arbeidere jobbe parallelt. Sørg imidlertid for at disse konfigurasjonsparameterne er riktig satt:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Standardverdiene er for små. Ideelt sett bør disse tallene økes i takt med antall prosessorkjerner. Les mer i .
Bakgrunnsindeksering
Du kan opprette en indeks i bakgrunnen ved å bruke parameteren CONCURRENTLY kommandoer CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenne prosedyren for indeksskapelse skiller seg fra den vanlige ved at den ikke krever å låse tabellen, og dermed blokkerer den ikke skriveoperasjoner. På den annen side tar den mer tid og bruker flere ressurser.
Postgres gir mange fleksible muligheter for opprettelse av indekser og løsninger for spesifikke tilfeller, samt gir metoder for databaseadministrasjon i tilfelle eksplosiv vekst av applikasjonen din. Vi håper disse tipsene vil hjelpe deg å gjøre forespørslene raske og forberede databasen for skalering.
Kilde: habr.com
Russian
Italian
German
Polish
Estonian
Albanian
Bulgarian
Croatian
Czech
Danish
Dutch
English
French
Hungarian
Latvian
Norwegian
Portuguese
Romanian
Slovak
Slovenian
Spanish
Swedish