In de Postgres-wereld zijn indexen essentieel voor efficiënte navigatie door de opslag van een database (een "heap" genoemd). Postgres ondersteunt hiervoor geen clustering, en de MVCC-architectuur zorgt ervoor dat je veel versies van dezelfde tuple krijgt. Daarom is het erg belangrijk om efficiënte indexen te kunnen maken en onderhouden om applicaties te ondersteunen.
Hier zijn enkele tips voor het optimaliseren en verbeteren van het gebruik van indexen.
Opmerking: de onderstaande zoekopdrachten werken op een ongewijzigde .
Overdekkende indexen gebruiken
Laten we eens kijken naar een verzoek om e-mailadressen op te halen voor inactieve gebruikers. Tafel customer er is een kolom active, en de vraag 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)
De query roept de volledige tabelscanreeks aan customer. Laten we een index op een kolom maken 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)
Het hielp, de daaropvolgende scan veranderde in "index scan". Dit betekent dat Postgres de index zal scannen "idx_cust1", en ga dan verder met het doorzoeken van de tabelheap om de waarden van andere kolommen te lezen (in dit geval de kolom email) die de query nodig heeft.
Dekkende indexen worden geïntroduceerd in PostgreSQL 11. Hiermee kunt u een of meer extra kolommen in de index zelf opnemen - hun waarden worden opgeslagen in de indexgegevensopslag.
Als we van deze functie gebruik zouden maken en de e-mailwaarde aan de index zouden toevoegen, zou Postgres niet in de tabelheap naar de waarde hoeven te zoeken. email. Eens kijken of dit gaat werken:
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' vertelt ons dat de query nu alleen een index nodig heeft, wat helpt voorkomen dat alle schijf-I/O de tabelheap leest.
Dekkende indexen zijn momenteel alleen beschikbaar voor B-bomen. In dit geval zal de onderhoudsinspanning echter hoger zijn.
Gedeeltelijke indexen gebruiken
Gedeeltelijke indexen indexeren slechts een subset van de rijen in een tabel. Dit bespaart de grootte van indexen en maakt scans sneller.
Stel dat we een lijst willen hebben met de e-mailadressen van onze klanten in Californië. Het verzoek zal als volgt zijn:
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 indexen ons zullen geven:
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)
scannen address is vervangen door indexscan idx_address1en vervolgens de hoop gescand address.
Aangezien dit een veel voorkomende zoekopdracht is en geoptimaliseerd moet worden, kunnen we een gedeeltelijke index gebruiken, die alleen die rijen indexeert met adressen waarin de wijk ‘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)
Nu leest de query alleen idx_address2 en raakt de tafel niet aan address.
Indexen met meerdere waarden gebruiken
Sommige kolommen die moeten worden geïndexeerd, bevatten mogelijk geen scalair gegevenstype. Kolomtypes zoals jsonb, arrays и tsvector bevatten samengestelde of meerdere waarden. Als u dergelijke kolommen moet indexeren, moet u meestal alle individuele waarden in die kolommen doorzoeken.
Laten we proberen de titels te vinden van alle films met fragmenten van mislukte opnames. Tafel film er is een tekstkolom genaamd special_features. Als de film deze "speciale eigenschap" heeft, bevat de kolom het element als een tekstarray Behind The Scenes. Om al dergelijke films te zoeken, moeten we alle rijen selecteren met "Behind The Scenes" wanneer elk matrix waarden special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nestende operator @> controleert of de rechterkant een subset is van de linkerkant.
Plan aanvragen:
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 vraagt om een volledige heap-scan met een kostprijs van 67.
Laten we eens kijken of een gewone B-tree-index ons helpt:
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)De index werd niet eens overwogen. De B-tree-index is niet op de hoogte van het bestaan van individuele elementen in de geïndexeerde waarden.
We hebben een GIN-index nodig.
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)De GIN-index ondersteunt het in kaart brengen van enkele waarden tegen geïndexeerde samengestelde waarden, wat resulteert in meer dan gehalveerde queryplankosten.
Dubbele indexen verwijderen
Indexen stapelen zich in de loop van de tijd op en soms kan een nieuwe index dezelfde definitie bevatten als een van de vorige. U kunt de catalogusweergave gebruiken om voor mensen leesbare SQL-definities van indexen te verkrijgen. pg_indexes. U kunt ook gemakkelijk identieke definities vinden:
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-indexen
Het kan gebeuren dat u veel indexen krijgt, waarvan er één een superset van kolommen indexeert die andere indexen indexeren. Dit kan al dan niet wenselijk zijn: de superset kan resulteren in alleen-indexscans, wat goed is, maar het kan te veel ruimte in beslag nemen, of de query die de superset moest optimaliseren, wordt niet meer gebruikt.
Als u de definitie van dergelijke indexen wilt automatiseren, kunt u beginnen met van de tafel pg_catalog.
Ongebruikte indexen
Naarmate toepassingen die gebruikmaken van databases evolueren, evolueren ook de query's die ze gebruiken. Indexen die eerder zijn toegevoegd, kunnen door geen enkele query meer worden gebruikt. Elke keer dat een index wordt gescand, wordt deze gemarkeerd door de statistiekenmanager en in de systeemcatalogusweergave pg_stat_user_indexes je kunt de waarde zien idx_scan, wat een cumulatieve teller is. Door deze waarde gedurende een bepaalde periode (bijvoorbeeld een maand) te volgen, krijgt u een goed idee van welke indexen niet worden gebruikt en kunnen worden verwijderd.
Hier is een query om de huidige scantellingen van alle indexen in het schema te krijgen ‘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)Indexen opnieuw opbouwen met minder sloten
Indexen moeten vaak opnieuw worden opgebouwd, bijvoorbeeld wanneer ze opgeblazen raken, en opnieuw opbouwen kan de scan versnellen. Ook indexen kunnen beschadigd raken. Als u de indexparameters wijzigt, moet u deze mogelijk ook opnieuw opbouwen.
Schakel het maken van parallelle indexen in
In PostgreSQL 11 is het maken van een B-Tree-index gelijktijdig. Om het creatieproces te versnellen, kunnen meerdere parallelwerkers worden gebruikt. Zorg er echter voor dat deze configuratie-opties correct zijn ingesteld:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;De standaardwaarden zijn te klein. Idealiter zouden deze aantallen moeten toenemen samen met het aantal processorkernen. Lees meer binnen .
Achtergrondindex maken
U kunt een index op de achtergrond maken met behulp van de optie CONCURRENTLY commando's CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDeze procedure voor het maken van een index verschilt van de gebruikelijke omdat er geen vergrendeling op de tabel nodig is en daarom schrijfbewerkingen niet worden geblokkeerd. Aan de andere kant kost het meer tijd en verbruikt het meer middelen.
Postgres biedt veel flexibiliteit voor het maken van indexen en manieren om speciale gevallen op te lossen, evenals manieren om de database te beheren voor het geval uw applicatie explosief groeit. We hopen dat deze tips je zullen helpen om je queries snel te krijgen en je database schaalbaar te maken.
Bron: www.habr.com