Di dunya Postgres, indéks penting pisan pikeun navigasi éfisién tina neundeun databés (disebut "numpuk"). Postgres teu ngarojong clustering pikeun eta, sarta arsitéktur MVCC ngabalukarkeun anjeun mungkas nepi ka loba versi tina tuple sarua. Ku sabab kitu, penting pisan pikeun tiasa nyiptakeun sareng ngajaga indéks éfisién pikeun ngadukung aplikasi.
Ieu sababaraha tip pikeun ngaoptimalkeun sareng ningkatkeun pamakean indéks.
Catetan: queries ditémbongkeun di handap dianggo dina unmodified .
Ngagunakeun Panutup Indexes
Hayu urang tingali pamundut pikeun nimba alamat email pikeun pangguna anu teu aktip. Méja customer aya kolom active, sareng patarosanna saderhana:
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)
Patarosan invokes runtuyan scan tabel pinuh customer. Hayu urang nyieun hiji indéks dina kolom 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)
Éta ngabantosan, scan salajengna janten "index scan". Ieu ngandung harti yén Postgres bakal nyeken indéks "idx_cust1", teras teraskeun milarian tumpukan tabel pikeun maca nilai kolom anu sanés (dina hal ieu, kolom email) anu diperyogikeun ku pamundut.
Panutup indéks diwanohkeun dina PostgreSQL 11. Éta ngamungkinkeun anjeun ngalebetkeun hiji atanapi langkung kolom tambahan dina indéks sorangan - nilaina disimpen dina toko data indéks.
Upami urang ngamangpaatkeun fitur ieu sareng nambihan nilai email di jero indéks, maka Postgres henteu kedah milarian tumpukan méja pikeun nilaina. email. Hayu urang tingali naha ieu bakal tiasa dianggo:
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' Nyarioskeun yén pamundut ayeuna ngan ukur peryogi indéks, anu ngabantosan sadaya disk I / O pikeun maca tumpukan méja.
Panutup indéks ayeuna ngan sadia pikeun B-tangkal. Nanging, dina hal ieu, usaha pangropéa bakal langkung luhur.
Ngagunakeun Indéks Parsial
Indéks parsial indéks ngan sawaréh ti baris dina tabel. Ieu ngahémat ukuran indéks sareng ngajantenkeun scan langkung gancang.
Sebutkeun urang hoyong kéngingkeun daptar alamat email para nasabah di California. Paménta bakal sapertos kieu:
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)Naon indéks biasa bakal masihan kami:
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 geus diganti ku scan indéks idx_address1lajeng discan numpuk address.
Kusabab ieu mangrupikeun patarosan anu sering sareng kedah dioptimalkeun, urang tiasa nganggo indéks parsial, anu ngan ukur ngindeks barisan sareng alamat dimana kabupaten ‘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)
Ayeuna patarosan ukur maca idx_address2 jeung teu noél méja address.
Ngagunakeun Indéks Multi-Nilai
Sababaraha kolom anu bakal diindeks tiasa henteu ngandung jinis data skalar. Jenis kolom sapertos jsonb, arrays и tsvector ngandung nilai komposit atawa sababaraha. Upami anjeun peryogi indéks kolom sapertos kitu, anjeun biasana kedah milarian sadaya nilai individu dina kolom éta.
Hayu urang cobian milarian judul sadaya pilem anu ngandung potongan tina nyandak anu henteu suksés. Méja film aya kolom téks disebut special_features. Lamun film boga ieu "sipat husus", lajeng kolom ngandung unsur salaku Asép Sunandar Sunarya téks Behind The Scenes. Pikeun milarian sadaya film sapertos kitu, urang kedah milih sadaya baris kalayan "Behind The Scenes" nalika sagala nilai Asép Sunandar Sunarya special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator nyarang @> mariksa lamun sisi katuhu mangrupakeun sawaréh ti sisi kénca.
Rencana pamundut:
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)Anu nyuhunkeun scan tumpukan pinuh kalayan biaya 67.
Hayu urang tingali naha indéks B-tangkal biasa ngabantosan urang:
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)indéks ieu malah teu dianggap. Indéks B-tangkal henteu sadar ayana elemen individu dina nilai indéks.
Urang peryogi indéks 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)Indéks GIN ngarojong pemetaan nilai tunggal ngalawan nilai komposit indéks, hasilna biaya rencana query leuwih ti satengah.
Ngaleungitkeun indéks duplikat
Indéks ngumpulkeun kana waktosna, sareng sakapeung indéks énggal tiasa ngandung definisi anu sami sareng salah sahiji anu sateuacana. Anjeun tiasa nganggo tampilan katalog pikeun kéngingkeun definisi SQL anu tiasa dibaca ku manusa ngeunaan indéks. pg_indexes. Anjeun ogé tiasa kalayan gampang mendakan definisi anu sami:
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)
Indéks Superset
Bisa lumangsung nu mungkas nepi ka loba indexes, salah sahiji nu indexes superset kolom nu indéks indexes séjén. Ieu tiasa atanapi henteu dipikahoyong-superset tiasa nyababkeun panyekenan indéks wungkul, anu saé, tapi peryogi seueur rohangan, atanapi pamundut anu ditujukeun pikeun dioptimalkeun superset henteu dianggo deui.
Upami anjeun kedah ngajadikeun otomatis definisi indéks sapertos kitu, anjeun tiasa mimitian ku tina méja pg_catalog.
indéks henteu kapake
Nalika aplikasi anu ngagunakeun database mekar, ku kituna ogé patarosan anu aranjeunna anggo. Indéks anu ditambihkeun sateuacana moal tiasa dianggo deui ku pamundut naon waé. Unggal waktos indéks diseken, éta ditandaan ku manajer statistik, sareng dina tampilan katalog sistem. pg_stat_user_indexes anjeun tiasa ningali nilai idx_scan, nu mangrupakeun counter kumulatif. Nyukcruk nilai ieu dina sababaraha waktos (sebutkeun sabulan) bakal masihan ide anu sae ngeunaan indéks mana anu henteu dianggo sareng tiasa turun.
Ieu mangrupikeun pamundut pikeun kéngingkeun cacah scan ayeuna sadaya indéks dina skéma ‘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)Ngawangun deui indéks kalayan sakedik konci
Indéks sering kedah diwangun deui, contona nalika aranjeunna kembung, sareng ngawangun deui tiasa nyepetkeun scan. Ogé indexes bisa meunang ruksak. Ngarobih parameter indéks ogé tiasa ngabutuhkeun ngawangun deui.
Aktipkeun kreasi indéks paralel
Dina PostgreSQL 11, nyiptakeun indéks B-Tree sakaligus. Pikeun ngagancangkeun prosés nyiptakeun, sababaraha pagawé paralel tiasa dianggo. Nanging, pastikeun pilihan konfigurasi ieu diatur leres:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Nilai standar leutik teuing. Ideally, angka ieu kudu nambahan babarengan jeung jumlah cores processor. Baca leuwih dina .
Nyiptakeun indéks latar tukang
Anjeun tiasa nyiptakeun indéks dina latar tukang nganggo pilihan CONCURRENTLY paréntah CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXProsedur nyiptakeun indéks ieu béda ti anu biasa sabab henteu meryogikeun konci dina méja, sareng ku kituna henteu ngahalangan operasi nyerat. Di sisi anu sanés, peryogi langkung seueur waktos sareng langkung seueur sumber daya.
Postgres nyadiakeun loba kalenturan pikeun nyieun indéks jeung cara pikeun ngajawab sagala kasus husus, kitu ogé cara pikeun ngatur database bisi aplikasi Anjeun tumuwuh explodingly. Kami ngarepkeun tip ieu bakal ngabantosan anjeun gancang patarosan sareng database anjeun siap skala.
sumber: www.habr.com