I roto i te ao Postgres, he mea nui nga tohu tohu ki te whakatere pai i te rokiroki raraunga (e kiia ana ko te puranga). Kare a Postgres e tautoko i te whakahiato mo taua mea, a na te hoahoanga MVCC ka mutu koe ki te maha o nga putanga o te tuple kotahi. Na reira, he mea nui kia kaha ki te hanga me te pupuri i nga taurangi pai hei tautoko i nga tono.
Ka kawea atu e ahau ki a koe etahi tohutohu mo te arotau me te whakapai ake i te whakamahinga o nga taurangi.
Tuhipoka: Ko nga patai e whakaatuhia ana i raro nei ka mahi i runga i te kore whakarereke .
Te Whakamahi Taurangi Uwhi
Me titiro tatou ki tetahi tono ki te tiki i nga wahitau imeera mo nga kaiwhakamahi hohekore. I te tepu customer he poupou active, a he ngawari te patai:
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)
Ka karangahia e te patai te raupapa matawai ripanga katoa customer. Me hanga he taurangi ki te pou 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)
I awhinatia, ka huri te karapa o muri ki te "index scan". Ko te tikanga ka ngoki a Postgres i te taurangi "idx_cust1", ka haere tonu ki te rapu i te puranga ripanga ki te panui i nga uara o etahi atu pou (i tenei keehi, ko te pou email) e hiahiatia ana e te tono.
I whakauruhia e PostgreSQL 11 nga tohu tohu. Ka taea e koe te whakauru kia kotahi, neke atu ranei nga pou taapiri i roto i te taurangi ake - ka penapena o raatau uara ki te toa raraunga taurangi.
Mena i whakamahia e matou tenei waahanga me te taapiri i te uara imeera ki roto i te taurangi, karekau a Postgres e titiro ki te puranga tepu mo te uara. email. Kia kite mena ka mahi tenei:
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 kii mai ana ko te patai inaianei me te taurangi anake, e awhina ana ki te karo i nga kōpae I/O katoa ki te panui i te puranga o te ripanga.
I tenei ra, kei te waatea noa nga tohu tohu mo nga rakau-B. Heoi, i tenei keehi ka nui ake te kaha tiaki.
Te Whakamahi i nga Taurangi Wahi
Ka tohuhia e nga taupū wahanga he waahanga iti o nga rarangi o te ripanga. Ma tenei ka taea e koe te tiaki i te rahi o te taurangi me te mahi tere i nga karapa.
Me kii me kii he rarangi o nga wahitau imeera mo o taatau kaihoko i California. Ka penei te tono:
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 nga taurangi auau ka homai ki a tatou:
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)
Matawai address kua whakakapihia e te matawai taupū idx_address1, katahi ka karapahia te puranga address.
I te mea he uiui auau tenei, me arotau ano, ka taea e tatou te whakamahi i tetahi waahanga taupū, e tohu ana i era rarangi anake me nga wahitau kei reira te waahi. ‘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)
Inaianei ka panui noa te tono idx_address2 a kaore e pa ki te tepu address.
Te whakamahi Taupū Uara-maha
Ko etahi o nga pou e tika ana kia taurangi kaore pea he momo raraunga tauine. Momo tīwae rite jsonb, arrays и tsvector kei roto nga tikanga whakakotahi, maha ranei. Mena ka hiahia koe ki te tohu i nga pou penei, me rapu koe mo nga uara takitahi katoa o aua pou.
Me ngana tatou ki te rapu i nga taitara o nga kiriata katoa kei roto nga topenga mai i nga tango kaore i angitu. I te tepu film he tīwae kuputuhi e kiia ana special_features. Mēnā he "taonga motuhake" kei tētahi kiriata, kei roto i te tīwae he huānga kei te ahua o te huānga kuputuhi Behind The Scenes. Hei rapu mo nga kiriata pera, me kowhiria nga rarangi katoa me te "Behind The Scenes" i tetahi uara huānga special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Kaimahi pupuri @> ka tirohia mehemea he huinga iti te taha matau o te taha maui.
Mahere Uiui:
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 tono ana i te karapa puranga katoa me te utu 67.
Kia kite tatou mehemea ka awhina te taurangi rakau-B ia tatou:
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)Kaore i whakaarohia te taurangi. Ko te tohu B-rakau kaore he whakaaro mo te noho o nga huānga takitahi i roto i nga uara e tohuhia ana.
Kei te hiahia maatau he tohu 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)E tautoko ana te taurangi GIN ki te mapi i nga uara takitahi ki nga uara hiato kua tohua, ka nui ake i te haurua o te utu mo te mahere patai.
Te whakakore i nga tohu taarua
Ka whakaemihia nga taurangi i roto i te waa, a, i etahi wa ka rite tonu te whakamaramatanga o te taurangi hou ki tetahi o nga mea o mua. Ka taea e koe te whakamahi i te tiro putumōhio ki te tiki i nga whakamaramatanga taupū SQL ka taea e te tangata te panui pg_indexes. Ka taea hoki e koe te kimi ngawari i nga whakamaramatanga ano:
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)
Taupū Superset
Tera pea ka kohia e koe te maha o nga taurangi, ko tetahi e tohu ana i te huinga huinga o nga pou e tohuhia ana e etahi atu tohu. Ka pai pea tenei, kaore ranei - ka puta mai he karapa-anake ma te superset, he pai, engari ka nui rawa te mokowā, karekau ranei te patai i whakaarohia e te superset ki te arotau.
Mena ka hiahia koe ki te whakaaunoa i te whakamaramatanga o aua taurangi, ka taea e koe te timata mai i te tepu pg_catalog.
Taupū kore whakamahia
I te wa e tipu haere ana nga tono e whakamahi ana i nga papaunga raraunga, ka pera ano nga patai e whakamahia ana e ratou. Ko nga tohu kua taapirihia i mua ka kore e whakamahia e nga patai. Ia wa ka karapahia he taurangi, ka tohuhia e te kaiwhakahaere tatauranga me te tirohanga putumōhio punaha pg_stat_user_indexes ka kite koe i te uara idx_scan, he porotiti whakahiato. Ko te whai i tenei uara mo te wa roa (mehemea ko te marama) ka maarama he aha nga tohu kaore e whakamahia ana ka taea te whakaheke.
Anei he tono ki te tiki i nga tatau karapa o naianei o nga taurangi katoa i roto i te aronuinga ‘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)Te hanga ano i nga tohu me te iti ake o nga raka
I te nuinga o nga wa me hanga ano nga tohu tohu, hei tauira ka pupuhi ana, ka tere ake te mahi karapa. Ka taea hoki te pirau nga tohu. Ko te whakarereke i nga tawhā taupū ka hiahia ano kia hanga ano.
Whakahohea te hanga taupū whakarara
I roto i te PostgreSQL 11, ko te hanga tohu tohu B-Tree he wa kotahi. Hei tere ake i te hangahanga, ka taea te whakamahi etahi kaimahi whakarara. Heoi, me whakarite kia tika enei tautuhinga whirihoranga:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;He iti rawa nga uara taunoa. Ko te tikanga, me piki ake enei nama me te maha o nga uho tukatuka. Pānuitia atu i roto i .
Waihanga taupū papamuri
Ka taea e koe te hanga i te tohu i te papamuri ma te whakamahi i te whiringa CONCURRENTLY ope CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXHe rereke tenei tikanga hanga taurangi mai i te tikanga o mua na te mea kaore e hiahiatia kia kati te tepu, na reira kaore e aukati i nga mahi tuhi. I tetahi atu taha, ka roa ake, ka pau nga rauemi.
Ka whakaratohia e Postgres te maha o nga whiringa ngawari mo te hanga tohu tohu me nga otinga ki etahi keehi motuhake, me te whakarato hoki i nga huarahi ki te whakahaere i te paataka korero mena ka tipu to tono. Ko te tumanako ka awhina enei tohutohu i a koe ki te tere o patai me te reri o to papaunga raraunga ki te tauine.
Source: will.com