Ann an saoghal Postgres, tha clàran-amais deatamach airson a bhith a’ seòladh stòr-dàta gu h-èifeachdach (ris an canar an tiùrr). Chan eil Postgres a 'toirt taic do bhith a' cruinneachadh air a shon, agus tha ailtireachd MVCC ag adhbhrachadh gum bi thu a 'crìochnachadh le iomadh dreach den aon tuple. Mar sin, tha e glè chudromach a bhith comasach air clàran-amais èifeachdach a chruthachadh agus a chumail suas gus taic a thoirt do thagraidhean.
Bheir mi gu d’ aire cuid de mholaidhean airson cleachdadh chlàran-amais a mheudachadh agus a leasachadh.
Nota: Tha na ceistean gu h-ìosal ag obair air neo-atharraichte .
A 'cleachdadh clàran-amais còmhdaich
Bheir sinn sùil air iarrtas airson seòlaidhean puist-d fhaighinn air ais airson luchd-cleachdaidh neo-ghnìomhach. Anns a' bhòrd customer tha colbh ann active, agus tha a’ cheist sìmplidh:
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)
Canaidh a’ cheist an t-sreath scan bùird iomlan customer. Cruthaichidh sinn clàr-amais air a’ cholbh 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)
Air a chuideachadh, thionndaidh an scan às deidh sin gu “index scan". Tha seo a’ ciallachadh gun dèan Postgres an clàr-amais “idx_cust1“, agus an uairsin lean ort a’ sgrùdadh a’ bhùird gus luachan nan colbhan eile a leughadh (sa chùis seo, an colbh email) a tha a dhìth air an iarrtas.
Thug PostgreSQL 11 a-steach clàran-amais còmhdaich. Leigidh iad leat aon cholbh a bharrachd no barrachd a thoirt a-steach don chlàr-amais fhèin - tha na luachan aca air an stòradh anns an stòr dàta clàr-amais.
Ma chleachd sinn am feart seo agus ma chuir sinn an luach post-d taobh a-staigh a’ chlàr-amais, cha bhiodh feum aig Postgres coimhead anns a’ chlàr airson an luach email. Feuch sinn a bheil seo ag obair:
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“ ag innse dhuinn nach fheum a’ cheist a-nis ach an clàr-amais, a chuidicheas le bhith a’ seachnadh a h-uile diosc I/O gus an tiùrr a’ bhùird a leughadh.
An-diugh, chan eil clàran-amais còmhdaich rim faighinn ach airson craobhan B. Ach, sa chùis seo bidh an oidhirp cumail suas nas àirde.
A’ cleachdadh clàran-amais pàirt
Clàr-amais pàirt-chlàran dìreach fo-sheata de shreathan a’ chlàir. Leigidh seo leat meud clàr-amais a shàbhaladh agus sganaidhean a dhèanamh nas luaithe.
Canaidh sinn gum feum sinn liosta de sheòlaidhean puist-d fhaighinn airson ar teachdaichean ann an California. Bidh an t-iarrtas mar seo:
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)Dè na clàran-amais cunbhalach a bheir dhuinn:
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)
Sganadh address air a chuir na àite le sganadh clàr-amais idx_address1, agus an sin chaidh an carn a sganadh address.
Leis gur e ceist tric a tha seo agus feumar a mheudachadh, is urrainn dhuinn clàr-amais pàirt a chleachdadh, a bhios a’ dèanamh clàr-amais dìreach air na sreathan sin le seòlaidhean anns a bheil an sgìre ‘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)
A-nis chan eil an t-iarrtas a’ leughadh ach idx_address2 agus cha bhean e ris a' bhòrd address.
A’ cleachdadh clàran-amais ioma-luach
Is dòcha nach bi seòrsa dàta sgalar ann an cuid de cholbhan a dh’ fheumar a chlàradh. Seòrsaichean colbh mar jsonb, arrays и tsvector anns a bheil brìgh iom-fhillte no iomadach. Ma dh'fheumas tu na colbhan sin a chlàradh, mar as trice feumaidh tu na luachan fa leth anns na colbhan sin a lorg.
Feuchaidh sinn ri tiotalan a h-uile film a lorg anns a bheil criomagan bho ghlacadh neo-shoirbheachail. Anns a 'bhòrd film tha colbh teacsa ris an canar special_features. Ma tha an "seilbh shònraichte" seo aig film, tha eileamaid anns a 'cholbh ann an cruth sreath teacsa Behind The Scenes. Gus a h-uile film mar sin a lorg, feumaidh sinn a h-uile sreath a thaghadh le “Behind The Scenes” aig aon sam bith luachan array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Gnìomhaiche cumail suas @> sgrùdadh a bheil an taobh cheart na fho-sheata den taobh chlì.
Plana ceiste:
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 dh’ iarras sgan làn tiùrr le cosgais 67.
Feuch am faic sinn an cuidich clàr-amais cunbhalach de chraobh-B sinn:
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)Cha deach beachdachadh air an clàr-amais eadhon. Chan eil beachd sam bith aig clàr-amais B-craobhan mu na tha eileamaidean fa leth anns na luachan a tha e a’ clàradh.
Feumaidh sinn clàr-amais 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)Tha clàr-amais GIN a’ toirt taic do bhith a’ mapadh luachan fa leth gu luachan coimeasgaichte clàraichte, a’ ciallachadh gu bheil còrr air leth de chosgais a’ phlana ceiste.
A 'faighinn cuidhteas de chlàran-amais dùblaichte
Bidh clàran-amais a’ cruinneachadh thar ùine, agus uaireannan faodaidh an aon mhìneachadh a bhith ann an clàr-amais ùr ’s a bha aig an fheadhainn a bh’ ann roimhe. Faodaidh tu an sealladh catalog a chleachdadh gus mìneachaidhean clàr-amais SQL a ghabhas leughadh le daoine fhaighinn pg_indexes. Faodaidh tu cuideachd na h-aon mhìneachaidhean a lorg gu furasta:
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)
Clàr-innse Superset
Dh’ fhaodadh e tachairt gun cruinnich thu mòran chlàran-amais, aon dhiubh a’ dèanamh clàr-amais air sàr-sheata de cholbhan a bhios clàran-amais eile a’ clàradh. Is dòcha gu bheil seo neo-mhiannach - dh’ fhaodadh sganadh clàr-amais a-mhàin a bhith mar thoradh air superset, rud a tha math, ach dh’ fhaodadh gun toir e cus àite, no chan eilear a’ cleachdadh a’ cheist a bha an superset an dùil a mheudachadh tuilleadh.
Ma dh'fheumas tu am mìneachadh air clàran-amais mar sin a dhèanamh fèin-ghluasadach, faodaidh tu tòiseachadh le bhon bhòrd pg_catalog.
Clàr-amais gun chleachdadh
Mar a bhios tagraidhean a chleachdas stòran-dàta ag atharrachadh, mar sin cuideachd na ceistean a bhios iad a’ cleachdadh. Chan fhaod clàran-amais a chaidh a chur ris roimhe a chleachdadh tuilleadh le ceistean sam bith. Gach uair a thèid clàr-amais a sganadh, thèid a thoirt fa-near leis a’ mhanaidsear staitistig agus ann an sealladh catalog an t-siostaim pg_stat_user_indexes chì thu an luach idx_scan, a tha na chunntair tionalach. Le bhith a’ cumail sùil air an luach seo thar ùine (abair mìos) bheir sin deagh bheachd air dè na clàran-amais nach eilear a’ cleachdadh agus a dh’ fhaodadh a bhith air an leigeil sìos.
Seo iarrtas airson na cunntasan scan gnàthach fhaighinn de na clàran-amais uile san sgeama ‘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)Ath-thogail clàran-amais le nas lugha de ghlasan
Gu tric feumar clàran-amais ath-chruthachadh, mar eisimpleir nuair a dh’fhàsas iad fo bhlàth, agus faodaidh ath-chruthachadh sganadh a luathachadh. Faodaidh clàran-amais a bhith truaillidh cuideachd. Dh’ fhaodadh gum bi feum air ath-chruthachadh cuideachd ma dh’ atharraicheas crìochan clàr-amais.
Dèan comas air cruthachadh clàr-amais co-shìnte
Ann am PostgreSQL 11, tha cruthachadh clàr-amais B-Tree aig an aon àm. Gus am pròiseas cruthachaidh a luathachadh, faodar grunn luchd-obrach co-shìnte a chleachdadh. Ach, dèan cinnteach gu bheil na roghainnean rèiteachaidh seo air an suidheachadh ceart:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Tha na luachan bunaiteach ro bheag. Mas fheàrr, bu chòir na h-àireamhan sin àrdachadh còmhla ris an àireamh de choraichean pròiseasar. Leugh tuilleadh ann an .
Cruthachadh clàr-amais cùl-fhiosrachaidh
Faodaidh tu an clàr-amais a chruthachadh air a’ chùl a’ cleachdadh an roghainn CONCURRENTLY àitheantan CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTha am modh cruthachaidh clàr-amais seo eadar-dhealaichte bhon fhear àbhaisteach leis nach fheum e am bòrd a ghlasadh, agus mar sin chan eil e a’ cur bacadh air gnìomhachd sgrìobhaidh. Air an làimh eile, bheir e nas fhaide agus a 'caitheamh barrachd ghoireasan.
Tha Postgres a’ toirt seachad mòran roghainnean sùbailte airson clàran-amais agus fuasglaidhean a chruthachadh do chùisean sònraichte sam bith, agus tha e cuideachd a’ toirt seachad dòighean air an stòr-dàta a riaghladh ma dh’ fhàsas an tagradh agad gu spreadhaidh. Tha sinn an dòchas gun cuidich na molaidhean seo thu gus do cheistean a dhèanamh luath agus an stòr-dàta agad deiseil airson sgèile.
Source: www.habr.com