Gebruik alle mogelijkheden van indexen in PostgreSQL

In de wereld van Postgres zijn indexen van cruciaal belang voor effectieve navigatie door de database-opslag (de zogenaamde 'heap'). Postgres ondersteunt geen clustering ervoor, en de MVCC-architectuur zorgt ervoor dat u veel versies van dezelfde tuple accumuleert. Daarom is het heel belangrijk om effectieve indexen te kunnen creëren en onderhouden ter ondersteuning van toepassingen.

Hier zijn enkele tips voor het optimaliseren en verbeteren van het gebruik van indexen.

Opmerking: de onderstaande queries werken op een ongewijzigd voorbeeld van de database pagila..

Het gebruik van dekkende indexen (Covering Indexes)

Laten we een query bekijken om e-mailadressen op te halen van inactieve gebruikers. In de tabel customer is er een kolom actief, en de query is vrij 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)

In de query wordt er een volledige sequentiële scan van de tabel aangeroepen. customerLaten we een index aanmaken voor de kolom actief:

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)

Dat hielp, de volgende scan werd een 'index scan'. Dit betekent dat Postgres de index 'idx_cust1' scant en dan verder zoek naar de heap van de tabel om de waarden van andere kolommen te lezen (in dit geval, de kolom e-mail), die de query nodig heeft.

In PostgreSQL 11 zijn dekkende indexen geïntroduceerd. Deze maken het mogelijk om een of meerdere extra kolommen in de index op te nemen - hun waarden worden opgeslagen in de opslag van de index.

Als we deze mogelijkheid gebruikten en de e-mailwaarde aan de index toevoegden, hoeft Postgres de waarde niet in de heap van de tabel te zoeken. e-mailLaten we bekijken of dit werkt:

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‘ geeft ons aan dat de query nu alleen de index nodig heeft, wat helpt om alle schijf I/O-bewerkingen voor het lezen van de heap van de tabel te vermijden.

Vandaag zijn de dekkende indexen alleen beschikbaar voor B-bomen. In dit geval zullen de onderhoudsinspanningen echter hoger zijn.

Gebruik van partiële indexen

Partiële indexen indexeren slechts een subset van de rijen in een tabel. Dit bespaart ruimte in de indexen en versnelt het scannen.

Stel dat we een lijst met e-mailadressen van onze klanten uit Californië nodig hebben. De query zou als volgt zijn:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
wat een queryplan heeft dat beide gekoppelde tabellen scant:
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 bieden gewone indexen ons:

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 werd vervangen door indexscannen idx_address1, en vervolgens werd een heap gescand address.

Aangezien dit een veel voorkomende query is en geoptimaliseerd moet worden, kunnen we een partiële index gebruiken die slechts die rijen met adressen indexeert waarin het district ‘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 tabel niet aan address.

Gebruik van meervoudige indexen (Multi-Value Indexes)

Sommige kolommen die geindexeerd moeten worden, bevatten mogelijk geen scalair datatypes. Kolomtypes zoals jsonb, arrays en tsvector kunnen samengestelde of meervoudige waarden bevatten. Als je dergelijke kolommen wilt indexeren, moet je meestal naar alle afzonderlijke waarden in deze kolommen zoeken.

Laten we de titels van alle films proberen te vinden die fragmenten van mislukte dubaties bevatten. In de tabel film is er een tekstkolom die special_featuresheet. Als een film dit "speciale kenmerk" heeft, bevat de kolom een element in de vorm van een tekstarray Behind The Scenes. Om al deze films te vinden, moeten we alle rijen selecteren met "Behind The Scenes" bij elke waarde van de array special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

De containment-operator @> controleert of de rechterkant een subset is van de linkerkant.

De query-planning:

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 een volledige scan van de heap met een kostenwaarde van 67 opvraagt.

Laten we kijken of een gewone B-tree-index ons kan helpen:

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 zelfs niet overwogen. De B-tree-index vermoedt niet het bestaan van afzonderlijke elementen in de geindexeerde 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 matchen van afzonderlijke waarden met geindexeerde samengestelde waarden, waardoor de kosten van de query-planning met meer dan de helft kunnen worden verlaagd.

We verminderen de duplicatie van indices.

Indexen accumuleren zich in de loop van de tijd en soms kan een nieuwe index dezelfde definitie bevatten als een van de vorige. Voor leesbare SQL-definities van indexen kunt u de catalogusweergave gebruiken. 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;
En hier is het resultaat wanneer het wordt uitgevoerd op de 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 Indexes

Het kan zijn dat u veel indexen heeft, waarvan er één een superset van kolommen indexeert die door andere indexen worden geïndexeerd. Dit kan zowel wenselijk als ongewenst zijn — een superset kan leiden tot het scannen alleen op indexen, wat goed is, maar het kan ook te veel ruimte innemen, of de query waarvoor dit superset was bedoeld, wordt al niet meer gebruikt.

Als u het identificeren van dergelijke indexen wilt automatiseren, kunt u beginnen met pg_index uit de tabel pg_catalog.

Ongebruikte indexen

Naarmate applicaties die databases gebruiken zich ontwikkelen, ontwikkelen ook de query's die ze gebruiken. Eerder toegevoegde indexen kunnen nu door geen enkele query meer worden toegepast. Bij elke indexscan wordt deze gemarkeerd door de statistiekbeheerder, en in de systeemcatalogusweergave pg_stat_user_indexes kan de waarde bekeken worden idx_scan, die een cumulatieve teller is. Het bijhouden van deze waarde gedurende een bepaalde periode (bijvoorbeeld een maand) biedt een goed inzicht in welke indexen niet worden gebruikt en kunnen worden verwijderd.

Hier is een verzoek om de huidige scanstatistieken van alle indexen in het schema op te vragen ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
met output zoals dit:
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 rijen)

Het opnieuw maken van indexen met minder blokkeringen

Vaak moeten indexen opnieuw worden gemaakt, bijvoorbeeld wanneer ze in omvang toenemen, en het opnieuw maken kan het scannen versnellen. Ook kunnen indexen beschadigd raken. Wijziging van indexparameters kan ook vereisen dat de index opnieuw wordt gemaakt.

We schakelen parallel indexcreatie in

In PostgreSQL 11 is de creatie van een B-Tree-index concurrent. Om het proces van aanmaken te versnellen, kunnen verschillende parallelle werkers worden gebruikt. Zorg er echter voor dat deze configuratieparameters correct zijn ingesteld:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

De standaardwaarden zijn te laag. Idealiter moeten deze cijfers toenemen met het aantal CPU-kernen. Lees meer in de documentatie.

Achtergrondindexcreatie

U kunt een index op de achtergrond aanmaken door de parameter CONCURRENTLY van de opdrachten. CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Deze procedure voor het aanmaken van een index verschilt van de gewone doordat deze geen blokkering van de tabel vereist, wat betekent dat deze ook geen schrijfbewerkingen blokkeert. Aan de andere kant duurt het langer en verbruikt het meer middelen.

Postgres biedt veel flexibele mogelijkheden voor het aanmaken van indexen en manieren om te gaan met specifieke situaties, alsook beheermethoden voor de database in het geval van een explosieve groei van uw applicatie. We hopen dat deze tips u helpen om uw query's snel te houden en de database klaar te maken voor schaling.

Bron: habr.com