Ag baint úsáide as na gnéithe innéacsanna go léir i PostgreSQL

I saol Postgres, tá innéacsanna riachtanach chun stóráil an bhunachair sonraí a nascleanúint go héifeachtúil (ar a dtugtar "carn"). Ní thacaíonn Postgres le braisliú dó, agus cuireann ailtireacht an MVCC faoi deara go mbíonn go leor leaganacha den tuple céanna agat. Dá bhrí sin, tá sé an-tábhachtach a bheith in ann innéacsanna éifeachtacha a chruthú agus a chothabháil chun tacú le hiarratais.

Seo roinnt leideanna chun úsáid innéacsanna a bharrfheabhsú agus a fheabhsú.

Tabhair faoi deara: oibríonn na fiosruithe a thaispeántar thíos ar neamhmhodhnú bunachar sonraí samplach.

Ag Úsáid Innéacsanna Clúdaigh

Breathnaímid ar iarratas chun seoltaí ríomhphoist a bhaint as úsáideoirí neamhghníomhacha. Tábla customer tá colún active, agus tá an cheist simplí:

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)

Baineann an cheist leis an seicheamh scanadh tábla iomlán customer. Cruthaímid innéacs ar cholún 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)

Chuidigh sé, d'iompaigh an scanadh ina dhiaidh sin isteach "index scan“. Ciallaíonn sé seo go ndéanfaidh Postgres an t-innéacs a scanadh "idx_cust1" , agus ansin lean ar aghaidh ag cuardach an charn tábla chun luachanna na gcolún eile a léamh (sa chás seo, an colún email) a theastaíonn ón bhfiosrúchán.

Tugtar isteach innéacsanna cumhdaigh in PostgreSQL 11 . Ligeann siad duit colún breise amháin nó níos mó a chur san áireamh san innéacs féin - stóráiltear a luachanna sa stór sonraí innéacs.

Dá mbainfimid leas as an ngné seo agus má chuireamar an luach ríomhphoist laistigh den innéacs, ní bheadh ​​​​gá le Postgres cuardach a dhéanamh ar an gcarn tábla don luach. email. Feicfimid an n-oibreoidh sé seo:

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' a insíonn dúinn nach bhfuil ag teastáil ón bhfiosrúchán anois ach innéacs, rud a chabhraíonn le gach diosca I/O a sheachaint chun an carn tábla a léamh.

Níl innéacsanna clúdaigh ar fáil faoi láthair ach amháin do chrainn B. Mar sin féin, sa chás seo, beidh an iarracht chothabhála níos airde.

Ag Úsáid Innéacsanna Páirteach

Ní innéacsaíonn innéacsanna páirteacha ach fo-thacar de na sraitheanna i dtábla. Sábhálann sé seo méid na n-innéacsanna agus déanann sé scanadh níos tapúla.

Ligean le rá gur mhaith linn liosta de sheoltaí ríomhphoist ár gcustaiméirí a fháil i gCalifornia. Beidh an t-iarratas 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)

Cad a thabharfaidh gnáth-innéacsanna dúinn:

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)

Scan address Tá scanadh innéacs curtha ina ionad idx_address1agus ansin scanadh an gcarn address.

Ós rud é gur ceist mhinic é seo agus gur gá é a bharrfheabhsú, is féidir linn innéacs páirteach a úsáid, a dhéanann innéacsú ar na sraitheanna sin amháin le seoltaí ina bhfuil an ceantar. ‘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)

Anois ní léann ach an cheist idx_address2 agus nach dteagmháil leis an tábla address.

Ag baint úsáide as Innéacsanna Illuacha

Seans nach mbeidh cineál sonraí scálach i roinnt colúin atá le hinnéacsú. Is maith le cineálacha colún jsonb, arrays и tsvector go bhfuil luachanna ilchodacha nó iolracha ann. Más gá duit colúin den sórt sin a innéacsú, de ghnáth caithfidh tú cuardach a dhéanamh trí na luachanna aonair go léir sna colúin sin.

Déanaimis iarracht teacht ar theidil na scannán go léir ina bhfuil gearrthacha ó ghlactha nár éirigh leo. Tábla film tá colún téacs ar a dtugtar special_features. Má tá an "airíonna speisialta" seo ag an scannán, tá an eilimint mar eagar téacs sa cholún Behind The Scenes. Chun cuardach a dhéanamh ar gach scannán den sórt sin, ní mór dúinn gach sraith a roghnú le "Behind The Scenes" nuair aon luachanna eagar special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Oibreoir neadaithe @> seiceálacha má tá an taobh deas fo-thacar den taobh clé.

Plean iarratais:

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 iarrann scanadh carn iomlán ar chostas 67.

Feicfimid an gcabhraíonn innéacs rialta crann B linn:

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)

Níor measadh an t-innéacs fiú. Ní heol don innéacs B-crann go bhfuil gnéithe aonair sna luachanna innéacsaithe.

Tá innéacs GIN de dhíth orainn.

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)

Tacaíonn an t-innéacs GIN le luachanna aonair a mhapáil i gcoinne luachanna ilchodacha innéacsaithe, rud a fhágann go bhfuil costas plean fiosrúcháin níos mó ná leath.

Fáil réidh le hinnéacsanna dúblacha

Carnann innéacsanna le himeacht ama, agus uaireanta d’fhéadfadh an sainmhíniú céanna a bheith in innéacs nua agus a bhí ar na cinn roimhe seo. Is féidir leat amharc na catalóige a úsáid chun sainmhínithe SQL atá inléite ag daoine a fháil ar innéacsanna. pg_indexes. Is féidir leat sainmhínithe comhionanna a fháil go héasca freisin:

 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)

Innéacsanna Sárshraith

Is féidir go dtarlódh sé go mbeidh go leor innéacsanna agat, ceann acu a dhéanann innéacsú ar shárthacar de cholúin a dhéanann innéacsú ar innéacsanna eile. D’fhéadfadh nó nach mbeadh sé seo inmhianaithe—d’fhéadfadh scananna innéacs-amháin a bheith mar thoradh ar an sár-thacar, rud atá go maith, ach d’fhéadfadh go dtógfadh sé an iomarca spáis, nó ní bhaintear úsáid a thuilleadh as an bhfiosrúchán a raibh sé i gceist ag an sárshraith é a bharrfheabhsú.

Más gá duit an sainmhíniú ar innéacsanna den sórt sin a uathoibriú, is féidir leat tosú leis pg_innéacs ón mbord pg_catalog.

Innéacsanna neamhúsáidte

De réir mar a fhorbraíonn feidhmchláir a úsáideann bunachair shonraí, is amhlaidh a thagann na ceisteanna a úsáideann siad. Ní féidir innéacsanna a cuireadh leis níos luaithe a úsáid a thuilleadh le haon cheist. Gach uair a scanadh innéacs, déanann an bainisteoir staitisticí é a mharcáil, agus i radharc catalóg an chórais pg_stat_user_indexes is féidir leat an luach a fheiceáil idx_scan, atá ina chuntar carnach. Má dhéantar an luach seo a rianú thar thréimhse ama (abair mí) tabharfar smaoineamh maith duit faoi na hinnéacsanna nach bhfuil á n-úsáid agus a bhféadfaí iad a ísliú.

Seo ceist chun na háirimh reatha scanadh a fháil ar na hinnéacsanna go léir sa scéimre ‘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)

Innéacsanna a atógáil le níos lú glas

Is minic is gá innéacsanna a atógáil, mar shampla nuair a thagann siad faoi bhláth, agus féadann atógáil an scanadh a bhrostú. Chomh maith leis sin is féidir innéacsanna a fháil truaillithe. D'fhéadfadh go mbeadh gá le atógáil na bparaiméadar innéacs freisin.

Cumasaigh cruthú innéacs comhthreomhar

In PostgreSQL 11, cruthaítear innéacs B-Tree i gcomhthráth. Chun an próiseas cruthaithe a bhrostú, is féidir roinnt oibrithe comhthreomhara a úsáid. Mar sin féin, déan cinnte go bhfuil na roghanna cumraíochta seo socraithe i gceart:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Tá na luachanna réamhshocraithe ró-bheag. Go hidéalach, ba cheart go n-ardódh na huimhreacha seo chomh maith le líon na gcroíthe próiseálaithe. Léigh tuilleadh i doiciméadú.

Cruthú innéacs cúlra

Is féidir leat innéacs a chruthú sa chúlra ag baint úsáide as an rogha CONCURRENTLY orduithe CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Tá an nós imeachta cruthú innéacs seo difriúil ón ngnáthnós sa mhéid is nach dteastaíonn glas ar an tábla, agus mar sin ní chuireann sé bac ar oibríochtaí scríbhneoireachta. Ar an láimh eile, tógann sé níos mó ama agus ídíonn níos mó acmhainní.

Soláthraíonn Postgres go leor solúbthachta maidir le hinnéacsanna a chruthú agus bealaí chun déileáil le haon chásanna speisialta, chomh maith le bealaí chun an bunachar sonraí a bhainistiú ar eagla go bhfásfaidh d’iarratas go pléascach. Tá súil againn go gcabhróidh na leideanna seo leat do cheisteanna a fháil go tapa agus do bhunachar sonraí réidh de réir scála.

Foinse: will.com