Sa kalibutan sa Postgres, ang mga indeks hinungdanon aron epektibo nga mag-navigate sa pagtipig sa database (gitawag nga pundok). Wala gisuportahan sa mga postgres ang pag-cluster para niini, ug ang arkitektura sa MVCC hinungdan kanimo nga adunay daghang mga bersyon sa parehas nga tuple. Busa, importante kaayo nga makahimo ug makamentinar sa episyente nga mga indeks aron suportahan ang mga aplikasyon.
Gidala ko sa imong pagtagad ang pipila ka mga tip sa pag-optimize ug pagpauswag sa paggamit sa mga indeks.
Mubo nga sulat: Ang mga pangutana nga gipakita sa ubos nagtrabaho sa wala giusab .
Paggamit sa Covering Index
Atong tan-awon ang usa ka hangyo aron makuha ang mga email address alang sa mga dili aktibo nga tiggamit. Sa lamesa customer naay kolum active, ug ang pangutana yano ra:
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)
Gitawag sa pangutana ang kompleto nga han-ay sa pag-scan sa lamesa customer. Magbuhat ta ug index sa 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)
Nakatabang, ang sunod nga pag-scan nahimo nga "index scan". Kini nagpasabot nga ang mga Postgres mag-crawl sa index "idx_cust1", ug dayon ipadayon ang pagpangita sa tapok sa lamesa aron mabasa ang mga kantidad sa ubang mga kolum (sa kini nga kaso, ang kolum email) nga gikinahanglan sa hangyo.
Gipaila sa PostgreSQL 11 ang mga indeks nga naglangkob. Gitugotan ka nila nga ilakip ang usa o daghang dugang nga mga kolum sa indeks mismo - ang ilang mga kantidad gitipigan sa tindahan sa data sa indeks.
Kung gigamit namon kini nga bahin ug gidugang ang kantidad sa email sa sulod sa indeks, nan dili kinahanglan nga tan-awon sa mga Postgres ang pundok sa lamesa alang sa kantidad email. Atong tan-awon kon kini molihok:
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" nagsulti kanato nga ang pangutana karon nagkinahanglan lamang sa index, nga makatabang sa paglikay sa tanan nga disk I/O sa pagbasa sa tapok sa lamesa.
Karon, ang mga indeks sa pagtabon magamit ra alang sa mga B-tree. Bisan pa, sa kini nga kaso ang paningkamot sa pagpadayon mas taas.
Paggamit sa Partial Index
Ang mga partial nga indeks nag-indeks lamang sa usa ka subset sa mga laray sa lamesa. Kini nagtugot kanimo sa pagtipig sa gidak-on sa indeks ug paghimo sa mga pag-scan nga mas paspas.
Ingnon ta kinahanglan nga makakuha kami usa ka lista sa mga adres sa email alang sa among mga kostumer sa California. Ang hangyo mahimong sama niini:
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)Unsa nga regular nga mga indeks ang ihatag kanato:
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 gipulihan sa index scanning idx_address1, ug dayon ang tapok gi-scan address.
Tungod kay kini usa ka kanunay nga pangutana ug kinahanglan nga ma-optimize, mahimo kaming mogamit usa ka partial nga indeks, nga nag-indeks lamang sa mga laray nga adunay mga adres diin ang lugar β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)
Karon ang hangyo mabasa ra idx_address2 ug dili mohikap sa lamesa address.
Paggamit sa Multi-Value Index
Ang ubang mga kolum nga kinahanglang ma-indeks mahimong walay scalar data type. Mga tipo sa kolum sama sa jsonb, arrays ΠΈ tsvector adunay compound o daghang kahulugan. Kung kinahanglan nimo nga i-index ang ingon nga mga kolum, kasagaran kinahanglan nimo pangitaon ang tanan nga mga indibidwal nga kantidad sa mga kolum.
Atong sulayan pagpangita ang mga titulo sa tanan nga mga pelikula nga adunay mga clip gikan sa dili malampuson nga pagkuha. Sa lamesa film naay text column nga gitawag special_features. Kung ang usa ka salida adunay kini nga "espesyal nga kabtangan", nan ang kolum adunay elemento sa porma sa usa ka array sa teksto Behind The Scenes. Aron pangitaon ang tanan nga ingon nga mga pelikula, kinahanglan namon nga pilion ang tanan nga mga laray nga adunay "Likod sa mga Eksena" sa Π»ΡΠ±ΡΡ
mga kantidad sa array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator sa pagpugong @> nagsusi kung ang tuo nga kilid usa ka subset sa wala nga bahin.
Plano sa pangutana:
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)Nga nangayo ug bug-os nga heap scan nga adunay kantidad nga 67.
Atong tan-awon kon ang usa ka regular nga B-tree index makatabang kanato:
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)Ang indeks wala gani gikonsiderar. Ang indeks sa B-tree walay ideya bahin sa paglungtad sa mga indibidwal nga elemento sa mga kantidad nga gi-index niini.
Kinahanglan namon ang indeks sa GIN.
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)Gisuportahan sa indeks sa GIN ang pagmapa sa mga indibidwal nga kantidad sa gi-index nga mga kantidad sa komposisyon, nga nagresulta sa kapin sa katunga sa gasto sa plano sa pangutana.
Pagtangtang sa mga duplicate nga indeks
Ang mga indeks natipon sa paglabay sa panahon, ug usahay ang usa ka bag-ong indeks mahimong adunay parehas nga kahulugan sa usa sa mga nauna. Mahimo nimong gamiton ang pagtan-aw sa katalogo aron makuha ang mga kahulugan sa indeks sa SQL nga mabasa sa tawo pg_indexes. Dali ra usab nimo makit-an ang parehas nga 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)
Superset nga mga Index
Mahimong mahitabo nga makaipon ka ug daghang mga indeks, usa niini nag-indeks sa usa ka superset sa mga kolum nga gi-indeks sa ubang mga indeks. Mahimo kini o dili mahimo nga gusto - ang usa ka superset mahimong moresulta sa usa ka indeks-lamang nga pag-scan, nga maayo, apan mahimoβg magkinahanglan kini daghang lugar, o ang pangutana nga gituyo sa superset nga ma-optimize wala na gigamit.
Kung kinahanglan nimo nga i-automate ang kahulugan sa ingon nga mga indeks, mahimo ka magsugod gikan sa lamesa pg_catalog.
Wala gigamit nga mga indeks
Samtang ang mga aplikasyon nga naggamit sa mga database nag-uswag, mao usab ang mga pangutana nga ilang gigamit. Ang mga gidugang kaniadto nga mga indeks mahimong dili na magamit sa bisan unsang mga pangutana. Matag higayon nga ang usa ka indeks ma-scan, kini mamatikdan sa statistics manager ug sa system catalog view pg_stat_user_indexes makita nimo ang bili idx_scan, nga usa ka cumulative counter. Ang pagsubay sa kini nga kantidad sa usa ka yugto sa panahon (ingon usa ka bulan) maghatag usa ka maayong ideya kung unsang mga indeks ang wala gigamit ug mahimong ihulog.
Ania ang usa ka hangyo aron makuha ang kasamtangan nga ihap sa pag-scan sa tanan nga mga indeks 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)Pagtukod pag-usab sa mga indeks nga adunay gamay nga mga kandado
Ang mga indeks sa kasagaran kinahanglan nga buhaton pag-usab, pananglitan kung kini moburot, ug ang paghimo usab makapadali sa pag-scan. Ang mga indeks mahimo usab nga madaot. Ang pagbag-o sa mga parameter sa index mahimo usab nga magkinahanglan sa paghimo pag-usab niini.
I-enable ang paghimo sa parallel index
Sa PostgreSQL 11, ang paghimo sa indeks sa B-Tree dungan. Aron mapadali ang proseso sa paghimo, daghang mga parallel nga mga trabahante ang magamit. Bisan pa, siguroha nga kini nga mga setting sa pag-configure husto nga gitakda:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Ang mga default nga kantidad gamay ra kaayo. Sa tinuud, kini nga mga numero kinahanglan nga modaghan kauban ang gidaghanon sa mga cores sa processor. Basaha ang dugang sa .
Paghimo sa indeks sa background
Mahimo nimong buhaton ang indeks sa background gamit ang kapilian CONCURRENTLY mga mando CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXKini nga pamaagi sa paghimo sa index lahi sa naandan nga wala kini kinahanglan nga pag-lock sa lamesa, ug busa dili babagan ang mga operasyon sa pagsulat. Sa laing bahin, kini nagkinahanglan og mas taas nga panahon ug naggamit sa dugang nga mga kapanguhaan.
Naghatag ang mga Postgres og daghang flexible nga mga kapilian alang sa paghimo og mga indeks ug mga solusyon sa bisan unsang espesyal nga mga kaso, ug naghatag usab mga paagi aron madumala ang database kung ang imong aplikasyon motubo nga kusog. Kami nanghinaut nga kini nga mga tip makatabang kanimo sa paghimo sa imong mga pangutana nga paspas ug ang imong database andam sa pagsukod.
Source: www.habr.com