An der Postgres Welt sinn Indizes wesentlech fir effizient Navigatioun vun enger Datebanklagerung (genannt "Koup"). Postgres ënnerstëtzt net Clustering dofir, an d'MVCC Architektur bewierkt datt Dir mat ville Versioune vum selwechten Tupel ophalen. Dofir ass et ganz wichteg fir effizient Indexen ze kreéieren an z'erhalen fir Uwendungen z'ënnerstëtzen.
Hei sinn e puer Tipps fir d'Benotzung vun Indexen ze optimiséieren an ze verbesseren.
Notiz: d'Ufroen hei ënnen funktionnéieren op engem onverännert .
Benotzt Ofdeckungsindexen
Loosst eis eng Ufro kucken fir E-Mailadressen fir inaktiv Benotzer ze extrahieren. Dësch customer et gëtt eng Kolonn active, an d'Ufro ass einfach:
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)
D'Ufro rifft déi ganz Tabellescansequenz op customer. Loosst eis en Index op enger Kolonn erstellen 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)
Et huet gehollef, de spéideren Scan gouf an "index scan". Dëst bedeit datt Postgres den Index scannt "idx_cust1", a fuert dann weider op den Dëschkoup fir d'Wäerter vun anere Kolonnen ze liesen (an dësem Fall d'Kolonn email) déi d'Ufro brauch.
Ofdeckungsindexe ginn am PostgreSQL 11 agefouert. Si erlaben Iech eng oder méi zousätzlech Kolonnen am Index selwer ze enthalen - hir Wäerter sinn am Indexdatengeschäft gespäichert.
Wa mir vun dëser Fonktioun profitéiert hunn an den E-Mail-Wäert am Index bäigefüügt hunn, da brauch Postgres net den Tabellekoup fir de Wäert ze sichen. email. Loosst eis kucken ob dëst funktionnéiert:
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' erzielt eis datt d'Ufro elo nëmmen en Index brauch, wat hëlleft all Disk I/O ze vermeiden fir den Tabelleheap ze liesen.
Ofdeckungsindexe sinn am Moment nëmme fir B-Beem verfügbar. Wéi och ëmmer, an dësem Fall wäert den Ënnerhalt Effort méi héich sinn.
Mat partiell Indexen
Partiell Indexen indexéieren nëmmen e Subset vun de Reihen an enger Tabell. Dëst spuert d'Gréisst vun den Indexen a mécht Scans méi séier.
Loosst eis soen, mir wëllen eng Lëscht vun eise Clienten hir E-Mail Adressen a Kalifornien kréien. D'Ufro wäert esou sinn:
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 normal Indexen eis ginn:
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 gouf duerch Index Scan ersat idx_address1an dann de Koup gescannt address.
Well dëst eng heefeg Ufro ass a muss optimiséiert ginn, kënne mir e partiellen Index benotzen, deen nëmmen déi Reihen mat Adressen indexéiert an deenen de Bezierk ‘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)
Elo liest d'Ufro nëmmen idx_address2 an beréiert den Dësch net address.
Benotzt Multi-Value Indexen
E puer Kolonnen, déi indexéiert ginn, kënnen net e scalar Datentyp enthalen. Kolonn Zorte wéi jsonb, arrays и tsvector enthalen Komposit oder Multiple Wäerter. Wann Dir esou Kolonnen indexéiere musst, musst Dir normalerweis all eenzel Wäerter an deene Kolonnen sichen.
Loosst eis probéieren d'Titele vun alle Filmer ze fannen déi Ausschnëtter aus net erfollegräichen Take enthalen. Dësch film et gëtt eng Textkolonne genannt special_features. Wann de Film dës "speziell Eegeschafte" huet, da enthält d'Kolonn d'Element als Textarray Behind The Scenes. Fir all esou Filmer ze sichen, musse mir all Zeile mat "Behind The Scenes" wielen wann iergendeen Array Wäerter special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nesting Bedreiwer @> kontrolléiert ob déi riets Säit en Ënnerdeel vun der lénker Säit ass.
Ufro Plang:
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 e komplette Heap Scan mat Käschte vu 67 freet.
Loosst eis kucken ob e reguläre B-Bam Index eis hëlleft:
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)Den Index gouf mol net berücksichtegt. De B-Bam Index ass net bewosst iwwer d'Existenz vun eenzelnen Elementer an den indexéierte Wäerter.
Mir brauchen e 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)De GIN Index ënnerstëtzt d'Kaart vun eenzel Wäerter géint indexéiert Kompositwäerter, wat zu enger Ufroplangkäschte resultéiert déi méi wéi halbéiert ass.
Kritt vun duplizéierten Indexen
Indexe sammelen sech mat der Zäit, an heiansdo kann en neien Index déiselwecht Definitioun enthalen wéi ee vun de fréiere. Dir kënnt d'Katalogusiicht benotze fir mënschlech liesbar SQL Definitioune vun Indexen ze kréien. pg_indexes. Dir kënnt och einfach identesch Definitiounen fannen:
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
Et ka geschéien datt Dir mat villen Indizes endet, vun deenen een e Superset vu Sailen indexéiert, déi aner Indizes indexéieren. Dëst kann oder vläicht net wënschenswäert sinn - de Superset kann zu Indexscannen resultéieren, wat gutt ass, awer et kann ze vill Plaz ophuelen, oder d'Ufro, déi de Superset geduecht war ze optimiséieren, gëtt net méi benotzt.
Wann Dir d'Definitioun vun esou Indizes automatiséieren musst, kënnt Dir ufänken mat vum Dësch pg_catalog.
Onbenotzt Indizes
Wéi Uwendungen déi Datenbanken benotzen evoluéieren, sou maachen d'Ufroen déi se benotzen. Indexen, déi virdru bäigefüügt goufen, kënnen net méi vun enger Ufro benotzt ginn. All Kéier wann en Index gescannt gëtt, gëtt et vum Statistikmanager markéiert, an am Systemkatalog View pg_stat_user_indexes Dir kënnt de Wäert gesinn idx_scan, wat e kumulative Konter ass. D'Verfollegung vun dësem Wäert iwwer eng Zäitperiod (soen e Mount) gëtt eng gutt Iddi iwwer wéi eng Indizes net benotzt ginn a kéinte falen.
Hei ass eng Ufro fir déi aktuell Scanzuele vun all Indexen am Schema ze kréien ‘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)Rekonstruktioun vun Indexen mat manner Spären
Indexe mussen dacks nei opgebaut ginn, zum Beispill wann se opgeblosen ginn, an d'Opbau kann de Scan beschleunegen. Och Indexe kënne korrupt ginn. D'Ännerung vun den Indexparameter kann och d'Opbau erfuerderen.
Aktivéiert parallel Index Kreatioun
Am PostgreSQL 11, e B-Tree Index erstellen ass gläichzäiteg. Fir de Kreatiounsprozess ze beschleunegen, kënne verschidde parallel Aarbechter benotzt ginn. Gitt awer sécher datt dës Konfiguratiounsoptioune richteg agestallt sinn:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;D'Standardwäerter sinn ze kleng. Idealerweis sollten dës Zuelen zesumme mat der Unzuel vun de Prozessorkären eropgoen. Liest méi an .
Hannergrond Index Kreatioun
Dir kënnt en Index am Hannergrond mat der Optioun erstellen CONCURRENTLY Kommandoen CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXDëst Index Kreatioun Prozedur ënnerscheet sech aus dem üblechen an datt et net e Spär op den Dësch erfuerdert, an dofir net Schreifoperatioune blockéiert. Op der anerer Säit hëlt et méi Zäit a verbraucht méi Ressourcen.
Postgres bitt vill Flexibilitéit fir Indexen a Weeër ze kreéieren fir all speziell Fäll ze léisen, souwéi Weeër fir d'Datebank ze managen am Fall wou Är Applikatioun explodéierend wiisst. Mir hoffen, datt dës Tipps Iech hëllefen Är Ufroe séier ze kréien an Är Datebank prett ze skaléieren.
Source: will.com