Ninu aye Postgres, awọn atọka jẹ pataki fun lilọ kiri daradara ti ibi ipamọ data (ti a npe ni "okiti"). Postgres ko ṣe atilẹyin iṣupọ fun rẹ, ati faaji MVCC jẹ ki o pari pẹlu ọpọlọpọ awọn ẹya ti tuple kanna. Nitorina, o ṣe pataki pupọ lati ni anfani lati ṣẹda ati ṣetọju awọn itọka daradara lati ṣe atilẹyin awọn ohun elo.
Eyi ni diẹ ninu awọn imọran fun iṣapeye ati ilọsiwaju lilo awọn atọka.
Akiyesi: awọn ibeere ti o han ni isalẹ ṣiṣẹ lori aiyipada .
Lilo Awọn atọka Ibora
Jẹ ki a wo ibeere kan lati jade awọn adirẹsi imeeli fun awọn olumulo ti ko ṣiṣẹ. Tabili customer ọwọn kan wa active, ati pe ibeere naa rọrun:
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)
Ibeere naa n pe ni kikun tabili ọlọjẹ lẹsẹsẹ customer. Jẹ ki a ṣẹda atọka lori iwe kan 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)
O ṣe iranlọwọ, ọlọjẹ atẹle naa yipada si "index scan". Eyi tumọ si pe Postgres yoo ṣe ayẹwo atọka naa "idx_cust1", ati lẹhinna tẹsiwaju wiwa okiti tabili lati ka awọn iye ti awọn ọwọn miiran (ninu ọran yii, ọwọn naa email) pe ibeere naa nilo.
Awọn atọka ibora ti wa ni afihan ni PostgreSQL 11. Wọn gba ọ laaye lati ṣafikun ọkan tabi diẹ sii awọn ọwọn afikun ninu atọka funrararẹ - awọn iye wọn ti wa ni ipamọ ninu ile itaja data atọka.
Ti a ba lo anfani ti ẹya yii ati ṣafikun iye imeeli inu atọka, lẹhinna Postgres kii yoo nilo lati wa okiti tabili fun iye naa. email. Jẹ ki a wo boya eyi yoo ṣiṣẹ:
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' sọ fun wa pe ibeere ni bayi nilo atọka nikan, eyiti o ṣe iranlọwọ yago fun gbogbo I/O disk lati ka okiti tabili.
Awọn atọka ibora wa lọwọlọwọ fun awọn igi B nikan. Sibẹsibẹ, ninu ọran yii, igbiyanju itọju yoo jẹ ti o ga julọ.
Lilo Awọn atọka Apa kan
Awọn atọka apakan nikan ni ipin ti awọn ori ila ninu tabili kan. Eyi fi iwọn awọn atọka pamọ ati mu ki awọn ọlọjẹ yiyara.
Jẹ ki a sọ pe a fẹ lati gba atokọ ti awọn adirẹsi imeeli awọn alabara wa ni California. Ibeere naa yoo jẹ bi eleyi:
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)Kini awọn atọka lasan yoo fun wa:
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)
Ṣayẹwo address ti rọpo nipasẹ ọlọjẹ atọka idx_address1ati lẹhinna ṣayẹwo okiti naa address.
Niwọn bi eyi jẹ ibeere loorekoore ati pe o nilo iṣapeye, a le lo itọka apa kan, eyiti o ṣe atọka awọn ori ila nikan pẹlu awọn adirẹsi ninu eyiti agbegbe naa. ‘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)
Bayi ibeere nikan ka idx_address2 ko si fi ọwọ kan tabili address.
Lilo Olona-iye atọka
Diẹ ninu awọn ọwọn lati ṣe atọka le ma ni iru data wiwọn ninu. Awọn iru ọwọn bi jsonb, arrays и tsvector ni akojọpọ tabi ọpọ iye ninu. Ti o ba nilo lati ṣe atọka iru awọn ọwọn, o nigbagbogbo ni lati wa nipasẹ gbogbo awọn iye ẹni kọọkan ninu awọn ọwọn yẹn.
Jẹ ki a gbiyanju lati wa awọn akọle ti gbogbo awọn fiimu ti o ni awọn gige lati awọn aṣeyọri ti ko ni aṣeyọri. Tabili film iwe ọrọ wa ti a npe ni special_features. Ti fiimu naa ba ni “ohun-ini pataki” yii, lẹhinna iwe naa ni ipin naa gẹgẹbi akopọ ọrọ Behind The Scenes. Lati wa gbogbo iru awọn fiimu, a nilo lati yan gbogbo awọn ori ila pẹlu "Behind The Scenes" nigbati eyikeyi orun iye special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Oniṣẹ itẹle @> sọwedowo ti o ba ti ọtun ẹgbẹ ni a ayosile ti apa osi.
Eto ibere:
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)Ewo ni o beere ọlọjẹ okiti ni kikun pẹlu idiyele ti 67.
Jẹ ki a rii boya atọka B-igi deede ṣe iranlọwọ fun wa:
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)Atọka naa ko tile gbero. Atọka igi B ko mọ ti aye ti awọn eroja kọọkan ninu awọn iye itọka.
A nilo atọka GIN kan.
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)Atọka GIN ṣe atilẹyin ṣiṣe aworan awọn iye ẹyọkan lodi si awọn iye akojọpọ atọka, ti o yọrisi idiyele ero ibeere kan ti o ju idaji lọ.
Bikòße ti àdáwòkọ atọka
Awọn atọka kojọpọ lori akoko, ati nigba miiran atọka tuntun le ni itumọ kanna gẹgẹbi ọkan ninu awọn iṣaaju. O le lo wiwo katalogi lati gba awọn asọye SQL-eniyan ti awọn atọka. pg_indexes. O tun le ni irọrun wa awọn itumọ kanna:
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 atọka
O le ṣẹlẹ pe o pari pẹlu ọpọlọpọ awọn atọka, ọkan ninu eyiti o ṣe atọkasi titobi awọn ọwọn ti o tọka awọn atọka miiran. Eyi le tabi ko le jẹ iwunilori — superset le ja si awọn iwoye atọka-nikan, eyiti o dara, ṣugbọn o le gba aaye ti o pọ ju, tabi ibeere ti superset ti pinnu lati mu dara julọ ko lo mọ.
Ti o ba nilo lati ṣe adaṣe adaṣe itumọ ti iru awọn atọka, o le bẹrẹ pẹlu lati tabili pg_catalog.
Awọn atọka ti a ko lo
Bi awọn ohun elo ti o lo awọn apoti isura infomesonu ṣe ndagba, bẹ ni awọn ibeere ti wọn lo. Awọn atọka ti a ṣafikun tẹlẹ le ma ṣe lo nipasẹ eyikeyi ibeere. Nigbakugba ti a ti ṣayẹwo atọka kan, o jẹ samisi nipasẹ oluṣakoso awọn iṣiro, ati ninu wiwo katalogi eto pg_stat_user_indexes o le wo iye naa idx_scan, eyi ti o jẹ a akojo counter. Titọpa iye yii ni akoko kan (sọ oṣu kan) yoo funni ni imọran to dara ti iru awọn atọka ti a ko lo ati pe o le lọ silẹ.
Eyi ni ibeere kan lati gba awọn iṣiro ọlọjẹ lọwọlọwọ ti gbogbo awọn atọka ninu ero ‘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)Atunṣe awọn atọka pẹlu awọn titiipa diẹ
Awọn atọka nigbagbogbo nilo lati tun tunṣe, fun apẹẹrẹ nigbati wọn ba di bloated, ati atunkọ le ṣe iyara ọlọjẹ naa. Bakannaa awọn atọka le bajẹ. Yiyipada awọn paramita atọka le tun nilo atunṣeto rẹ.
Mu iṣẹda atọka ti o jọra ṣiṣẹ
Ni PostgreSQL 11, ṣiṣẹda atọka B-Igi jẹ nigbakanna. Lati ṣe iyara ilana ẹda, ọpọlọpọ awọn oṣiṣẹ ti o jọra le ṣee lo. Sibẹsibẹ, rii daju pe awọn aṣayan atunto wọnyi ti ṣeto ni deede:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Awọn iye aiyipada kere ju. Bi o ṣe yẹ, awọn nọmba wọnyi yẹ ki o pọ si pẹlu nọmba awọn ohun kohun ero isise. Ka siwaju ninu .
Iṣẹda atọka abẹlẹ
O le ṣẹda atọka ni abẹlẹ nipa lilo aṣayan CONCURRENTLY awọn pipaṣẹ CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXIlana ẹda atọka yii yatọ si ọkan deede ni pe ko nilo titiipa lori tabili, ati nitorinaa ko ṣe idiwọ awọn iṣẹ kikọ. Ni apa keji, o gba akoko diẹ sii ati gba awọn orisun diẹ sii.
Postgres n pese irọrun pupọ fun ṣiṣẹda awọn atọka ati awọn ọna lati yanju eyikeyi awọn ọran pataki, bakanna bi awọn ọna lati ṣakoso ibi-ipamọ data ti ohun elo rẹ ba dagba explodingly. A nireti pe awọn imọran wọnyi yoo ṣe iranlọwọ fun ọ lati gba awọn ibeere rẹ ni iyara ati pe data data rẹ ṣetan lati ṣe iwọn.
orisun: www.habr.com