Sa mundo ng Postgres, ang mga index ay mahalaga para sa mahusay na pag-navigate ng imbakan ng database (tinatawag na "bunton"). Hindi sinusuportahan ng mga postgres ang clustering para dito, at ang arkitektura ng MVCC ay nagdudulot sa iyo na magkaroon ng maraming bersyon ng parehong tuple. Samakatuwid, napakahalaga na makalikha at mapanatili ang mahusay na mga index upang suportahan ang mga aplikasyon.
Narito ang ilang mga tip para sa pag-optimize at pagpapabuti ng paggamit ng mga index.
Tandaan: ang mga query na ipinapakita sa ibaba ay gumagana sa isang hindi nabago .
Paggamit ng mga Covering Index
Tingnan natin ang isang kahilingan na kumuha ng mga email address para sa mga hindi aktibong user. mesa customer may column active, at ang query ay simple:
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)
Invokes ng query ang buong table scan sequence customer. Gumawa tayo ng index sa isang column 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)
Nakatulong ito, ang kasunod na pag-scan ay naging "index scan". Nangangahulugan ito na i-scan ng Postgres ang index "idx_cust1", at pagkatapos ay ipagpatuloy ang paghahanap sa table heap upang basahin ang mga halaga ng iba pang mga column (sa kasong ito, ang column email) na kailangan ng query.
Ang mga sumasaklaw na index ay ipinakilala sa PostgreSQL 11. Pinapayagan ka nilang magsama ng isa o higit pang mga karagdagang column sa index mismo - ang kanilang mga halaga ay naka-imbak sa index data store.
Kung sinamantala namin ang feature na ito at idinagdag ang halaga ng email sa loob ng index, hindi na kakailanganin ng Postgres na maghanap sa table heap para sa value. email. Tingnan natin kung gagana ito:
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' ay nagsasabi sa amin na ang query ngayon ay nangangailangan lamang ng isang index, na tumutulong na maiwasan ang lahat ng disk I/O upang mabasa ang table heap.
Ang mga sumasaklaw na index ay kasalukuyang magagamit lamang para sa mga B-tree. Gayunpaman, sa kasong ito, ang pagsisikap sa pagpapanatili ay magiging mas mataas.
Paggamit ng mga Partial Index
Ang mga bahagyang index ay nag-index lamang ng isang subset ng mga hilera sa isang talahanayan. Ito ay nakakatipid sa laki ng mga index at ginagawang mas mabilis ang mga pag-scan.
Sabihin nating gusto naming makakuha ng listahan ng mga email address ng aming mga customer sa California. Ang kahilingan ay magiging ganito:
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)Anong mga ordinaryong index ang ibibigay sa atin:
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)
I-scan address ay napalitan ng index scan idx_address1at pagkatapos ay ini-scan ang heap address.
Dahil ito ay isang madalas na query at kailangang i-optimize, maaari kaming gumamit ng isang bahagyang index, na nag-i-index lamang ng mga row na may mga address kung saan ang distrito. β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)
Ngayon ang query ay nagbabasa lamang idx_address2 at hindi humawak sa mesa address.
Paggamit ng Multi-Value Index
Maaaring walang scalar data type ang ilang column na ii-index. Mga uri ng column tulad ng jsonb, arrays ΠΈ tsvector naglalaman ng pinagsama-sama o maramihang mga halaga. Kung kailangan mong i-index ang mga naturang column, karaniwang kailangan mong maghanap sa lahat ng mga indibidwal na halaga sa mga column na iyon.
Subukan nating hanapin ang mga pamagat ng lahat ng pelikulang naglalaman ng mga hiwa mula sa mga hindi matagumpay na pagkuha. mesa film may isang text column na tinatawag special_features. Kung ang pelikula ay may ganitong "espesyal na pag-aari", ang column ay naglalaman ng elemento bilang isang text array Behind The Scenes. Para hanapin ang lahat ng naturang pelikula, kailangan nating piliin ang lahat ng row na may "Behind The Scenes" kung kailan anuman mga halaga ng array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Nesting operator @> sinusuri kung ang kanang bahagi ay isang subset ng kaliwang bahagi.
Humiling ng plano:
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)Na humihiling ng buong heap scan na may halagang 67.
Tingnan natin kung nakakatulong sa atin ang isang regular na B-tree index:
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)Hindi man lang isinasaalang-alang ang index. Ang index ng B-tree ay hindi alam ang pagkakaroon ng mga indibidwal na elemento sa mga na-index na halaga.
Kailangan namin ng 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)Sinusuportahan ng GIN index ang pagmamapa ng mga solong halaga sa mga na-index na composite na halaga, na nagreresulta sa isang gastos sa query plan na higit sa kalahati.
Pag-alis ng mga duplicate na index
Nag-iipon ang mga index sa paglipas ng panahon, at kung minsan ang isang bagong index ay maaaring maglaman ng parehong kahulugan tulad ng isa sa mga nauna. Maaari mong gamitin ang view ng catalog upang makakuha ng mga kahulugan ng SQL na nababasa ng tao ng mga index. pg_indexes. Madali mo ring mahahanap ang magkatulad na mga kahulugan:
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)
Mga Superset Index
Maaaring mangyari na magkakaroon ka ng maraming index, kung saan ang isa ay nag-i-index ng superset ng mga column na nag-index ng iba pang mga index. Ito ay maaaring maging kanais-nais o hindiβang superset ay maaaring magresulta sa mga index-only na pag-scan, na mabuti, ngunit maaaring tumagal ito ng masyadong maraming espasyo, o ang query na nilayon ng superset na i-optimize ay hindi na ginagamit.
Kung kailangan mong i-automate ang kahulugan ng mga naturang index, maaari kang magsimula sa mula sa mesa pg_catalog.
Mga hindi nagamit na index
Habang umuunlad ang mga application na gumagamit ng mga database, gayundin ang mga query na ginagamit nila. Ang mga index na idinagdag nang mas maaga ay hindi na maaaring gamitin ng anumang query. Sa bawat oras na ini-scan ang isang index, minarkahan ito ng tagapamahala ng istatistika, at sa view ng system catalog pg_stat_user_indexes makikita mo ang halaga idx_scan, na isang pinagsama-samang counter. Ang pagsubaybay sa halagang ito sa loob ng isang yugto ng panahon (sabihin ang isang buwan) ay magbibigay ng magandang ideya kung aling mga index ang hindi ginagamit at maaaring i-drop.
Narito ang isang query upang makuha ang kasalukuyang mga bilang ng pag-scan ng lahat ng mga index sa schema β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)Muling pagbuo ng mga index na may mas kaunting mga lock
Ang mga index ay madalas na kailangang itayo muli, halimbawa kapag sila ay namamaga, at ang muling pagtatayo ay maaaring mapabilis ang pag-scan. Gayundin ang mga index ay maaaring masira. Ang pagbabago sa mga parameter ng index ay maaari ding mangailangan ng muling pagtatayo nito.
I-enable ang paggawa ng parallel index
Sa PostgreSQL 11, ang paglikha ng isang B-Tree index ay kasabay. Upang mapabilis ang proseso ng paglikha, maraming mga parallel na manggagawa ang maaaring gamitin. Gayunpaman, tiyaking nakatakda nang tama ang mga opsyon sa pagsasaayos na ito:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Ang mga default na halaga ay masyadong maliit. Sa isip, ang mga numerong ito ay dapat tumaas kasama ng bilang ng mga core ng processor. Magbasa nang higit pa sa .
Paglikha ng background index
Maaari kang lumikha ng isang index sa background gamit ang opsyon CONCURRENTLY mga koponan CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXAng pamamaraan ng paglikha ng index na ito ay naiiba sa karaniwan dahil hindi ito nangangailangan ng lock sa mesa, at samakatuwid ay hindi hinaharangan ang mga operasyon sa pagsulat. Sa kabilang banda, nangangailangan ito ng mas maraming oras at kumukonsumo ng mas maraming mapagkukunan.
Ang mga Postgres ay nagbibigay ng maraming flexibility para sa paglikha ng mga index at mga paraan upang malutas ang anumang mga espesyal na kaso, pati na rin ang mga paraan upang pamahalaan ang database kung sakaling lumaki ang iyong aplikasyon. Umaasa kami na ang mga tip na ito ay makakatulong sa iyo na makuha ang iyong mga query nang mabilis at ang iyong database ay handa nang sukatin.
Pinagmulan: www.habr.com