M'dziko la Postgres, ma index ndi ofunikira pakuyendetsa bwino kosungirako nkhokwe (yotchedwa "mulu"). Postgres sichigwirizana ndi kuphatikizika kwake, ndipo kamangidwe ka MVCC kumakupangitsani kuti mukhale ndi mitundu yambiri yamtundu womwewo. Chifukwa chake, ndikofunikira kwambiri kuti muthe kupanga ndikusunga ma index abwino othandizira mapulogalamu.
Nawa maupangiri owongolera ndikuwongolera kugwiritsa ntchito ma index.
Zindikirani: mafunso omwe akuwonetsedwa pansipa amagwira ntchito yosasinthidwa .
Kugwiritsa Ntchito Covering Indexes
Tiyeni tiwone pempho loti tichotse ma adilesi a imelo a ogwiritsa ntchito omwe sanagwiritse ntchito. Table customer pali mzati active, ndipo funso ndi losavuta:
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)
Funsoli limatengera kusanthula kwa tebulo lonse customer. Tiyeni tipange cholozera pagawo 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)
Zinathandiza, scanner yotsatila idasandulika "index scan". Izi zikutanthauza kuti Postgres isanthula index "idx_cust1", kenako pitilizani kufufuza mulu wa tebulo kuti muwerenge zomwe zili m'mizere ina (pankhaniyi, gawoli email) zomwe funso likufunika.
Zolemba zophimba zimayambitsidwa mu PostgreSQL 11. Amakulolani kuti muphatikizepo mizati imodzi kapena zingapo zowonjezera muzolembazo - mfundo zake zimasungidwa mu sitolo ya data.
Ngati titapezerapo mwayi pa izi ndikuwonjezera mtengo wa imelo mkati mwa index, ndiye kuti Postgres sakanafunikira kusaka mulu wa tebulo kuti apeze mtengo. email. Tiyeni tiwone ngati izi zigwira ntchito:
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' imatiuza kuti funsoli tsopano likungofunika index, zomwe zimathandiza kupewa disk I / O kuti muwerenge mulu wa tebulo.
Milozera yophimba ikupezeka pamitengo ya B yokha. Komabe, mu nkhani iyi, khama kukonza adzakhala apamwamba.
Kugwiritsa Ntchito Partial Indexes
Zolozera pang'ono zimalozera kagawo kakang'ono ka mizere mu tebulo. Izi zimasunga kukula kwa ma index ndikupanga masikelo mwachangu.
Tiyerekeze kuti tikufuna kupeza mndandanda wa ma adilesi a imelo a makasitomala athu ku California. Pempho lidzakhala motere:
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)Zomwe ma index wamba angatipatse:
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)
Sakanizani address chasinthidwa ndi index scan idx_address1kenako anasanthula muluwo address.
Popeza ili ndi funso lomwe limafunsidwa pafupipafupi ndipo likufunika kukonzedwa bwino, titha kugwiritsa ntchito index yocheperako, yomwe imalemba mizere yokhayo yokhala ndi maadiresi omwe chigawocho chili ndi maadiresi. ‘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)
Tsopano funso likungowerenga idx_address2 ndipo sichikhudza tebulo address.
Kugwiritsa Ntchito Multi-Value Indexes
Magawo ena oti alembetsedwe sangakhale ndi mtundu wa data wa scalar. Mitundu yazanja ngati jsonb, arrays и tsvector zili ndi zophatikiza kapena zingapo. Ngati mukufuna kulondolera mindandanda yotere, nthawi zambiri mumayenera kufufuza zikhalidwe zonse zomwe zili m'zakudyazo.
Tiyeni tiyese kupeza maudindo a mafilimu onse omwe ali ndi mabala omwe sanapambane. Table film pali lemba lolemba lotchedwa special_features. Ngati filimuyo ili ndi "katundu wapadera", ndiye kuti gawoli lili ndi zinthu monga malemba Behind The Scenes. Kuti tifufuze mafilimu onsewa, tiyenera kusankha mizere yonse ndi "Behind The Scenes" pamene chilichonse magulu osiyanasiyana special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Wogwiritsa ntchito nesting @> imayang'ana ngati mbali yakumanja ndi kagawo kakang'ono kumanzere.
Pemphani dongosolo:
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)Chomwe chimapempha kuwunika kwathunthu ndi mtengo wa 67.
Tiyeni tiwone ngati mlozera wanthawi zonse wa B-tree umatithandiza:
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)Mlozerawu sunaganizidwe nkomwe. Mlozera wa B-tree sudziwa za kukhalapo kwa zinthu payokha pamitengo yolondoleredwa.
Tikufuna index ya 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)Mlozera wa GIN umathandizira kupanga mapu amtundu umodzi motsutsana ndi magulu ophatikizika, zomwe zimapangitsa mtengo wamafunso wopitilira theka.
Kuchotsa ma index obwereza
Ma index amawunjikana m’kupita kwa nthaŵi, ndipo nthaŵi zina mlozera watsopano ukhoza kukhala ndi tanthauzo lofanana ndi limodzi la m’mbuyomo. Mutha kugwiritsa ntchito mawonedwe a kalozera kuti mupeze matanthauzidwe a SQL owerengeka ndi anthu. pg_indexes. Mutha kupezanso matanthauzidwe ofananira mosavuta:
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
Zitha kuchitika kuti mutha kukhala ndi ma index ambiri, amodzi omwe amalozera magulu apamwamba omwe amalozera ma index ena. Izi zitha kukhala zofunika kapena sizingakhale zofunika - superset ikhoza kubweretsa ma index okha, omwe ndi abwino, koma angatenge malo ochulukirapo, kapena funso lomwe superset lidapangidwa kuti liwonjezeke siligwiritsidwanso ntchito.
Ngati mukufuna kusintha tanthauzo la ma index awa, mutha kuyamba nawo kuchokera patebulo pg_catalog.
Zolozera zosagwiritsidwa ntchito
Momwe mapulogalamu omwe amagwiritsa ntchito nkhokwe akusintha, momwemonso mafunso omwe amagwiritsa ntchito. Ma index omwe adawonjezeredwa kale sangagwiritsidwenso ntchito ndi funso lililonse. Nthawi iliyonse index ikasinthidwa, imazindikiridwa ndi woyang'anira ziwerengero, komanso mawonekedwe amtundu wamakina pg_stat_user_indexes mukhoza kuwona mtengo wake idx_scan, yomwe ndi kauntala yowonjezera. Kutsata mtengowu pakapita nthawi (nenani mwezi) kumapereka lingaliro labwino lazomwe sizikugwiritsidwa ntchito ndipo zitha kugwetsedwa.
Nayi funso kuti mupeze mawerengedwe aposachedwa amitundu yonse mu 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)Kumanganso ma index okhala ndi maloko ochepa
Ma index nthawi zambiri amafunikira kumangidwanso, mwachitsanzo akakhala otupa, ndipo kumanganso kumatha kufulumizitsa jambulani. Komanso ma index akhoza kuwonongeka. Kusintha magawo a index kungafunenso kuyimanganso.
Yambitsani kupanga index yofananira
Mu PostgreSQL 11, kupanga index ya B-Tree ndikofanana. Kuti mufulumizitse ntchito yolenga, antchito angapo ofanana angagwiritsidwe ntchito. Komabe, onetsetsani kuti masinthidwe awa akhazikitsidwa molondola:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Zosasintha ndizochepa kwambiri. Momwemo, ziwerengerozi ziyenera kuwonjezeka pamodzi ndi chiwerengero cha ma processor cores. Werengani zambiri mu .
Kupanga zolozera zakumbuyo
Mutha kupanga cholozera chakumbuyo pogwiritsa ntchito njirayo CONCURRENTLY malamulo CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXNjira yopangira index iyi imasiyana ndi momwe imakhalira nthawi zonse chifukwa sichifuna loko patebulo, chifukwa chake sichiletsa kulemba. Kumbali ina, zimatenga nthawi yochulukirapo komanso zimawononga zinthu zambiri.
Postgres imapereka kusinthasintha kwakukulu popanga ma index ndi njira zothetsera vuto lililonse lapadera, komanso njira zoyendetsera nkhokwe ngati pulogalamu yanu ikukula kwambiri. Tikukhulupirira kuti malangizowa akuthandizani kuti mafunso anu akhale mwachangu komanso kuti database yanu ikonzekere.
Source: www.habr.com