V svetu Postgres so indeksi bistveni za učinkovito krmarjenje po pomnilniku baze podatkov (imenovanem "kup"). Postgres zanj ne podpira združevanja v gruče, arhitektura MVCC pa povzroči, da na koncu dobite veliko različic istega niza. Zato je zelo pomembno, da lahko ustvarite in vzdržujete učinkovite indekse za podporo aplikacijam.
Tukaj je nekaj nasvetov za optimizacijo in izboljšanje uporabe indeksov.
Opomba: spodaj prikazane poizvedbe delujejo na nespremenjenem .
Uporaba pokrivnih indeksov
Poglejmo zahtevo za pridobivanje e-poštnih naslovov za neaktivne uporabnike. Tabela customer obstaja stolpec active, poizvedba pa je preprosta:
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)
Poizvedba prikliče zaporedje skeniranja celotne tabele customer. Ustvarimo indeks na stolpcu 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)
Pomagalo je, naknadno skeniranje se je spremenilo v "index scan". To pomeni, da bo Postgres skeniral indeks "idx_cust1«, nato pa nadaljujte z iskanjem po kopici tabele, da preberete vrednosti drugih stolpcev (v tem primeru stolpca email), ki jih potrebuje poizvedba.
Pokrivni indeksi so predstavljeni v PostgreSQL 11. Omogočajo vam, da v sam indeks vključite enega ali več dodatnih stolpcev - njihove vrednosti so shranjene v shrambi podatkov indeksa.
Če bi izkoristili to funkcijo in dodali e-poštno vrednost znotraj indeksa, potem Postgresu ne bi bilo treba iskati vrednosti v kopici tabel. email. Poglejmo, ali bo to delovalo:
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' nam pove, da poizvedba zdaj potrebuje samo indeks, ki pomaga preprečiti vse V/I diska za branje kopice tabele.
Pokrivni indeksi so trenutno na voljo samo za B-drevesa. Vendar bo v tem primeru napor vzdrževanja večji.
Uporaba delnih indeksov
Delni indeksi indeksirajo samo podnabor vrstic v tabeli. To prihrani velikost indeksov in omogoča hitrejše skeniranje.
Recimo, da želimo pridobiti seznam e-poštnih naslovov naših strank v Kaliforniji. Zahteva bo takšna:
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)Kaj nam bodo dali običajni indeksi:
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)
Optično branje address je bil nadomeščen s skeniranjem indeksa idx_address1in nato skeniral kup address.
Ker je to pogosta poizvedba in jo je treba optimizirati, lahko uporabimo delni indeks, ki indeksira samo tiste vrstice z naslovi, v katerih okrožje ‘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)
Zdaj se poizvedba samo bere idx_address2 in se ne dotika mize address.
Uporaba indeksov z več vrednostmi
Nekateri stolpci, ki jih je treba indeksirati, morda ne vsebujejo skalarnega podatkovnega tipa. Vrste stolpcev, kot so jsonb, arrays и tsvector vsebujejo sestavljene ali več vrednosti. Če morate indeksirati takšne stolpce, morate običajno iskati po vseh posameznih vrednostih v teh stolpcih.
Poskusimo poiskati naslove vseh filmov, ki vsebujejo izreze iz neuspešnih posnetkov. Tabela film obstaja besedilni stolpec, imenovan special_features. Če ima film to "posebno lastnost", potem stolpec vsebuje element kot besedilno polje Behind The Scenes. Za iskanje vseh takšnih filmov moramo izbrati vse vrstice z "Behind The Scenes". vse vrednosti niza special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operater gnezdenja @> preveri, ali je desna stran podmnožica leve strani.
Zahtevaj načrt:
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)Ki zahteva celotno skeniranje kopice s ceno 67.
Poglejmo, ali nam običajni indeks B-drevesa pomaga:
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)Indeksa sploh niso upoštevali. Indeks B-drevesa ne pozna obstoja posameznih elementov v indeksiranih vrednostih.
Potrebujemo indeks GIN.
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)Indeks GIN podpira preslikavo posameznih vrednosti v primerjavi z indeksiranimi sestavljenimi vrednostmi, zaradi česar je strošek načrta poizvedbe več kot prepolovljen.
Znebite se podvojenih indeksov
Indeksi se sčasoma kopičijo in včasih lahko nov indeks vsebuje enako definicijo kot eden od prejšnjih. Pogled kataloga lahko uporabite za pridobitev človeku berljivih definicij SQL indeksov. pg_indexes. Prav tako lahko zlahka najdete enake 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 nadmnožic
Lahko se zgodi, da imate na koncu veliko indeksov, od katerih eden indeksira nadmnožico stolpcev, ki indeksirajo druge indekse. To je lahko zaželeno ali pa ne – nadmnožica lahko povzroči skeniranje samo indeksa, kar je dobro, vendar lahko zavzame preveč prostora ali pa se poizvedba, ki naj bi jo nadnabor optimiziral, ne uporablja več.
Če morate avtomatizirati definicijo takšnih indeksov, lahko začnete z iz mize pg_catalog.
Neuporabljeni indeksi
Z razvojem aplikacij, ki uporabljajo baze podatkov, se razvijajo tudi poizvedbe, ki jih uporabljajo. Prej dodanih indeksov ne sme več uporabljati nobena poizvedba. Vsakič, ko je indeks skeniran, ga označi upravitelj statistike in v pogledu sistemskega kataloga pg_stat_user_indexes lahko vidite vrednost idx_scan, ki je kumulativni števec. Sledenje tej vrednosti v določenem časovnem obdobju (recimo en mesec) bo dalo dobro predstavo o tem, kateri indeksi se ne uporabljajo in bi jih lahko opustili.
Tukaj je poizvedba za pridobitev trenutnega števila pregledov vseh indeksov v 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 indeksov z manj zaklepanji
Indekse je pogosto treba znova sestaviti, na primer ko postanejo napihnjeni, in vnovična izgradnja lahko pospeši skeniranje. Tudi indeksi se lahko poškodujejo. Spreminjanje parametrov indeksa bo morda zahtevalo tudi njegovo ponovno izgradnjo.
Omogoči ustvarjanje vzporednega indeksa
V PostgreSQL 11 je ustvarjanje indeksa B-Tree sočasno. Za pospešitev procesa ustvarjanja je mogoče uporabiti več vzporednih delavcev. Vendar se prepričajte, da so te konfiguracijske možnosti pravilno nastavljene:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Privzete vrednosti so premajhne. V idealnem primeru bi se te številke povečale skupaj s številom procesorskih jeder. Preberite več v .
Ustvarjanje indeksa ozadja
Z možnostjo lahko ustvarite indeks v ozadju CONCURRENTLY ukazi CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTa postopek ustvarjanja indeksa se od običajnega razlikuje po tem, da ne zahteva zaklepanja tabele in zato ne blokira zapisovalnih operacij. Po drugi strani pa vzame več časa in porabi več sredstev.
Postgres ponuja veliko prilagodljivosti za ustvarjanje indeksov in načine za reševanje kakršnih koli posebnih primerov, pa tudi načine za upravljanje baze podatkov v primeru, da vaša aplikacija eksplozivno raste. Upamo, da vam bodo ti nasveti pomagali, da bodo vaše poizvedbe hitre in vaša zbirka podatkov pripravljena na povečanje.
Vir: www.habr.com