Ym myd Postgres, mae mynegeion yn hanfodol i lywio'r storfa gronfa ddata yn effeithlon (a elwir yn domen). Nid yw Postgres yn cefnogi clystyru ar ei gyfer, ac mae pensaernïaeth MVCC yn achosi i chi gael llawer o fersiynau o'r un tuple yn y pen draw. Felly, mae'n bwysig iawn gallu creu a chynnal mynegeion effeithlon i gefnogi ceisiadau.
Dygaf eich sylw at rai awgrymiadau ar gyfer optimeiddio a gwella'r defnydd o fynegeion.
Nodyn: Mae'r ymholiadau a ddangosir isod yn gweithio ar heb eu haddasu .
Defnyddio Mynegeion Eglurhaol
Gadewch i ni edrych ar gais i adalw cyfeiriadau e-bost ar gyfer defnyddwyr anactif. Yn y bwrdd customer mae colofn active, ac mae'r ymholiad yn syml:
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)
Mae'r ymholiad yn galw'r dilyniant sgan tabl cyflawn customer. Gadewch i ni greu mynegai ar y golofn 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)
Wedi helpu, sgan dilynol wedi ei droi yn "index scan" . Mae hyn yn golygu y bydd Postgres yn cropian y mynegai "idx_cust1“ , ac yna parhewch i chwilio'r domen tabl i ddarllen gwerthoedd colofnau eraill (yn yr achos hwn, y golofn email) sydd ei angen ar y cais.
Cyflwynodd PostgreSQL 11 fynegeion eglurhaol. Maent yn caniatáu ichi gynnwys un neu fwy o golofnau ychwanegol yn y mynegai ei hun - mae eu gwerthoedd yn cael eu storio yn y storfa ddata mynegai.
Pe baem yn defnyddio'r nodwedd hon ac yn ychwanegu'r gwerth e-bost y tu mewn i'r mynegai, yna ni fyddai angen i Postgres edrych yn y domen tabl am y gwerth email. Gawn ni weld a yw hyn yn gweithio:
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" yn dweud wrthym mai dim ond y mynegai sydd ei angen ar yr ymholiad nawr, sy'n helpu i osgoi'r holl ddisg I/O i ddarllen pentwr y tabl.
Heddiw, dim ond ar gyfer coed-B y mae mynegeion eglurhaol ar gael. Fodd bynnag, yn yr achos hwn bydd yr ymdrech cynnal a chadw yn uwch.
Defnyddio Mynegeion Rhannol
Mae mynegeion rhannol yn mynegeio is-set yn unig o resi'r tabl. Mae hyn yn eich galluogi i arbed maint mynegai a pherfformio sganiau yn gyflymach.
Gadewch i ni ddweud bod angen i ni gael rhestr o gyfeiriadau e-bost ar gyfer ein cwsmeriaid yng Nghaliffornia. Bydd y cais fel hyn:
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)Yr hyn y bydd mynegeion rheolaidd yn ei roi i ni:
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)
Sganio address wedi'i ddisodli gan sganio mynegai idx_address1, ac yna sganiwyd y domen address.
Gan fod hwn yn ymholiad aml a bod angen ei optimeiddio, gallwn ddefnyddio mynegai rhannol, sy'n mynegeio dim ond y rhesi hynny â chyfeiriadau y mae'r ardal ynddynt ‘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)
Nawr mae'r cais yn darllen yn unig idx_address2 ac nid yw'n cyffwrdd â'r bwrdd address.
Defnyddio Mynegeion Aml-Werth
Efallai na fydd rhai colofnau y mae angen eu mynegeio yn cynnwys math o ddata sgalar. Mathau o golofnau fel jsonb, arrays и tsvector cynnwys ystyron cyfansawdd neu luosog. Os oes angen mynegeio colofnau o'r fath, fel arfer mae'n rhaid i chi chwilio am yr holl werthoedd unigol yn y colofnau hynny.
Gadewch i ni geisio dod o hyd i deitlau pob ffilm sy'n cynnwys clipiau o gofnodion aflwyddiannus. Yn y bwrdd film mae colofn destun o'r enw special_features. Os oes gan ffilm yr "eiddo arbennig", yna mae'r golofn yn cynnwys elfen ar ffurf arae testun Behind The Scenes. I chwilio am yr holl ffilmiau o'r fath, mae angen i ni ddewis pob rhes gyda “Behind The Scenes” yn unrhyw gwerthoedd arae special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Gweithredwr cyfyngu @> yn gwirio a yw'r ochr dde yn is-set o'r ochr chwith.
Cynllun ymholiad:
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)Sy'n gofyn am sgan pentwr llawn gyda chost o 67.
Gawn ni weld a fydd mynegai coed-B rheolaidd yn ein helpu ni:
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)Ni ystyriwyd y mynegai hyd yn oed. Nid oes gan fynegai B-coed unrhyw syniad am fodolaeth elfennau unigol yn y gwerthoedd y mae'n eu mynegeio.
Mae angen mynegai GIN arnom.
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)Mae'r mynegai GIN yn cefnogi mapio gwerthoedd unigol i werthoedd cyfansawdd wedi'u mynegeio, gan arwain at fwy na hanner cost y cynllun ymholiad.
Cael gwared ar fynegeion dyblyg
Mae mynegeion yn cronni dros amser, ac weithiau gall mynegai newydd gynnwys yr un diffiniad ag un o'r rhai blaenorol. Gallwch ddefnyddio'r golwg catalog i gael diffiniadau mynegai SQL y gall pobl eu darllen pg_indexes. Gallwch hefyd ddod o hyd i'r un diffiniadau yn hawdd:
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)
Mynegeion Superset
Mae'n bosibl y byddwch yn cronni llawer o fynegeion, ac mae un ohonynt yn mynegeio cyfres o golofnau y mae mynegeion eraill yn eu mynegeio. Gall hyn fod yn ddymunol neu beidio - gall uwchset arwain at sgan mynegrif yn unig, sy'n dda, ond fe all gymryd gormod o le, neu nid yw'r ymholiad y bwriadwyd ei optimeiddio gan yr uwch-set yn cael ei ddefnyddio mwyach.
Os oes angen i chi awtomeiddio'r diffiniad o fynegeion o'r fath, gallwch chi ddechrau o'r bwrdd pg_catalog.
Mynegeion nas defnyddiwyd
Wrth i gymwysiadau sy'n defnyddio cronfeydd data esblygu, felly hefyd yr ymholiadau y maent yn eu defnyddio. Ni all mynegeion a ychwanegwyd yn flaenorol gael eu defnyddio mwyach gan unrhyw ymholiadau. Bob tro y caiff mynegai ei sganio, caiff ei nodi gan y rheolwr ystadegau ac yng ngolwg catalog y system pg_stat_user_indexes gallwch weld y gwerth idx_scan, sef rhifydd cronnus. Bydd olrhain y gwerth hwn dros gyfnod o amser (mis dyweder) yn rhoi syniad da o ba fynegeion nad ydynt yn cael eu defnyddio ac y gellid eu gollwng.
Dyma gais i gael y cyfrifon sgan cyfredol o'r holl fynegeion yn y sgema ‘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)Ailadeiladu mynegeion gyda llai o gloeon
Yn aml mae angen ail-greu mynegeion, er enghraifft pan fyddant yn chwyddedig, a gall ail-greu gyflymu'r sganio. Gall mynegeion hefyd gael eu llygru. Efallai y bydd angen ail-greu hefyd er mwyn newid paramedrau mynegai.
Galluogi creu mynegai cyfochrog
Yn PostgreSQL 11, mae creu mynegai B-Tree yn gydamserol. Er mwyn cyflymu'r broses greu, gellir defnyddio sawl gweithiwr cyfochrog. Fodd bynnag, gwnewch yn siŵr bod y gosodiadau cyfluniad hyn wedi'u gosod yn gywir:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Mae'r gwerthoedd diofyn yn rhy fach. Yn ddelfrydol, dylai'r niferoedd hyn gynyddu ynghyd â nifer y creiddiau prosesydd. Darllenwch fwy yn .
Creu cefndir mynegai
Gallwch greu'r mynegai yn y cefndir gan ddefnyddio'r opsiwn CONCURRENTLY gorchmynion CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXMae'r weithdrefn creu mynegai hon yn wahanol i'r un arferol gan nad oes angen cloi'r bwrdd, ac felly nid yw'n rhwystro gweithrediadau ysgrifennu. Ar y llaw arall, mae'n cymryd mwy o amser ac yn defnyddio mwy o adnoddau.
Mae Postgres yn darparu llawer o opsiynau hyblyg ar gyfer creu mynegeion ac atebion i unrhyw achosion arbennig, ac mae hefyd yn darparu ffyrdd o reoli'r gronfa ddata os bydd eich cais yn tyfu'n ffrwydrol. Gobeithiwn y bydd yr awgrymiadau hyn yn eich helpu i wneud eich ymholiadau yn gyflym a'ch cronfa ddata yn barod i raddfa.
Ffynhonnell: hab.com