Ma ka honua Postgres, he mea koʻikoʻi nā kuhikuhi no ka hoʻokele pono ʻana i ka waihona waihona (i kapa ʻia ʻo ka puʻu). ʻAʻole kākoʻo ʻo Postgres i ka clustering no ia mea, a ʻo ka hoʻolālā MVCC e hoʻopau iā ʻoe me nā mana he nui o ka tuple like. No laila, he mea koʻikoʻi ka hiki ke hana a mālama i nā indexes kūpono e kākoʻo i nā noi.
Hāʻawi wau i kou manaʻo i kekahi mau ʻōlelo aʻoaʻo no ka hoʻomaikaʻi ʻana a me ka hoʻomaikaʻi ʻana i ka hoʻohana ʻana i nā kuhikuhi.
'Ōlelo Aʻo: Ke hana nei nā nīnau i hōʻike ʻia ma lalo nei me ka hoʻololi ʻole ʻia .
Ke hoʻohana nei i nā Index Covering
E nānā i kahi noi e kiʻi i nā leka uila no nā mea hoʻohana ʻole. I ka papaʻaina customer aia he kolamu active, a ua maʻalahi ka nīnau:
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)
Kāhea ka nīnau i ke kaʻina scan papa piha customer. E hana kākou i papa kuhikuhi ma ke kolamu 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)
Ua kōkua ʻia, ua hoʻololi ʻia ka scan ma hope i "index scan". ʻO kēia ke ʻano e kolo ʻo Postgres i ka index "idx_cust1", a laila e hoʻomau i ka ʻimi ʻana i ka puʻu papaʻaina e heluhelu i nā waiwai o nā kolamu ʻē aʻe (i kēia hihia, ke kolamu. email) e pono ai ka noi.
Ua hoʻokomo ʻo PostgreSQL 11 i ka uhi ʻana i nā kuhikuhi. Hāʻawi lākou iā ʻoe e hoʻokomo i hoʻokahi a ʻoi aku paha nā kolamu i loko o ka index ponoʻī - mālama ʻia kā lākou mau waiwai i ka hale kūʻai data index.
Inā mākou i hoʻohana i kēia hiʻohiʻona a hoʻohui i ka waiwai leka uila i loko o ka index, a laila ʻaʻole pono ʻo Postgres e nānā i ka puʻu papa no ka waiwai. email. E nānā inā hana kēia:
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" e haʻi mai iā mākou he pono wale nō ka nīnau i kēia manawa, e kōkua i ka pale ʻana i ka disk I/O āpau e heluhelu i ka puʻu o ka papaʻaina.
I kēia mau lā, loaʻa nā ʻōlelo kuhikuhi no nā lāʻau B wale nō. Eia naʻe, i kēia hihia, e ʻoi aku ka kiʻekiʻe o ka mālama ʻana.
Ke hoʻohana ʻana i nā ʻōlelo kuhikuhi hapa
Hōʻike ka papa kuhikuhi hapa i kahi ʻāpana o nā lālani o ka papaʻaina. ʻAe kēia iā ʻoe e mālama i ka nui index a hana wikiwiki i nā scans.
E ʻōlelo mākou pono mākou e kiʻi i kahi papa inoa o nā leka uila no kā mākou mea kūʻai ma Kaleponi. Penei ka noi:
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)He aha nā papa kuhikuhi maʻamau e hāʻawi iā mākou:
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)
Pākuʻi address ua pani ʻia e ka scanning index idx_address1, a laila nānā ʻia ka puʻu address.
No ka mea he nīnau pinepine kēia a pono e hoʻomaikaʻi ʻia, hiki iā mākou ke hoʻohana i kahi papa kuhikuhi ʻāpana, kahi e kuhikuhi ai i kēlā mau lālani me nā helu wahi i ka wahi. ‘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)
I kēia manawa, heluhelu wale ka noi idx_address2 a hoopa ole i ka papaaina address.
Ke hoʻohana nei i nā helu helu helu lehulehu
ʻAʻole paha i loaʻa kekahi ʻano ʻikepili scalar i kekahi mau kolamu pono e kuhikuhi ʻia. Nā ʻano kolamu like jsonb, arrays и tsvector loaʻa nā manaʻo hui a lehulehu. Inā pono ʻoe e kuhikuhi i kēlā mau kolamu, pono ʻoe e ʻimi i nā waiwai āpau o kēlā me kēia kolamu.
E ho'āʻo kāua e ʻimi i nā poʻo inoa o nā kiʻiʻoniʻoni a pau i loaʻa nā kiʻi mai nā kiʻi pono ʻole. I ka papaʻaina film aia kahi kolamu kikokiko i kapa ʻia special_features. Inā loaʻa i kahi kiʻiʻoniʻoni kēia "waiwai kūikawā", a laila aia i loko o ke kolamu kahi mea ma ke ʻano o kahi ʻano kikokikona Behind The Scenes. No ka ʻimi ʻana i nā kiʻiʻoniʻoni a pau, pono mākou e koho i nā lālani āpau me "Behind The Scenes" ma kekahi pūʻulu waiwai special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Mea hana hoʻopaʻa @> nānā inā he ʻaoʻao ʻākau ka ʻaoʻao o ka ʻaoʻao hema.
Hoʻolālā nīnau:
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)E noi ana i kahi scan heap piha me ke kumu kūʻai o 67.
E ʻike kākou inā e kōkua ka papa kuhikuhi lāʻau B maʻamau iā mākou:
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)ʻAʻole i noʻonoʻo ʻia ka papa kuhikuhi. ʻAʻohe manaʻo o ka B-tree index e pili ana i ka noho ʻana o kēlā me kēia mea i nā waiwai i kuhikuhi ʻia.
Pono mākou i kahi helu 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)Kākoʻo ka GIN index i ka palapala ʻana i nā waiwai pākahi i ka helu ʻana i nā waiwai i hoʻohui ʻia, e hopena i ka hapalua o ke kumukūʻai o ka hoʻolālā nīnau.
E hoopau ana i na papa kuhikuhi papalua
Hōʻuluʻulu ʻia nā papa kuhikuhi i ka manawa, a i kekahi manawa hiki ke loaʻa i kahi papa kuhikuhi hou ka wehewehe like me kekahi o nā mea ma mua. Hiki iā ʻoe ke hoʻohana i ka nānā ʻana no ka loaʻa ʻana o nā wehewehe kikoʻī SQL hiki ke heluhelu ʻia e ke kanaka pg_indexes. Hiki iā ʻoe ke ʻimi maʻalahi i nā wehewehe like:
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)
Nā Papa kuhikuhi Superset
Hiki paha iā ʻoe ke hōʻiliʻili i nā helu helu he nui, ʻo kekahi o ia e kuhikuhi i kahi superset o nā kolamu i kuhikuhi ʻia e nā mea kuhikuhi ʻē aʻe. Makemake paha kēia a ʻaʻole paha - hiki i kahi superset ke loaʻa i kahi scan index-wale nō, maikaʻi ia, akā nui paha ka nui o ka manawa, a i ʻole ke hoʻohana ʻia ka nīnau a ka superset i manaʻo ʻia e loiloi.
Inā pono ʻoe e hoʻokaʻawale i ka wehewehe ʻana o ia mau kuhikuhi, hiki iā ʻoe ke hoʻomaka me mai ka papaʻaina pg_catalog.
Nā kuhikuhi i hoʻohana ʻole ʻia
Ke ulu nei nā noi e hoʻohana ana i nā ʻikepili, pēlā nō nā nīnau a lākou e hoʻohana ai. ʻAʻole hiki ke hoʻohana hou ʻia nā ʻōlelo kuhikuhi i hoʻohui ʻia e kekahi nīnau. I kēlā me kēia manawa e nānā ʻia kahi index, ʻike ʻia ia e ka luna helu helu a ma ka ʻike helu ʻōnaehana. pg_stat_user_indexes hiki iā ʻoe ke ʻike i ka waiwai idx_scan, ʻo ia ka helu helu hui. ʻO ka nānā ʻana i kēia waiwai ma kahi manawa (e ʻōlelo i hoʻokahi mahina) e hāʻawi i kahi manaʻo maikaʻi no nā mea i hoʻohana ʻole ʻia a hiki ke hoʻokuʻu ʻia.
Eia kahi noi e kiʻi i nā helu scan o kēia manawa o nā kuhikuhi a pau i ka 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)Ke kūkulu hou ʻana i nā papa kuhikuhi me nā laka liʻiliʻi
Pono e hana hou ʻia nā indexes, no ka laʻana i ka wā e hoʻohūhū ai, a hiki i ka hana hou ke wikiwiki i ka nānā ʻana. Hiki ke hoʻopōʻino ʻia nā papa kuhikuhi. Pono paha ka hoʻololi ʻana i nā ʻāpana kuhikuhi i kāna hana hou ʻana.
E ʻae i ka hana ʻana i ka papa kuhikuhi like
Ma PostgreSQL 11, hui pū ka hana ʻana i ka index index B-Tree. No ka wikiwiki o ka hana ʻana, hiki ke hoʻohana ʻia nā mea hana like. Eia naʻe, e hōʻoia i ka hoʻonohonoho pono ʻana o kēia mau hoʻonohonoho hoʻonohonoho:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Liʻiliʻi loa nā waiwai paʻamau. ʻO ke kūpono, pono e hoʻonui kēia mau helu me ka helu o nā cores processor. Heluhelu hou aku ma .
Hana i ka papa kuhikuhi hope
Hiki iā ʻoe ke hana i ka index ma ka hope me ka hoʻohana ʻana i ke koho CONCURRENTLY kauoha CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXHe ʻokoʻa kēia kaʻina hana index mai ka mea maʻamau i ka mea ʻaʻole ia e koi i ka laka ʻana i ka papaʻaina, a no laila ʻaʻole ia e ālai i nā hana kākau. Ma ka ʻaoʻao ʻē aʻe, lōʻihi ka lōʻihi a hoʻopau i nā kumuwaiwai hou aʻe.
Hāʻawi ʻo Postgres i nā koho maʻalahi no ka hana ʻana i nā kuhikuhi a me nā hoʻonā i nā hihia kūikawā, a hāʻawi pū i nā ala e hoʻokele ai i ka ʻikepili inā e ulu nui kāu noi. Manaʻo mākou e kōkua kēia mau ʻōlelo aʻoaʻo iā ʻoe e wikiwiki i kāu mau nīnau a mākaukau hoʻi kāu waihona e hoʻonui.
Source: www.habr.com