Yn 'e Postgres-wrâld binne yndeksen kritysk foar it effisjint navigearjen fan de databankopslach (de heap neamd). Postgres stipet dêr gjin klustering foar, en de MVCC-arsjitektuer makket dat jo einigje mei in protte ferzjes fan deselde tuple. Dêrom is it heul wichtich om effisjinte yndeksen te meitsjen en te ûnderhâlden om applikaasjes te stypjen.
Ik bring jo oandacht wat tips foar it optimalisearjen en ferbetterjen fan it brûken fan yndeksen.
Opmerking: De hjirûnder werjûn queries wurkje op net wizige .
It brûken fan Covering Indexes
Litte wy nei in fersyk sjen om e-mailadressen op te heljen foar ynaktive brûkers. Yn 'e tabel customer der is in kolom active, en de fraach is ienfâldich:
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 ropt de folsleine tabelscansekwinsje op customer. Litte wy in yndeks meitsje op 'e 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)
Geholpen, folgjende scan feroare yn "index scan". Dit betsjut dat Postgres de yndeks sil krûpe "idx_cust1", en gean dan troch mei it sykjen fan 'e tabelheap om de wearden fan oare kolommen te lêzen (yn dit gefal, de kolom email) dat it fersyk nedich is.
PostgreSQL 11 yntrodusearre dekkende yndeksen. Se kinne jo ien of mear ekstra kolommen yn 'e yndeks sels opnimme - har wearden wurde opslein yn' e yndeksgegevenswinkel.
As wy dizze funksje brûkten en de e-postwearde tafoege yn 'e yndeks, dan soe Postgres net hoege te sykjen yn' e tabelheap foar de wearde email. Litte wy sjen oft dit wurket:
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" fertelt ús dat de query no allinich de yndeks nedich is, wat helpt om alle skiif I/O te foarkommen om de heap fan 'e tabel te lêzen.
Tsjintwurdich binne dekkende yndeksen allinnich beskikber foar B-beammen. Yn dit gefal sil de ûnderhâldsynspanning lykwols heger wêze.
Mei help fan Partial Indexes
Diellike yndeksen yndeksearje allinich in subset fan 'e rigen fan' e tabel. Hjirmei kinne jo yndeksgrutte bewarje en scans rapper útfiere.
Litte wy sizze dat wy in list mei e-mailadressen moatte krije foar ús klanten yn Kalifornje. It fersyk sil sa wêze:
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 reguliere yndeksen sille ús jaan:
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)
Scan address is ferfongen troch yndeksscannen idx_address1, en doe waard de heap skansearre address.
Om't dit in faak fraach is en optimalisearre wurde moat, kinne wy in dielyndeks brûke, dy't allinich de rigen yndeksearret mei adressen wêryn it gebiet ‘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)
No lêst it fersyk allinnich idx_address2 en net oanreitsje de tafel address.
Mei help fan multi-wearde yndeksen
Guon kolommen dy't moatte wurde yndeksearre meie net befetsje in skalar gegevens type. Kolomtypen lykas jsonb, arrays и tsvector befetsje gearstalde of meardere betsjuttings. As jo sokke kolommen moatte yndeksearje, moatte jo normaal sykje nei alle yndividuele wearden yn dy kolommen.
Litte wy besykje de titels fan alle films te finen dy't klips befetsje fan mislearre opnames. Yn 'e tabel film der is in tekst kolom neamd special_features. As in film dizze "spesjale eigenskip" hat, dan befettet de kolom in elemint yn 'e foarm fan in tekstarray Behind The Scenes. Om te sykjen foar al sokke films, wy moatte selektearje alle rigen mei "Behind The Scenes" at ien array wearden special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Containment operator @> kontrolearret oft de rjochterkant in subset fan 'e lofterkant is.
Query 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 in folsleine heap scan freget mei in kosten fan 67.
Litte wy sjen oft in reguliere B-beam-yndeks ús sil helpe:
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 yndeks waard net iens beskôge. De B-beam-yndeks hat gjin idee oer it bestean fan yndividuele eleminten yn 'e wearden dy't it yndeksearret.
Wy hawwe in GIN-yndeks nedich.
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-yndeks stipet it yn kaart bringen fan yndividuele wearden nei yndekseare gearstalde wearden, wat resulteart yn mear as de helte fan 'e kosten fan it queryplan.
Ferwiderje fan dûbele yndeksen
Yndeksen sammelje oer de tiid, en soms kin in nije yndeks deselde definysje befetsje as ien fan 'e foarige. Jo kinne de kataloguswerjefte brûke om minsklik lêsbere SQL-yndeksdefinysjes te krijen pg_indexes. Jo kinne ek maklik deselde definysjes fine:
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 Indexes
It kin barre dat jo in protte yndeksen sammelje, wêrfan ien in superset fan kolommen yndeksearret dy't oare yndeksen yndeksearje. Dit kin wol of net winsklik wêze - in superset kin resultearje yn in scan-allinich yndeks, wat goed is, mar it kin tefolle romte nimme, of de query dy't de superset bedoeld wie om te optimalisearjen is net mear yn gebrûk.
As jo de definysje fan sokke yndeksen automatisearje moatte, kinne jo begjinne mei fan 'e tafel pg_catalog.
Net brûkte yndeksen
As applikaasjes dy't databases brûke evoluearje, dogge ek de queries dy't se brûke. Earder tafoege yndeksen meie net mear brûkt wurde troch alle fragen. Elke kear as in yndeks wurdt skend, wurdt it opmurken troch de statistykbehearder en yn 'e werjefte fan' e systeemkatalogus pg_stat_user_indexes kinne jo sjen de wearde idx_scan, dat is in kumulative teller. It folgjen fan dizze wearde oer in perioade fan tiid (sizze in moanne) sil in goed idee jaan fan hokker yndeksen net wurde brûkt en kinne wurde falle.
Hjir is in fersyk om de aktuele scantellingen fan alle yndeksen yn it skema te krijen ‘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)Yndeksen opnij opbouwe mei minder slûzen
Yndeksen moatte faak opnij oanmakke wurde, bygelyks as se opblaasd wurde, en opnij oanmeitsje kin it skennen fersnelle. Yndeksen kinne ek beskeadige wurde. It feroarjen fan yndeksparameters kin it opnij oanmeitsje.
Skeakelje parallelle yndeks oanmeitsjen
Yn PostgreSQL 11 is skepping fan B-Tree-yndeks tagelyk. Om it skeppingsproses te rapperjen, kinne ferskate parallelle arbeiders brûkt wurde. Soargje der lykwols foar dat dizze konfiguraasje-ynstellingen goed ynsteld binne:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;De standertwearden binne te lyts. Ideal moatte dizze nûmers tanimme tegearre mei it oantal prosessorkearnen. Lês mear yn .
Eftergrûn yndeks skepping
Jo kinne de yndeks op 'e eftergrûn oanmeitsje mei de opsje CONCURRENTLY kommando's CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDizze proseduere foar oanmeitsjen fan yndeks ferskilt fan 'e gewoane yn dat it net nedich is om de tafel te beskoatteljen, en dus gjin skriuwoperaasjes blokkearje. Oan 'e oare kant duorret it langer en verbruikt mear boarnen.
Postgres biedt in protte fleksibele opsjes foar it meitsjen fan yndeksen en oplossingen foar spesjale gefallen, en biedt ek manieren om de database te behearjen as jo applikaasje eksplosyf groeit. Wy hoopje dat dizze tips jo sille helpe jo fragen rap te meitsjen en jo database klear om te skaaljen.
Boarne: www.habr.com