Postgres-maailmassa indeksit ovat välttämättömiä tehokkaalle tietokantavarastossa (kutsutaan "kasaksi") liikkumiselle. Postgres ei tue sen klusterointia, ja MVCC-arkkitehtuuri saa aikaan useita versioita samasta tuplesta. Siksi on erittäin tärkeää pystyä luomaan ja ylläpitämään tehokkaita indeksejä sovellusten tukemiseksi.
Tässä on muutamia vinkkejä indeksien käytön optimointiin ja parantamiseen.
Huomautus: alla näkyvät kyselyt toimivat muokkaamattomalla .
Peittoindeksien käyttäminen
Tarkastellaan pyyntöä poimia sähköpostiosoitteet passiivisilta käyttäjiltä. Pöytä customer siellä on sarake active, ja kysely on yksinkertainen:
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)
Kysely kutsuu koko taulukon tarkistussekvenssin customer. Luodaan sarakkeeseen indeksi 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)
Se auttoi, myöhemmästä skannauksesta tuli "index scan". Tämä tarkoittaa, että Postgres skannaa indeksin "idx_cust1", ja jatka sitten taulukkokeon etsimistä lukeaksesi muiden sarakkeiden (tässä tapauksessa sarakkeen) arvot email), joita kysely tarvitsee.
Peittävät indeksit otetaan käyttöön PostgreSQL 11:ssä. Niiden avulla voit sisällyttää yhden tai useamman lisäsarakkeen itse hakemistoon - niiden arvot tallennetaan indeksitietosäilöön.
Jos hyödynsimme tätä ominaisuutta ja lisäsimme sähköpostiarvon hakemistoon, Postgresin ei tarvitsisi etsiä arvoa taulukkokekasta. email. Katsotaan toimiiko tämä:
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' kertoo meille, että kysely tarvitsee nyt vain indeksin, mikä auttaa välttämään kaiken levyn I/O:n taulukkokeon lukemiseen.
Kattoindeksit ovat tällä hetkellä saatavilla vain B-puille. Tässä tapauksessa huoltotarve on kuitenkin suurempi.
Osittaisten indeksien käyttö
Osittaiset indeksit indeksoivat vain taulukon rivien osajoukon. Tämä säästää hakemistojen kokoa ja nopeuttaa skannausta.
Oletetaan, että haluamme saada luettelon Kaliforniassa olevien asiakkaidemme sähköpostiosoitteista. Pyyntö tulee olemaan seuraava:
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)Mitä tavalliset indeksit antavat meille:
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)
skannata address on korvattu indeksiskannauksella idx_address1ja sitten skannannut kasan address.
Koska tämä on usein toistuva kysely ja se on optimoitava, voimme käyttää osittaista indeksiä, joka indeksoi vain ne rivit, joiden osoitteet ‘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)
Nyt kysely vain lukee idx_address2 eikä koske pöytään address.
Moniarvoisten indeksien käyttö
Jotkut indeksoitavat sarakkeet eivät välttämättä sisällä skalaaritietotyyppiä. Saraketyypit kuten jsonb, arrays и tsvector sisältää yhdistelmäarvoja tai useita arvoja. Jos sinun on indeksoitava tällaisia sarakkeita, sinun on yleensä etsittävä kaikki näiden sarakkeiden yksittäiset arvot.
Yritetään löytää kaikkien elokuvien nimet, jotka sisältävät leikkauksia epäonnistuneista otoista. Pöytä film siellä on tekstisarake nimeltä special_features. Jos elokuvassa on tämä "erikoisominaisuus", sarake sisältää elementin tekstitaulukona Behind The Scenes. Kaikkien tällaisten elokuvien etsimiseksi meidän on valittava kaikki rivit, joissa on "Behind The Scenes" milloin kaikki taulukon arvot special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Pesäoperaattori @> tarkistaa, onko oikea puoli vasemman puolen osajoukko.
Pyydä suunnitelma:
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)Joka vaatii täyden kasan skannauksen, jonka hinta on 67.
Katsotaan, auttaako tavallinen B-puuindeksi meitä:
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)Indeksiä ei edes otettu huomioon. B-puuindeksi ei ole tietoinen yksittäisten elementtien olemassaolosta indeksoiduissa arvoissa.
Tarvitsemme GIN-indeksin.
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-indeksi tukee yksittäisten arvojen yhdistämistä indeksoituihin yhdistelmäarvoihin, jolloin kyselysuunnitelman kustannukset ovat yli puolet.
Päästä eroon päällekkäisistä hakemistoista
Indeksit kertyvät ajan myötä, ja joskus uusi indeksi voi sisältää saman määritelmän kuin jokin aikaisemmista. Voit käyttää luettelonäkymää saadaksesi ihmisen luettavia SQL-määritelmiä hakemistoista. pg_indexes. Voit myös helposti löytää identtisiä määritelmiä:
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)
Superset indeksit
Saattaa käydä niin, että päädyt useisiin indekseihin, joista yksi indeksoi sarakkeiden superjoukon, joka indeksoi muita indeksejä. Tämä voi olla toivottavaa tai ei – superjoukko voi johtaa vain indeksitarkistuksiin, mikä on hyvä asia, mutta se voi viedä liikaa tilaa tai kyselyä, jota superjoukko oli tarkoitettu optimoimaan, ei enää käytetä.
Jos sinun on automatisoitava tällaisten indeksien määrittely, voit aloittaa pöydältä pg_catalog.
Käyttämättömät indeksit
Tietokantoja käyttävien sovellusten kehittyessä myös niiden käyttämät kyselyt kehittyvät. Aiemmin lisättyjä indeksejä ei voi enää käyttää missään kyselyssä. Aina kun indeksi tarkistetaan, tilastojen hallinta merkitsee sen ja järjestelmäluettelonäkymässä pg_stat_user_indexes voit nähdä arvon idx_scan, joka on kumulatiivinen laskuri. Tämän arvon seuraaminen tietyn ajanjakson (esim. kuukauden) aikana antaa hyvän käsityksen siitä, mitkä indeksit eivät ole käytössä ja mitkä voidaan jättää pois.
Tässä on kysely kaikkien skeeman indeksien nykyisten tarkistusten saamiseksi ‘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)Indeksien uudelleenrakentaminen vähemmällä lukolla
Indeksit on usein rakennettava uudelleen esimerkiksi silloin, kun ne turpoavat, ja uudelleenrakentaminen voi nopeuttaa skannausta. Myös indeksit voivat vioittua. Indeksiparametrien muuttaminen voi myös vaatia sen uudelleen rakentamista.
Ota rinnakkaishakemiston luominen käyttöön
PostgreSQL 11:ssä B-Tree-indeksin luominen tapahtuu samanaikaisesti. Luomisprosessin nopeuttamiseksi voidaan käyttää useita rinnakkaisia työntekijöitä. Varmista kuitenkin, että nämä kokoonpanoasetukset on asetettu oikein:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Oletusarvot ovat liian pieniä. Ihannetapauksessa näiden lukujen pitäisi kasvaa prosessoriytimien määrän mukana. Lue lisää kohdasta .
Taustahakemiston luominen
Voit luoda hakemiston taustalla käyttämällä vaihtoehtoa CONCURRENTLY komennot CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTämä indeksin luontimenettely eroaa tavallisesta siinä, että se ei vaadi taulukon lukitusta, eikä siksi estä kirjoitustoimintoja. Toisaalta se vie enemmän aikaa ja kuluttaa enemmän resursseja.
Postgres tarjoaa paljon joustavuutta indeksien luomiseen ja tapoihin ratkaista erityistapauksia sekä tapoja hallita tietokantaa, jos sovelluksesi kasvaa räjähdysmäisesti. Toivomme, että nämä vinkit auttavat sinua saamaan kyselysi nopeasti ja tietokantasi valmiiksi skaalautumaan.
Lähde: will.com