I PostgreSQL Àr index extremt viktiga för effektiv navigering genom databasens lagringsutrymme (det kallas «heap»). PostgreSQL stöder inte klusterisering, och MVCC-arkitekturen leder till att mÄnga versioner av samma tuple samlas pÄ hög. DÀrför Àr det mycket viktigt att kunna skapa och underhÄlla effektiva index för att stödja applikationer.
HÀr Àr nÄgra tips för att optimera och förbÀttra anvÀndningen av index.
Obs: NedanstÄende frÄgor fungerar pÄ en oförÀndrad .
AnvÀndning av tÀckande index (Covering Indexes)
LÄt oss titta pÄ en frÄga för att extrahera e-postadresser för inaktiva anvÀndare. I tabellen customer finns en kolumn active, och frÄgan blir inte komplicerad:
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) I frÄgan anropas en fullstÀndig sekvens av tabellscanning. customerLÄt oss skapa ett index för kolumnen 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) Det hjÀlpte, den efterföljande scanningen blev en «index scan«. Det betyder att PostgreSQL kommer att skanna indexet «idx_cust1«, och sedan fortsÀtta att söka i tabellens heap för att lÀsa vÀrdena av andra kolumner (i detta fall kolumnen e-post), som begÀrs av frÄgan.
I PostgreSQL 11 introducerades tĂ€ckande index. De gör det möjligt att inkludera en eller flera extra kolumner i indexet â deras vĂ€rden lagras i indexets datalagring.
Om vi hade utnyttjat denna möjlighet och lagt till e-postadressen i indexet, skulle PostgreSQL inte behöva söka efter vÀrdet i tabellens heap. e-postLÄt oss se om det fungerar:
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» sÀger oss att frÄgan nu bara behöver indexet, vilket hjÀlper till att undvika alla diskoperationer för in- och utdata för att lÀsa tabellens heap.
I dag Àr tÀckande index endast tillgÀngliga för B-trÀd. Men i detta fall kommer underhÄllet att vara mer krÀvande.
AnvÀndning av partiella index
Partiella index indexerar bara en delmÀngd av tabellens rader. Det sparar indexstorlek och gör skanning snabbare.
Anta att vi behöver fÄ en lista över e-postadresser frÄn vÄra kunder i Kalifornien. FrÄgan kommer att se ut sÄ hÀr:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
vilket har en frÄgeplan som involverar att skanna bÄda de ihopslagna tabellerna:
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)Vad ger oss vanliga index:
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) Skanning address ersattes av indexskanning idx_address1, och sedan skannades högstapeln address.
Eftersom detta Ă€r en vanlig förfrĂ„gan och behöver optimeras kan vi anvĂ€nda ett partiellt index som indexerar endast de rader med adresser dĂ€r distriktet â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 lÀser förfrÄgan endast idx_address2 och rör inte tabellen address.
AnvÀndning av flervÀrdiga index (Multi-Value Indexes)
Vissa kolumner som behöver indexeras kan sakna skalÀra datatyper. Kolumntyper som jsonb, arrays och tsvector kan innehÄlla sammansatta eller multipla vÀrden. Om du behöver indexera sÄdana kolumner, mÄste du vanligtvis söka igenom varje individuellt vÀrde i dessa kolumner.
LÄt oss försöka hitta titlarna pÄ alla filmer som innehÄller klipp frÄn misslyckade dubbar. I tabellen film finns det en texkolumn som kallas special_features. Om en film har denna "specialfunktion" sÄ innehÄller kolumnen ett element som en textarray Behind The Scenes. För att hitta alla sÄdana filmer behöver vi vÀlja alla rader som har "Behind The Scenes" vid vilka vÀrden som helst i arrayen special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Containment operator @> kontrollerar om den högra sidan Àr en delmÀngd av den vÀnstra sidan.
FrÄgeplan:
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)Vilken begÀr en fullstÀndig skanning av högen med en kostnad av 67.
LÄt oss se om en vanlig B-trÀd index kan hjÀlpa oss:
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)Indexen beaktades inte ens. B-trÀd indexet har ingen aning om existensen av individuella element i de indexerade vÀrdena.
Vi behöver ett GIN-index.
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-indexet stöder matchning av individuella vÀrden med de indexerade sammansatta vÀrdena, vilket resulterar i att kostnaden för frÄgeplanen minskar med mer Àn hÀlften.
Vi eliminerar dubbletter av index
Indexar samlas över tid, och ibland kan ett nytt index innehÄlla samma definition som ett tidigare. För att fÄ lÀsbara SQL-definitioner för index kan man anvÀnda katalogvisningen. pg_indexes. Du kommer ocksÄ lÀtt kunna hitta identiska definitioner:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
Och hÀr Àr resultatet nÀr det körs pÄ den stock pagila-databasen:
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)
Ăvergripande index (Superset Indexes)
Det kan hĂ€nda att du har samlat mĂ„nga index, dĂ€r ett av dem indexerar en övergripande mĂ€ngd kolumner som ocksĂ„ indexeras av andra index. Detta kan vara bĂ„de önskvĂ€rt och oönskat â övergripande index kan leda till att man skannar endast via index, vilket Ă€r bra, men kan ocksĂ„ ta för mycket utrymme, eller sĂ„ anvĂ€nds inte den frĂ„ga som detta index var avsett att optimera lĂ€ngre.
Om du behöver automatisera identifieringen av sÄdana index kan du börja med frÄn tabellen pg_catalog.
OanvÀnda index
I takt med att applikationer som anvÀnder databaser utvecklas, utvecklas Àven frÄgorna de anvÀnder. Tidigare tillagda index kanske inte lÀngre anvÀnds av nÄgon frÄga. Vid varje skanning av indexet markeras det av statistikern, och i systemkatalogens vy pg_stat_user_indexes kan man se vÀrdet idx_scan, som Àr en ackumulerande rÀknare. Att spÄra detta vÀrde över en viss tidsperiod (lÄt oss sÀga en mÄnad) ger en bra insikt om vilka index som inte anvÀnds och kan tas bort.
HĂ€r Ă€r en frĂ„ga för att fĂ„ de aktuella skannerrĂ€knarna för alla index i schemat âpublicâ:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
med utskrift som denna:
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 rader)à terskapande av index med fÀrre lÄs
Det hĂ€nder ofta att index mĂ„ste Ă„terskapas, till exempel nĂ€r de vĂ€xer i storlek, och Ă„terskapande kan snabba upp skanningar. Index kan ocksĂ„ skadas. Ăndring av indexparametrar kan ocksĂ„ krĂ€va Ă„terskapande.
Aktivera parallell skapande av index
I PostgreSQL 11 Àr skapande av B-Tree-index konkurrensutsatt. Flera arbetare kan anvÀndas parallellt för att snabba upp skapandeprocessen. Se dock till att dessa konfigurationsparametrar Àr instÀllda korrekt:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;StandardvÀrdena Àr för smÄ. Idealiskt sett bör dessa siffror ökas i takt med antalet CPU-kÀrnor. LÀs mer i .
Bakgrundsskapande av index
Du kan skapa ett index i bakgrunden genom att anvÀnda parametern CONCURRENTLY kommandon CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenna procedur för att skapa ett index skiljer sig frÄn den vanliga eftersom den inte krÀver lÄsning av tabellen, vilket innebÀr att den inte blockerar skrivoperationer. à andra sidan tar den lÀngre tid och förbrukar fler resurser.
Postgres erbjuder mÄnga flexibla möjligheter för att skapa index och sÀtt att hantera alla specifika fall, samt sÀtt att hantera databasen vid explosiv tillvÀxt av din applikation. Vi hoppas att dessa tips hjÀlper dig att göra dina frÄgor snabba och din databas beredd att skala.
KĂ€lla: habr.com
