I Postgres-världen är index avgörande för effektiv navigering av en databas lagring (kallad "hög"). Postgres stöder inte klustring för det, och MVCC-arkitekturen gör att du får många versioner av samma tupel. 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: frågorna nedan fungerar på en oförändrad .
Använda täckande index
Låt oss titta på en begäran om att extrahera e-postadresser för inaktiva användare. Tabell customer det finns en kolumn active, och frågan är enkel:
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)
Frågan anropar hela tabellskanningssekvensen customer. Låt oss skapa ett index på en kolumn 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 skanningen blev till "index scan". Detta betyder att Postgres kommer att skanna indexet "idx_cust1", och fortsätt sedan att söka i tabellhögen för att läsa värdena för andra kolumner (i det här fallet kolumnen email) som frågan behöver.
Täckande index introduceras i PostgreSQL 11. De låter dig inkludera en eller flera ytterligare kolumner i själva indexet - deras värden lagras i indexdatalagret.
Om vi skulle använda den här funktionen och lägga till e-postvärdet i indexet, skulle Postgres inte behöva söka i tabellens hög efter värdet. email. Låt oss se om detta kommer att fungera:
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' berättar att frågan nu bara behöver ett index, vilket hjälper till att undvika all disk I/O för att läsa tabellhögen.
Täckande index är för närvarande endast tillgängliga för B-träd. Men i det här fallet blir underhållsansträngningen högre.
Använda partiella index
Partiella index indexerar endast en delmängd av raderna i en tabell. Detta sparar storleken på indexen och gör skanningarna snabbare.
Låt oss säga att vi vill få en lista över våra kunders e-postadresser i Kalifornien. Förfrå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';
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)Vad vanliga index ger oss:
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 har ersatts av indexskanning idx_address1och skannade sedan högen address.
Eftersom detta är en frekvent fråga och behöver optimeras, kan vi använda ett partiellt index, som endast indexerar 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 frågan bara idx_address2 och rör inte bordet address.
Använda Multi-Value Index
Vissa kolumner som ska indexeras kanske inte innehåller en skalär datatyp. Kolumntyper som jsonb, arrays и tsvector innehåller sammansatta eller flera värden. Om du behöver indexera sådana kolumner måste du vanligtvis söka igenom alla individuella värden i dessa kolumner.
Låt oss försöka hitta titlarna på alla filmer som innehåller klipp från misslyckade inspelningar. Tabell film det finns en textkolumn som heter special_features. Om filmen har denna "särskilda egenskap" innehåller kolumnen elementet som en textmatris Behind The Scenes. För att söka efter alla sådana filmer måste vi välja alla rader med "Behind The Scenes" när någon matrisvärden special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Häckande operatör @> kontrollerar om den högra sidan är en delmängd av den vänstra.
Begär 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)Som begär en full heap scan med en kostnad av 67.
Låt oss se om ett vanligt B-trädindex hjälper 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)Indexet övervägdes inte ens. B-trädets index är inte medvetet om förekomsten av enskilda 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 kartläggning av enskilda värden mot indexerade sammansatta värden, vilket resulterar i en frågeplanskostnad som är mer än halverad.
Att bli av med dubbletter av index
Index ackumuleras med tiden, och ibland kan ett nytt index innehålla samma definition som ett av de tidigare. Du kan använda katalogvyn för att få mänskligt läsbara SQL-definitioner av index. pg_indexes. Du kan också enkelt hitta identiska definitioner:
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-index
Det kan hända att du får många index, varav ett indexerar en superuppsättning av kolumner som indexerar andra index. Detta kan vara önskvärt eller inte – supersetet kan resultera i enbart indexsökningar, vilket är bra, men det kan ta för mycket utrymme, eller så används inte längre frågan som supersetet var tänkt att optimera.
Om du behöver automatisera definitionen av sådana index kan du börja med från bordet pg_catalog.
Oanvända index
I takt med att applikationer som använder databaser utvecklas, så gör frågorna de använder. Index som lagts till tidigare får inte längre användas av någon fråga. Varje gång ett index skannas markeras det av statistikhanteraren och i systemkatalogvyn pg_stat_user_indexes du kan se värdet idx_scan, som är en kumulativ räknare. Att spåra detta värde över en tidsperiod (säg en månad) kommer att ge en god uppfattning om vilka index som inte används och kan tas bort.
Här är en fråga för att få de aktuella skanningsantalerna för alla index i schemat ‘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)Bygger om index med färre lås
Index behöver ofta byggas om, till exempel när de blir uppsvällda, och ombyggnad kan påskynda skanningen. Även index kan skadas. Ändring av indexparametrarna kan också kräva ombyggnad.
Aktivera skapande av parallellt index
I PostgreSQL 11 skapas ett B-Tree-index samtidigt. För att påskynda skapelseprocessen kan flera parallella arbetare användas. Se dock till att dessa konfigurationsalternativ är korrekt inställda:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Standardvärdena är för små. Helst bör dessa siffror öka tillsammans med antalet processorkärnor. Läs mer i .
Skapa bakgrundsindex
Du kan skapa ett index i bakgrunden med alternativet CONCURRENTLY kommandon CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDenna procedur för att skapa index skiljer sig från den vanliga genom att den inte kräver ett lås på bordet och därför inte blockerar skrivoperationer. Å andra sidan tar det mer tid och drar mer resurser.
Postgres ger en hel del flexibilitet för att skapa index och sätt att lösa eventuella specialfall, samt sätt att hantera databasen ifall din applikation växer exploderande. Vi hoppas att dessa tips hjälper dig att få dina frågor snabbt och din databas redo att skalas.
Källa: will.com