I le lalolagi Postgres, o faʻamatalaga e taua mo le faʻaogaina lelei o le teuina o faʻamaumauga (taʻua o le "faʻaputuga"). E le lagolagoina e Postgres le faʻapipiʻiina, ma o le fausaga MVCC e mafua ai ona e faʻamaeʻaina le tele o faʻamatalaga o le tuple tutusa. O le mea lea, e taua tele le mafai ona fatuina ma tausia faʻamatalaga lelei e lagolago ai talosaga.
O nisi nei o fautuaga mo le faʻaleleia ma le faʻaleleia o le faʻaogaina o faʻasino igoa.
Manatua: o fesili o loʻo faʻaalia i lalo o loʻo galue i se mea e leʻi suia .
Fa'aaogā Fa'asinoga Ufiufi
Se'i o tatou va'ai i se talosaga e aveese mai ai tuatusi imeli mo tagata e le toaga mai. Laupapa customer o lo'o i ai se koluma active, ma e faigofie le fesili:
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)
O le su'esu'ega e fa'aogaina ai le fa'asologa atoa o su'esu'ega laulau customer. Tatou fai se faasino igoa i luga o se koluma 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)
Na fesoasoani, o le suʻesuʻega mulimuli ane na liua i le "index scan". O lona uiga o le a suʻesuʻe e Postgres le faasino igoa "idx_cust1", ona faʻaauau lea ona suʻe le faʻaputu laulau e faitau ai tau o isi koluma (i lenei tulaga, o le koluma email) e manaʻomia e le fesili.
O lo'o fa'ailoa mai fa'asino igoa i le PostgreSQL 11. Latou te faʻatagaina oe e faʻapipiʻi se tasi pe sili atu koluma faaopoopo i totonu o le faʻasinomaga lava ia - o latou tau o loʻo teuina i totonu o le faleoloa faʻamaumauga.
Afai matou te faʻaogaina lenei faʻaoga ma faʻaopoopo le tau imeli i totonu o le faasino igoa, ona le manaʻomia lea e Postgres ona suʻeina le faʻaputuga laulau mo le tau. email. Sei o tatou vaai pe aoga lenei:
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' taʻu mai ia i matou o le fesili o loʻo manaʻomia nei naʻo se faasino igoa, lea e fesoasoani e aloese ai mai le I/O tisiki uma e faitau ai le faaputuga laulau.
O lo'o avanoa nei fa'asino igoa mo na'o B-la'au. Ae ui i lea, i lenei tulaga, o le a sili atu le malosi o le tausiga.
Fa'aaogaina o Fa'asinoga Fa'asinoala
E na'o se vaega o laina o lo'o i totonu o se laulau e fa'asino ai vaega fa'asino. Ole mea lea e fa'asaoina ai le tele o fa'asinomaga ma fa'avavevave ai su'ega.
Fa'apea matou te fia maua se lisi o tuatusi imeli a matou tagata fa'atau i Kalefonia. O le talosaga e pei o lenei:
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)O a faasino igoa masani o le a tuuina mai ia i tatou:
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 ua suia i le index scan idx_address1ona siaki lea o le faaputuga address.
Talu ai o se fesili masani lea ma e manaʻomia ona faʻaleleia atili, e mafai ona matou faʻaogaina se vaega faʻasino, lea e faʻasino ai na o laina ma tuatusi o loʻo i ai le itumalo. ‘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)
O lea ua na'o le faitau o le fesili idx_address2 ma e le tago i le laulau address.
Fa'aaogaina o Fa'asinoga Fa'asinomaga Tele
O nisi koluma e fa'asino i igoa atonu e leai se ituaiga fa'amatalaga scalar. Ituaiga koluma pei jsonb, arrays и tsvector o lo'o iai fa'atasi po'o le tele o tau. Afai e te manaʻomia le faʻavasegaina o ia koluma, e masani lava ona e suʻesuʻeina uma tulaga taua i na koluma.
Se'i o tatou taumafai e su'e ulutala o ata uma o lo'o i ai fa'ato'aga mai fa'ailoga le manuia. Laupapa film o lo'o i ai se koluma tusitusiga e ta'ua special_features. Afai o le ata tifaga o loʻo i ai lenei "meatotino faʻapitoa", o loʻo i ai i le koluma le elemene o se faʻasologa o tusitusiga Behind The Scenes. Ina ia su'e ata uma faapena, e tatau ona tatou filifilia laina uma ma le "Behind The Scenes" pe a soʻo se tulaga taua special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Fa'anofoga @> siaki pe o le itu taumatau o se vaega o le itu agavale.
Talosaga fuafuaga:
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)O lo'o mana'omia se su'esu'ega atoa ma le tau e 67.
Se'i tatou va'ai pe fesoasoani se fa'asinomaga masani B-laau ia i tatou:
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)E lei iloiloina foi le faasino igoa. O le B-tree index e le o iloa le i ai o elemene taʻitasi i totonu o tau faʻasino.
Matou te manaʻomia se 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)E lagolagoina e le GIN index le fa'afanua o tau e tasi e fa'asaga i fa'amaumauga tu'ufa'atasia fa'asinomaga, e maua ai se tau o fuafuaga e sili atu nai lo le afa.
Ave'esea fa'ailoga fa'alua
E fa'aputuina fa'asinomaga i le aluga o taimi, ma o nisi taimi e mafai ona i ai i se fa'asinomaga fou le fa'auigaga tutusa ma se tasi o fa'amatalaga muamua. E mafai ona e fa'aogaina le va'aiga fa'amaumauga e maua ai fa'amatalaga SQL e mafai ona faitau e tagata o fa'asino igoa. pg_indexes. E faigofie fo'i ona e maua fa'auigaga tutusa:
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
E mafai ona tupu e fa'ai'u i le tele o fa'asino igoa, o se tasi o lo'o fa'asinoina se superset o koluma e fa'asino i isi fa'asino igoa. Atonu e mana'omia pe leai fo'i—o le superset e mafai ona maua ai na'o su'esu'ega fa'asinomaga, e lelei, ae atonu e tele naua le avanoa, po'o le fesili na fa'amoemoe le superset e fa'amalieina ua le toe fa'aaogaina.
Afai e te manaʻomia le faʻautometi le faʻamatalaga o ia faʻasino igoa, e mafai ona e amata i mai le laulau pg_catalog.
Fa'ailoga e le'i fa'aaogaina
A'o fa'atupula'ia talosaga e fa'aogaina fa'amaumauga, fa'apea fo'i fesili latou te fa'aogaina. Fa'asinomaga fa'aopoopo muamua e ono le toe fa'aogaina e so'o se fesili. O taimi uma e su'e ai se fa'ailoga, e fa'ailogaina e le pule o fuainumera, ma i le va'aiga fa'asologa o fa'amaumauga. pg_stat_user_indexes e mafai ona e vaʻaia le taua idx_scan, o se fa'aputuga fa'aopoopo. O le siakiina o lenei tau i luga o se vaitaimi (fai mai le masina) o le a maua ai se manatu lelei po'o fea fa'asino igoa e le o fa'aaogaina ma e mafai ona pa'u.
Ole su'ega lea e maua ai fa'amatalaga su'esu'e o lo'o i ai nei o fa'ailoga uma ile sikola ‘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)Toe fausia fa'asino igoa e itiiti loka
E masani ona manaʻomia le toe faʻaleleia o faʻamatalaga, mo se faʻataʻitaʻiga pe a faʻafefeteina, ma toe faʻaleleia e mafai ona faʻavaveina le suʻega. E mafai fo'i ona fa'aleagaina fa'asino igoa. O le suia o fa'amaufa'ailoga e ono mana'omia ai fo'i le toe fau.
Fa'aaga le fa'atusa fa'atusa
I le PostgreSQL 11, o le fatuina o se B-Tree index e tutusa. Ina ia faatelevaveina le faagasologa o le foafoaga, e mafai ona faʻaogaina le tele o tagata faigaluega tutusa. Ae ui i lea, ia mautinoa o loʻo faʻatulaga saʻo nei filifiliga:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;O tau fa'aletonu e la'ititi tele. O le mea lelei, o nei fuainumera e tatau ona faʻateleina faʻatasi ma le numera o mea faʻapipiʻi. Faitau atili ile .
Fa'asologa o fa'asino igoa
E mafai ona e fatuina se faasino igoa i tua e faʻaaoga ai le filifiliga CONCURRENTLY poloaʻiga CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXO lenei faiga fa'asinomaga e ese mai le masani ona e le mana'omia se loka i luga o le laulau, ma o lea e le poloka ai galuega tusitusi. I le isi itu, e manaʻomia le tele o le taimi ma faʻaalu ai le tele o punaoa.
E tuʻuina atu e Postgres le tele o fetuutuunaiga mo le fatuina o faʻasinomaga ma auala e foia ai soʻo se mataupu faʻapitoa, faʻapea foʻi ma auala e pulea ai faʻamaumauga i le tulaga o lau talosaga e tupu faʻafuaseʻi. Matou te fa'amoemoe o le a fesoasoani nei fautuaga ia te oe e vave maua au fesili ma sauni lau fa'amaumauga e fua.
puna: www.habr.com