En la Postgres-mondo, indeksoj estas kritikaj por efike navigi la datumbazan stokadon (nomitan amason). Postgres ne subtenas clustering por ĝi, kaj la MVCC-arkitekturo igas vin fini kun multaj versioj de la sama opo. Tial, estas tre grave povi krei kaj konservi efikajn indeksojn por subteni aplikojn.
Mi atentigas vin kelkajn konsiletojn por optimumigi kaj plibonigi la uzadon de indeksoj.
Noto: La demandoj montritaj sube funkcias nemodifitaj .
Uzante Kovrantajn Indeksojn
Ni rigardu peton por retrovi retadresojn por neaktivaj uzantoj. En la tablo customer estas kolono active, kaj la demando estas simpla:
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)
La demando vokas la kompletan tabelan skansekvencon customer. Ni kreu indekson sur la kolumno 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)
Helpita, posta skanado fariĝis "index scan". Ĉi tio signifas, ke Postgres rampos la indekson "idx_cust1", kaj poste daŭrigu serĉi la tabelan amason por legi la valorojn de aliaj kolumnoj (en ĉi tiu kazo, la kolumno email) ke la peto bezonas.
PostgreSQL 11 enkondukis kovrajn indeksojn. Ili permesas vin inkluzivi unu aŭ pli aldonajn kolumnojn en la indekso mem - iliaj valoroj estas konservitaj en la indeksa datuma vendejo.
Se ni uzis ĉi tiun funkcion kaj aldonis la retpoŝtan valoron ene de la indekso, tiam Postgres ne bezonus serĉi la valoron en la tabelamaso. email. Ni vidu ĉu ĉi tio funkcias:
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" diras al ni, ke la demando nun bezonas nur la indekson, kio helpas eviti la tutan diskon I/O por legi la amason de la tabelo.
Hodiaŭ, kovraj indeksoj disponeblas nur por B-arboj. Tamen, en ĉi tiu kazo la bontena penado estos pli alta.
Uzante Partajn Indeksojn
Partaj indeksoj indeksas nur subaron de la vicoj de la tabelo. Ĉi tio permesas vin konservi indeksan grandecon kaj fari skanadon pli rapide.
Ni diru, ke ni devas ricevi liston de retadresoj por niaj klientoj en Kalifornio. La peto estos tiel:
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)Kiajn regulajn indeksojn donos al ni:
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)
Skani address estis anstataŭigita per indeksa skanado idx_address1, kaj tiam la amaso estis skanita address.
Ĉar ĉi tio estas ofta demando kaj devas esti optimumigita, ni povas uzi partan indekson, kiu indeksas nur tiujn vicojn kun adresoj en kiuj la areo ‘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)
Nun la peto nur legas idx_address2 kaj ne tuŝas la tablon address.
Uzante Multvalorajn Indeksojn
Iuj kolumnoj, kiuj devas esti indeksitaj, eble ne enhavas skalaran datumtipon. Kolumnaj tipoj kiel jsonb, arrays и tsvector enhavas kunmetitajn aŭ multoblajn signifojn. Se vi bezonas indeksi tiajn kolumnojn, vi kutime devas serĉi ĉiujn individuajn valorojn en tiuj kolumnoj.
Ni provu trovi la titolojn de ĉiuj filmoj enhavantaj klipoj de malsukcesaj prenoj. En la tablo film estas teksta kolumno nomita special_features. Se filmo havas ĉi tiun "specialan posedaĵon", tiam la kolumno enhavas elementon en formo de teksta tabelo Behind The Scenes. Por serĉi ĉiujn tiajn filmojn, ni devas elekti ĉiujn vicojn kun "Malantaŭ La Scenoj" ĉe ajn tabelaj valoroj special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Enteniga operatoro @> kontrolas ĉu la dekstra flanko estas subaro de la maldekstra flanko.
Demandplano:
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)Kiu petas plenan amasan skanadon kun kosto de 67.
Ni vidu ĉu regula B-arba indekso helpos nin:
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)La indekso eĉ ne estis pripensita. La B-arba indekso ne havas ideon pri la ekzisto de individuaj elementoj en la valoroj, kiujn ĝi indeksas.
Ni bezonas GIN-indekson.
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)La GIN-indekso subtenas mapadon de individuaj valoroj al indeksitaj kunmetitaj valoroj, rezultigante pli ol duonon de la kosto de la demanda plano.
Forigi duplikatajn indeksojn
Indeksoj akumuliĝas laŭlonge de la tempo, kaj foje nova indekso povas enhavi la saman difinon kiel unu el la antaŭaj. Vi povas uzi la katalogan vidon por akiri homlegeblajn SQL-indeksajn difinojn pg_indexes. Vi ankaŭ povas facile trovi la samajn difinojn:
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)
Superaroj Indeksoj
Povas okazi, ke vi amasigas multajn indeksojn, unu el kiuj indeksas superaron da kolumnoj, kiujn aliaj indeksoj indeksas. Ĉi tio eble aŭ eble ne estas dezirinda - superaro povas rezultigi nur indeksan skanadon, kio estas bona, sed ĝi povas okupi tro da spaco, aŭ la demando, kiun la superaro celis optimumigi, ne plu estas uzata.
Se vi bezonas aŭtomatigi la difinon de tiaj indeksoj, vi povas komenci per de la tablo pg_catalog.
Neuzataj indeksoj
Dum aplikaĵoj, kiuj uzas datumbazojn, evoluas, ankaŭ la demandoj, kiujn ili uzas. Antaŭe aldonitaj indeksoj ne plu povas esti uzataj de iuj demandoj. Ĉiufoje kiam indekso estas skanita, ĝi estas notita de la statistika administranto kaj en la sistema katalogo-vido pg_stat_user_indexes vi povas vidi la valoron idx_scan, kiu estas akumula nombrilo. Spurado de ĉi tiu valoro dum tempodaŭro (diru monaton) donos bonan ideon pri kiuj indeksoj ne estas uzataj kaj povus esti forigitaj.
Jen peto akiri la nunajn skanajn nombrojn de ĉiuj indeksoj en la skemo ‘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)Rekonstruante indeksojn kun malpli da seruroj
Indeksoj ofte devas esti rekreitaj, ekzemple kiam ili ŝveliĝas, kaj rekreado povas akceli skanadon. Indeksoj ankaŭ povas esti koruptitaj. Ŝanĝi indeksajn parametrojn ankaŭ povas postuli ĝian rekreadon.
Ebligu paralelan indekskreadon
En PostgreSQL 11, B-Tree-indekskreado estas samtempa. Por akceli la kreadprocezon, pluraj paralelaj laboristoj povas esti uzataj. Tamen, certigu, ke ĉi tiuj agordaj agordoj estas ĝuste agorditaj:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;La defaŭltaj valoroj estas tro malgrandaj. Ideale, ĉi tiuj nombroj devus pliiĝi kune kun la nombro da procesoraj kernoj. Legu pli en .
Fona indekso kreado
Vi povas krei la indekson en la fono uzante la opcion CONCURRENTLY ordonas CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXĈi tiu indeksa kreadproceduro diferencas de la kutima pro tio, ke ĝi ne postulas ŝlosi la tabelon, kaj tial ne blokas skribajn operaciojn. Aliflanke, ĝi daŭras pli longe kaj konsumas pli da rimedoj.
Postgres provizas multajn flekseblajn eblojn por krei indeksojn kaj solvojn al iuj specialaj kazoj, kaj ankaŭ provizas manierojn administri la datumbazon se via aplikaĵo kreskas eksplode. Ni esperas, ke ĉi tiuj konsiletoj helpos vin rapidigi viajn demandojn kaj vian datumbazon preta al skalo.
fonto: www.habr.com