In die Postgres-wêreld is indekse noodsaaklik vir doeltreffende navigasie van 'n databasis se berging (genoem 'n "hoop"). Postgres ondersteun nie groepering daarvoor nie, en die MVCC-argitektuur veroorsaak dat jy met baie weergawes van dieselfde tupel eindig. Daarom is dit baie belangrik om doeltreffende indekse te kan skep en in stand te hou om toepassings te ondersteun.
Hier is 'n paar wenke vir die optimalisering en verbetering van die gebruik van indekse.
Let wel: die navrae wat hieronder gewys word, werk op 'n ongewysigde .
Gebruik dekkende indekse
Kom ons kyk na 'n versoek om e-posadresse vir onaktiewe gebruikers te onttrek. Tafel customer daar is 'n kolom active, en die navraag is eenvoudig:
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)
Die navraag roep die volledige tabelskanderingsvolgorde aan customer. Kom ons skep 'n indeks op 'n kolom 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)
Dit het gehelp, die daaropvolgende skandering het verander in "index scan". Dit beteken dat Postgres die indeks sal skandeer "idx_cust1", en gaan dan voort om die tabelhoop te soek om die waardes van ander kolomme te lees (in hierdie geval, die kolom email) wat die navraag benodig.
Dekindekse word in PostgreSQL 11 bekendgestel. Hulle laat jou toe om een of meer bykomende kolomme in die indeks self in te sluit - hul waardes word in die indeksdatawinkel gestoor.
As ons hierdie funksie sou gebruik en die e-poswaarde in die indeks sou byvoeg, sou Postgres nie nodig het om die tabel se hoop vir die waarde te soek nie. email. Kom ons kyk of dit sal werk:
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' vertel ons dat die navraag nou net 'n indeks benodig, wat help om alle skyf-I/O te vermy om die tabelhoop te lees.
Dekindekse is tans slegs beskikbaar vir B-bome. In hierdie geval sal die instandhoudingspoging egter hoër wees.
Gebruik gedeeltelike indekse
Gedeeltelike indekse indekseer slegs 'n subset van die rye in 'n tabel. Dit bespaar die grootte van indekse en maak skanderings vinniger.
Kom ons sê ons wil 'n lys van ons kliënte se e-posadresse in Kalifornië kry. Die versoek sal soos volg wees:
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)Wat gewone indekse vir ons sal gee:
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)
skandering address is vervang deur indeksskandering idx_address1en dan die hoop geskandeer address.
Aangesien dit 'n gereelde navraag is en geoptimaliseer moet word, kan ons 'n gedeeltelike indeks gebruik, wat slegs daardie rye indekseer met adresse waarin die distrik ‘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)
Nou lees die navraag net idx_address2 en raak nie aan die tafel nie address.
Gebruik multi-waarde-indekse
Sommige kolomme wat geïndekseer moet word, bevat dalk nie 'n skalêre datatipe nie. Kolom tipes soos jsonb, arrays и tsvector saamgestelde of veelvuldige waardes bevat. As jy sulke kolomme moet indekseer, moet jy gewoonlik deur al die individuele waardes in daardie kolomme soek.
Kom ons probeer om die titels van alle rolprente te vind wat snitte van onsuksesvolle opnames bevat. Tafel film daar is 'n tekskolom genaamd special_features. As die fliek hierdie "spesiale eienskap" het, dan bevat die kolom die element as 'n teksskikking Behind The Scenes. Om na al sulke films te soek, moet ons alle rye met "Behind The Scenes" wanneer kies enige skikking waardes special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nestoperateur @> kontroleer of die regterkant 'n subset van die linkerkant is.
Versoek plan:
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)Wat 'n volledige hoop skandering met 'n koste van 67 versoek.
Kom ons kyk of 'n gereelde B-boom-indeks ons help:
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)Die indeks is nie eers oorweeg nie. Die B-boom-indeks is nie bewus van die bestaan van individuele elemente in die geïndekseerde waardes nie.
Ons benodig 'n 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)Die GIN-indeks ondersteun die kartering van enkele waardes teen geïndekseerde saamgestelde waardes, wat lei tot 'n navraagplankoste wat meer as gehalveer word.
Om ontslae te raak van duplikaat-indekse
Indekse akkumuleer met verloop van tyd, en soms kan 'n nuwe indeks dieselfde definisie as een van die voriges bevat. Jy kan die katalogusaansig gebruik om mens-leesbare SQL-definisies van indekse te kry. pg_indexes. Jy kan ook maklik identiese definisies vind:
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-indekse
Dit kan gebeur dat jy met baie indekse eindig, waarvan een 'n superstel van kolomme indekseer wat ander indekse indekseer. Dit mag of mag nie wenslik wees nie - die superstel kan lei tot slegs indeksskanderings, wat goed is, maar dit kan te veel spasie in beslag neem, of die navraag wat die superset bedoel was om te optimaliseer, word nie meer gebruik nie.
As jy die definisie van sulke indekse moet outomatiseer, kan jy begin met van die tafel af pg_catalog.
Ongebruikte indekse
Soos toepassings wat databasisse gebruik ontwikkel, so ontwikkel die navrae wat hulle gebruik. Indekse wat vroeër bygevoeg is, mag nie meer deur enige navraag gebruik word nie. Elke keer as 'n indeks geskandeer word, word dit deur die statistiekbestuurder en in die stelselkatalogusaansig gemerk pg_stat_user_indexes jy kan die waarde sien idx_scan, wat 'n kumulatiewe teller is. Om hierdie waarde oor 'n tydperk (sê 'n maand) na te spoor, sal 'n goeie idee gee van watter indekse nie gebruik word nie en kan laat vaar word.
Hier is 'n navraag om die huidige skanderingtellings van alle indekse in die skema te kry ‘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)Herbou indekse met minder slotte
Indekse moet dikwels herbou word, byvoorbeeld wanneer hulle opgeblaas word, en herbou kan die skandering bespoedig. Ook indekse kan beskadig word. Om die indeksparameters te verander, kan ook nodig wees om dit te herbou.
Aktiveer parallelle indeksskepping
In PostgreSQL 11 is die skep van 'n B-Tree-indeks gelyktydig. Om die skeppingsproses te bespoedig, kan verskeie parallelle werkers gebruik word. Maak egter seker dat hierdie konfigurasie-opsies korrek gestel is:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Die verstekwaardes is te klein. Ideaal gesproke moet hierdie getalle toeneem saam met die aantal verwerkerkerne. Lees meer in .
Agtergrond indeks skepping
U kan 'n indeks op die agtergrond skep deur die opsie te gebruik CONCURRENTLY opdragte CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXHierdie indeksskeppingsprosedure verskil van die gewone een deurdat dit nie 'n slot op die tafel vereis nie, en dus nie skryfbewerkings blokkeer nie. Aan die ander kant neem dit meer tyd en verbruik meer hulpbronne.
Postgres bied baie buigsaamheid vir die skep van indekse en maniere om enige spesiale gevalle op te los, sowel as maniere om die databasis te bestuur ingeval jou toepassing ontplofbaar groei. Ons hoop dat hierdie wenke jou sal help om jou navrae vinnig te kry en jou databasis gereed om te skaal.
Bron: will.com