{"id":34419,"date":"2019-10-31T21:58:13","date_gmt":"2019-10-31T18:58:13","guid":{"rendered":"https:\/\/prohoster.info\/blog\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql\/"},"modified":"2019-10-31T21:58:13","modified_gmt":"2019-10-31T18:58:13","slug":"ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","title":{"rendered":"Shfryt\u00ebzojm\u00eb t\u00eb gjitha mund\u00ebsit\u00eb e indekseve n\u00eb PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Shfryt\u00ebzojm\u00eb
\nN\u00eb bot\u00ebn e Postgres, indeks\u00ebt jan\u00eb jasht\u00ebzakonisht t\u00eb r\u00ebnd\u00ebsish\u00ebm p\u00ebr navigimin efikas n\u00eb ruajtjen e t\u00eb dh\u00ebnave (quhet \"heap\"). Postgres nuk mb\u00ebshtet klasterizimin p\u00ebr t\u00eb, dhe arkitektura MVCC b\u00ebn q\u00eb t\u00eb grumbullohen shum\u00eb versione t\u00eb t\u00eb nj\u00ebjtit tuple. Prandaj, \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme t\u00eb dihet si t\u00eb krijoni dhe menaxhoni indekset efikase p\u00ebr t\u00eb mb\u00ebshtetur aplikacionet.<\/p>\n

Para jush, po ofroj disa k\u00ebshilla p\u00ebr optimizimin dhe p\u00ebrmir\u00ebsimin e p\u00ebrdorimit t\u00eb indekseve.<\/p>\n

V\u00ebrejtje: k\u00ebrkesat e paraqitura m\u00eb posht\u00eb funksionojn\u00eb n\u00eb nj\u00eb shembuj t\u00eb pandryshuar t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave pagila<\/a><\/noindex>.<\/i>
\n<\/a><\/noindex><\/p>\n

P\u00ebrdorimi i indekseve mbuluese (Covering Indexes)<\/h3>\n

\nLe t\u00eb shqyrtojm\u00eb nj\u00eb k\u00ebrkes\u00eb p\u00ebr t\u00eb nxjerr\u00eb adresat e emailit p\u00ebr p\u00ebrdoruesit e pasivizuar. N\u00eb tabel\u00ebn customer<\/code> ka nj\u00eb kolon\u00eb active<\/code>, dhe k\u00ebrkesa rezulton t\u00eb jet\u00eb e thjesht\u00eb:<\/p>\n

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;\n                        PLAN K\u00cbRKESE\n-----------------------------------------------------------\n Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)\n   Filter: (active = 0)\n(2 rows)<\/code><\/pre>\n
\nN\u00eb k\u00ebrkes\u00eb thirret skanimi i plot\u00eb i tabel\u00ebs customer<\/code>. Le t\u00eb krijojm\u00eb nj\u00eb indeks p\u00ebr kolon\u00ebn active<\/code>:<\/p>\n
pagila=# KRIJO INDEX idx_cust1 N\u00cb customer(active);\nKRIJO INDEX\npagila=# SHPJEGO ZGJIDH email NGA customer KUJ aktiv=0;\n                                 PLANI I K\u00cbRKIMI\n-----------------------------------------------------------------------------\n Skano Indeksi duke p\u00ebrdorur idx_cust1 n\u00eb customer  (kostot=0.28..12.29 rreshta=15 gjer\u00ebsi=32)\n   Kushti i Indeksit: (aktiv = 0)\n(2 rreshta)<\/code><\/pre>\n
\nNdihmoi, skanimi i m\u00ebvonsh\u00ebm u kthye n\u00eb «skanimi i indeksit<\/code>«. Kjo do t\u00eb thot\u00eb se Postgres do t\u00eb skanoj\u00eb indeksin «idx_cust1<\/code>«, dhe m\u00eb pas do t\u00eb vazhdoj\u00eb t\u00eb k\u00ebrkoj\u00eb n\u00eb grumbullin e tabelave p\u00ebr t\u00eb lexuar vlerat e kolonave tjera (n\u00eb k\u00ebt\u00eb rast, kolon\u00ebn email<\/code>), q\u00eb shp\u00ebrndahet me k\u00ebrkes\u00ebn.<\/p>\n
N\u00eb PostgreSQL 11 jan\u00eb futur indekset mbuluese. Ato lejojn\u00eb q\u00eb t\u00eb p\u00ebrfshihen nj\u00eb ose disa kolona shtes\u00eb n\u00eb vet\u00eb indeksin \u2014 vlerat e tyre ruhen n\u00eb magazin\u00ebn e t\u00eb dh\u00ebnave t\u00eb indeksit.<\/p>\n
N\u00ebse do t\u00eb p\u00ebrdornim k\u00ebt\u00eb mund\u00ebsi dhe do t\u00eb shtonim vler\u00ebn e emailit brenda indeksit, at\u00ebher\u00eb Postgres do t\u00eb mos ket\u00eb nevoj\u00eb t\u00eb k\u00ebrkoj\u00eb n\u00eb grumbullin e tabel\u00ebs p\u00ebr vler\u00ebn email<\/code>. Le t\u00eb shohim n\u00ebse do t\u00eb funksionoj\u00eb:<\/p>\n
pagila=# KRIJO INDEX idx_cust2 N\u00cb customer(active) P\u00cbRFSHIN (email);\nKRIJO INDEX\npagila=# SHPJEGO ZGJIDH email NGA customer KUJ aktiv=0;\n                                    PLANI I K\u00cbRKIMI\n----------------------------------------------------------------------------------\n Skanimi i Indeksit vet\u00ebm duke p\u00ebrdorur idx_cust2 n\u00eb customer  (kostot=0.28..12.29 rreshta=15 gjer\u00ebsi=32)\n   Kushti i Indeksit: (aktiv = 0)\n(2 rreshta)<\/code><\/pre>\n
\n\u00abSkanimi i Indeksit vet\u00ebm<\/code>» na tregon se p\u00ebr k\u00ebrkes\u00ebn tani mjafton vet\u00ebm indeksi, duke ndihmuar n\u00eb evitimin e t\u00eb gjitha operacioneve t\u00eb hyrjes\/daljes p\u00ebr t\u00eb lexuar grumbullin e tabel\u00ebs.<\/p>\n
Sot, indekset mbuluese jan\u00eb t\u00eb disponueshme vet\u00ebm p\u00ebr pem\u00ebt B. Megjithat\u00eb, n\u00eb k\u00ebt\u00eb rast, p\u00ebrpjekjet p\u00ebr mir\u00ebmbajtjen do t\u00eb jen\u00eb m\u00eb t\u00eb larta.<\/p>\n
P\u00ebrdorimi i indikseve p\u00ebrdoruese<\/h3>\n
\nIndekset p\u00ebrdoruese indeksojn\u00eb vet\u00ebm nj\u00eb n\u00ebnngjyr\u00eb t\u00eb rreshtave t\u00eb tabel\u00ebs. Kjo kursen hap\u00ebsir\u00ebn e indeksit dhe p\u00ebrshpejton skanimin.<\/p>\n
Supozoni se duam t\u00eb marrim nj\u00eb list\u00eb t\u00eb adresave t\u00eb post\u00ebs elektronike t\u00eb klient\u00ebve tan\u00eb nga Kaliforni. K\u00ebrkesa do t\u00eb jet\u00eb:<\/p>\n
SELECT c.email FROM customer c\nJOIN address a ON c.address_id = a.address_id\nWHERE a.district = 'California';\nkurse ka nj\u00eb plan k\u00ebrkese q\u00eb p\u00ebrfshin skanimet e t\u00eb dy tabelave q\u00eb jan\u00eb t\u00eb bashkuara:\npagila=# EXPLAIN SELECT c.email FROM customer c\npagila-# JOIN address a ON c.address_id = a.address_id\npagila-# WHERE a.district = 'California';\n                              PLANI I K\u00cbRKES\u00cbS\n----------------------------------------------------------------------\n Hash Join  (kostot=15.65..32.22 rreshta=9 gjer\u00ebsi=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Sekuenciale Scan mbi klientin c  (kostot=0.00..14.99 rreshta=599 gjer\u00ebsi=34)\n   ->  Hash  (kostot=15.54..15.54 rreshta=9 gjer\u00ebsi=4)\n         ->  Sekuenciale Scan mbi adres\u00ebn a  (kostot=0.00..15.54 rreshta=9 gjer\u00ebsi=4)\n               Filtri: (distrikti = 'California'::tekst)\n(6 rreshta)<\/code><\/pre>\n
\n\u00c7far\u00eb do na japin indekset e zakonshme:<\/p>\n
pagila=# KRIJO INDIEVE idx_address1 NC\u00cb address(district);\nKRIJO INDIEVE\npagila=# EXPLORO ZGJIDH c.email NGA klient c\npagila-# BASHKO adres a NC\u00cb c.address_id = a.address_id\npagila-# KU a.district = 'California';\n                                      PLAN I K\u00cbRKIMIT\n---------------------------------------------------------------------------------------\n Bashkim Hash  (kostot=12.98..29.55 rreshta=9 gjer\u00ebsi=32)\n   Kushti Hash: (c.address_id = a.address_id)\n   ->  Skano Sekuencial n\u00eb klient c  (kostot=0.00..14.99 rreshta=599 gjer\u00ebsi=34)\n   ->  Hash  (kostot=12.87..12.87 rreshta=9 gjer\u00ebsi=4)\n         ->  Skano Bimbat Heap mbi adres\u00eb a  (kostot=4.34..12.87 rreshta=9 gjer\u00ebsi=4)\n               Rishiko Kushti: (distrikti = 'California'::teksti)\n               ->  Skano Indeksin e Bimbat mbi idx_address1  (kostot=0.00..4.34 rreshta=9 gjer\u00ebsi=0)\n                     Kushti i Indeksit: (distrikti = 'California'::teksti)\n(8 rreshta)<\/code><\/pre>\n
\nSkano adres\u00eb<\/code> u z\u00ebvend\u00ebsua me skanimin e indeksit idx_address1<\/code>, dhe pastaj u skanuar kupa adres\u00eb<\/code>.<\/p>\n
Meqen\u00ebse ky \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb e zakonshme dhe duhet optimizuar, mund t\u00eb p\u00ebrdorim nj\u00eb indeks t\u00eb pjessh\u00ebm q\u00eb indekson vet\u00ebm ato rreshta me adresat ku distriktohet \u2018California\u2019<\/code>:<\/p>\n
pagila=# KRIJO IND\u00cbX idx_address2 N\u00cb address(address_id) KU DISTRIGUAR='California';\nKRIJO IND\u00cbX\npagila=# SHPJEGIMI ZGJIDH c.email NGA bler\u00ebsi c\npagila-# BASHKOHU address a N\u00cb c.address_id = a.address_id\npagila-# KU a.district = 'California';\n                                           PLANI I PYETJES\n------------------------------------------------------------------------------------------------\n Bashkimi Hash  (kosto=12.38..28.96 rreshta=9 gjer\u00ebsi=32)\n   Kushti Hash: (c.address_id = a.address_id)\n   ->  Ska Scan n\u00eb bler\u00ebs c  (kosto=0.00..14.99 rreshta=599 gjer\u00ebsi=34)\n   ->  Hash  (kosto=12.27..12.27 rreshta=9 gjer\u00ebsi=4)\n         ->  Skano vet\u00ebm me indeks duke p\u00ebrdorur idx_address2 n\u00eb address a  (kosto=0.14..12.27 rreshta=9 gjer\u00ebsi=4)\n(5 rreshta)<\/code><\/pre>\n
\nTani k\u00ebrkesa lexon vet\u00ebm idx_address2<\/code> dhe nuk prekur tabel\u00ebn adres\u00eb<\/code>.<\/p>\n
P\u00ebrdorimi i indekseve me shum\u00eb vlera (Multi-Value Indexes)<\/h3>\n
\nDisa kolona q\u00eb duhet t\u00eb indeksohen mund t\u00eb mos p\u00ebrmbajn\u00eb nj\u00eb tip t\u00eb dh\u00ebnash skalar. Tipet e kolonave si jsonb<\/code>, arrays<\/code> dhe tsvector<\/code> mund t\u00eb p\u00ebrmbajn\u00eb vlera p\u00ebrb\u00ebr\u00ebse ose shum\u00ebfish. N\u00ebse duhet t\u00eb indeksoni k\u00ebto kolona, zakonisht duhet t\u00eb k\u00ebrkoni p\u00ebr t\u00eb gjitha vlerat e ve\u00e7anta n\u00eb k\u00ebto kolona.<\/p>\n
Le t\u00eb provojm\u00eb t\u00eb gjejm\u00eb titujt e t\u00eb gjitha filmave q\u00eb p\u00ebrmbajn\u00eb prerje nga dubllet e d\u00ebshtuar. N\u00eb tabel\u00ebn film<\/code> ka nj\u00eb kolon\u00eb tekstuale t\u00eb quajtur special_features<\/code>. N\u00ebse filmi ka k\u00ebt\u00eb \"ve\u00e7ori speciale\", at\u00ebher\u00eb n\u00eb kolon\u00eb p\u00ebrmban nj\u00eb element n\u00eb form\u00eb t\u00eb nj\u00eb varg tekstual Pas Scene<\/code>. P\u00ebr t\u00eb gjetur t\u00eb gjitha k\u00ebto filma, duhet t\u00eb zgjedhim t\u00eb gjitha rreshtat me \u00abPrapa skenave\u00bb n\u00eb \u00e7do <\/b>vler\u00eb t\u00eb array-t special_features<\/code>:<\/p>\n
SELECT title FROM film WHERE special_features @> '{\"Behind The Scenes\"}';<\/code><\/pre>\n
\nOperatori i p\u00ebrfshirjes (containment operator) @><\/code> kontrollon n\u00ebse ana e djatht\u00eb \u00ebsht\u00eb nj\u00eb n\u00ebngrup i an\u00ebs s\u00eb majt\u00eb.<\/p>\n
Plani i k\u00ebrkes\u00ebs:<\/p>\n
pagila=# EXPLAIN SELECT title FROM film\npagila-# WHERE special_features @> '{\"Behind The Scenes\"}';\n                           QUERY PLAN\n-----------------------------------------------------------------\n Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)\n   Filter: (special_features @> '{\"Behind The Scenes\"}'::text[])\n(2 rows)<\/code><\/pre>\n
\nI cili k\u00ebrkon nj\u00eb skanim t\u00eb plot\u00eb me kosto 67.<\/p>\n
T\u00eb shohim n\u00ebse na ndihmon nj\u00eb indeks i zakonsh\u00ebm B-tree:<\/p>\n
pagila=# CREATE INDEX idx_film1 ON film(special_features);\nCREATE INDEX\npagila=# EXPLAIN SELECT title FROM film\npagila-# WHERE special_features @> '{\"Behind The Scenes\"}';\n                           QUERY PLAN\n-----------------------------------------------------------------\n Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)\n   Filter: (special_features @> '{\"Behind The Scenes\"}'::text[])\n(2 rows)<\/code><\/pre>\n
\nIndeksi nuk u mor n\u00eb konsiderat\u00eb. Indeksi B-tree nuk \u00ebsht\u00eb i vet\u00ebdijsh\u00ebm p\u00ebr ekzistenc\u00ebn e elementeve t\u00eb ve\u00e7anta n\u00eb vlerat e indeksuara.<\/p>\n
Na nevojitet nj\u00eb indeks GIN.<\/p>\n
pagila=# KRIJO INDHEKSIN idx_film2 N\u00cb film P\u00cbRDORIN GIN(special_features);\nKRIJO INDHEKS\npagila=# EXPLAIN ZGJIDH TITULLIN NGA film\npagila-# KU EQ SPECIAL_FEATURES @> '{\"Pas Skeneve\"}';\n                                PLANI I KERKES\u00cbS\n---------------------------------------------------------------------------\n Skana e Heap-it me Bitmap n\u00eb film  (kosto=8.04..23.58 rreshta=5 gjer\u00ebsi=15)\n   Kontrolli i Rihapjes: (special_features @> '{\"Pas Skeneve\"}'::tekst[])\n   ->  Skana e Indeksit me Bitmap n\u00eb idx_film2  (kosto=0.00..8.04 rreshta=5 gjer\u00ebsi=0)\n         Kushti i Indeksit: (special_features @> '{\"Pas Skeneve\"}'::tekst[])\n(4 rreshta)<\/code><\/pre>\n
\nIndeksi GIN mb\u00ebshtet p\u00ebrshtatjen e vlerave t\u00eb ve\u00e7anta me vlerat e p\u00ebrmbledhura t\u00eb indeksuara, si rezultat, kostoja e planit t\u00eb k\u00ebrkes\u00ebs do t\u00eb zvog\u00eblohet m\u00eb shum\u00eb se dyfish.<\/p>\n
Shk\u00ebputemi nga p\u00ebrs\u00ebritja e indekseve<\/h3>\n
\nIndeksat grumbullohen me kalimin e koh\u00ebs, dhe ndonj\u00ebher\u00eb indeksi i ri mund t\u00eb ket\u00eb t\u00eb nj\u00ebjtin p\u00ebrkufizim si nj\u00eb nga t\u00eb kaluarit. P\u00ebr definicionet SQL t\u00eb leht\u00ebsuara p\u00ebr njeriun, mund t\u00eb p\u00ebrdorni pamjen katalogjike pg_indexes<\/code>. Ju gjithashtu do t\u00eb mund t\u00eb gjeni leht\u00ebsisht definicione t\u00eb nj\u00ebjta:<\/p>\n
 Zgjidhni array_agg(indexname) SI indekset, z\u00ebvend\u00ebsoni(indexdef, indexname, '') SI defn\n    NGA pg_indexes\nGRUPI NGA defn\n  KRAHASO numri(*) > 1;\nDhe ja rezultati kur ekzekutohet n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave pagila:\npagila=#   Zgjidhni array_agg(indexname) SI indekset, z\u00ebvend\u00ebsoni(indexdef, indexname, '') SI defn\npagila-#     NGA pg_indexes\npagila-# GRUPI NGA defn\npagila-#   KRAHASO numri(*) > 1;\n                                indekset                                 |                                defn\n------------------------------------------------------------------------+------------------------------------------------------------------\n {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_01 DHE btree (customer_id\n {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_02 DHE btree (customer_id\n {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_03 DHE btree (customer_id\n {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_04 DHE btree (customer_id\n {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_05 DHE btree (customer_id\n {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | KRIJO IND\u00cbKSI  N\u00cb public.payment_p2017_06 DHE btree (customer_id\n(6 radh\u00eb)\n<\/code><\/pre>\n
<\/p>\n
Indekset e mbi-grupeve (Superset Indexes)<\/h3>\n
\nMund t\u00eb ndodh\u00eb q\u00eb t\u00eb keni shum\u00eb indekse, nj\u00ebri prej t\u00eb cil\u00ebve indekson nj\u00eb n\u00ebnshum\u00eb kolonash q\u00eb indeksojn\u00eb indekse t\u00eb tjera. Kjo mund t\u00eb jet\u00eb e d\u00ebshiruashme, por gjithashtu edhe jo \u2014 n\u00ebnshuma mund t\u00eb \u00e7oj\u00eb n\u00eb skanimin vet\u00ebm p\u00ebrmes indekseve, q\u00eb \u00ebsht\u00eb mir\u00eb, por n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb mund t\u00eb p\u00ebrdor\u00eb shum\u00eb hap\u00ebsir\u00eb, ose k\u00ebrkesa p\u00ebr optimizimin e s\u00eb cil\u00ebs u krijua kjo n\u00ebnshum\u00eb, tashm\u00eb nuk p\u00ebrdoret m\u00eb.<\/p>\n
N\u00ebse keni nevoj\u00eb t\u00eb automatizoni identifikimin e k\u00ebtyre indekseve, mund t\u00eb filloni nga pg_index<\/a><\/noindex> nga tabela pg_catalog<\/code>.<\/p>\n
Indekset e pap\u00ebrdorura<\/h3>\n
\nMe zhvillimin e aplikacioneve q\u00eb p\u00ebrdorin bazat e t\u00eb dh\u00ebnave, zhvillohen gjithashtu k\u00ebrkesat q\u00eb ato p\u00ebrdorin. Indekset e shtuar m\u00eb par\u00eb mund t\u00eb mos aplikohen m\u00eb nga asnj\u00eb k\u00ebrkes\u00eb. N\u00eb \u00e7do skanimin e indeksit, ai sh\u00ebnohet nga menaxheri i statistikave, dhe n\u00eb pamjen e katalogut sistemor pg_stat_user_indexes<\/code> mund t\u00eb shikohet vlera idx_scan<\/code>, e cila \u00ebsht\u00eb nj\u00eb num\u00ebrues akumulues. Ndjekja e k\u00ebsaj vler\u00eb p\u00ebr nj\u00eb periudh\u00eb kohore (t\u00eb themi, nj\u00eb muaj) do t'ju jap\u00eb nj\u00eb ide t\u00eb mir\u00eb se cilat indekse nuk p\u00ebrdoren dhe mund t\u00eb hiqen.<\/p>\n
Kjo \u00ebsht\u00eb nj\u00eb k\u00ebrkes\u00eb p\u00ebr t\u00eb marr\u00eb numrat aktual\u00eb t\u00eb skanimeve t\u00eb t\u00eb gjitha indekseve n\u00eb skem\u00ebn \u2018public\u2019<\/code>:<\/p>\n
Zgjidhni relname, indexrelname, idx_scan\nNga   pg_catalog.pg_stat_user_indexes\nKujdes  schemaname = 'public';\nme daljen si kjo:\npagila=# Zgjidhni relname, indexrelname, idx_scan\npagila-# Nga   pg_catalog.pg_stat_user_indexes\npagila-# Ku  schemaname = 'public'\npagila-# LIMIT  10;\n    relname    |    indexrelname    | idx_scan\n---------------+--------------------+----------\n customer      | customer_pkey      |    32093\n actor         | actor_pkey         |     5462\n address       | address_pkey       |      660\n category      | category_pkey      |     1000\n city          | city_pkey          |      609\n country       | country_pkey       |      604\n film_actor    | film_actor_pkey    |        0\n film_category | film_category_pkey |        0\n film          | film_pkey          |    11043\n inventory     | inventory_pkey     |    16048\n(10 rreshta)<\/code><\/pre>\n
<\/p>\n
Rikrijimi i indekseve me m\u00eb pak bllokime<\/h3>\n
\nShpesh indekset duhet t\u00eb rikrijohen, p\u00ebr shembull, kur ato fryhen n\u00eb p\u00ebrmasa, dhe rikrijimi mund t\u00eb p\u00ebrshpejtoj\u00eb skanimin. Gjithashtu indekset mund t\u00eb d\u00ebmtohen. Ndryshimi i parametrave t\u00eb indeksit gjithashtu mund t\u00eb k\u00ebrkoj\u00eb rikrijimin e tij.<\/p>\n
Aktivizojm\u00eb krijimin paralel t\u00eb indekseve<\/h3>\n
\nN\u00eb PostgreSQL 11, krijimi i indeksit B-Tree \u00ebsht\u00eb konkurent. P\u00ebr t\u00eb shpejtuar procesin e krijimit, mund t\u00eb p\u00ebrdoren disa pun\u00ebtor\u00eb q\u00eb punojn\u00eb paralelisht. Sigurohuni q\u00eb k\u00ebto parametra konfigurimi t\u00eb jen\u00eb caktuar n\u00eb m\u00ebnyr\u00eb t\u00eb sakt\u00eb:<\/p>\n
SET max_parallel_workers = 32;\nSET max_parallel_maintenance_workers = 16;<\/code><\/pre>\n
\nVlerat e paracaktuar jan\u00eb shum\u00eb t\u00eb vogla. N\u00eb m\u00ebnyr\u00eb ideale, k\u00ebto numra duhet t\u00eb rriten me numrin e b\u00ebrthamave t\u00eb procesorit. Lexoni m\u00eb shum\u00eb n\u00eb dokumentacion<\/a><\/noindex>.<\/p>\n
Krijimi i indekseve n\u00eb sfond<\/h3>\n
\nMund t\u00eb krijoni nj\u00eb indeks n\u00eb sfond duke p\u00ebrdorur parametrin CONCURRENTLY<\/code> komandat CREATE INDEX<\/code>:<\/p>\n
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);\nCREATE INDEX<\/code><\/pre>\n
Kjo procedur\u00eb e krijimit t\u00eb indeksit \u00ebsht\u00eb e ndryshme nga e zakonshmja, pasi nuk k\u00ebrkon bllokimin e tabel\u00ebs dhe, p\u00ebr rrjedhoj\u00eb, nuk bllokon operacionet e shkruar. Nga ana tjet\u00ebr, ajo merr m\u00eb shum\u00eb koh\u00eb dhe konsumon m\u00eb shum\u00eb burime.<\/p>\n
Postgres ofron shum\u00eb mund\u00ebsi fleksibile p\u00ebr krijimin e indekseve dhe rrug\u00ebve t\u00eb zgjidhjes p\u00ebr \u00e7do rast t\u00eb ve\u00e7ant\u00eb, si dhe ofron m\u00ebnyra p\u00ebr menaxhimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb rast t\u00eb rritjes explosive t\u00eb aplikacionit tuaj. Shpresojm\u00eb q\u00eb k\u00ebto k\u00ebshilla do t'ju ndihmojn\u00eb t\u00eb b\u00ebni k\u00ebrkesat t\u00eb shpejta dhe baz\u00ebn e t\u00eb dh\u00ebnave t\u00eb gatshme p\u00ebr t'u shkall\u00ebzuar.
\n
Burimi: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u0412 \u043c\u0438\u0440\u0435 Postgres \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043a\u0440\u0430\u0439\u043d\u0435 \u0432\u0430\u0436\u043d\u044b \u0434\u043b\u044f \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0439 \u043d\u0430\u0432\u0438\u0433\u0430\u0446\u0438\u0438 \u043f\u043e \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0443 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 (\u0435\u0433\u043e \u043d\u0430\u0437\u044b\u0432\u0430\u044e\u0442 \u00ab\u043a\u0443\u0447\u0430\u00bb, heap). Postgres \u043d\u0435 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442 \u0434\u043b\u044f \u043d\u0435\u0433\u043e \u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0438\u0437\u0430\u0446\u0438\u044e, \u0438 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0430 MVCC \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442 \u043a \u0442\u043e\u043c\u0443, \u0447\u0442\u043e \u0443 \u0432\u0430\u0441 \u043d\u0430\u043a\u0430\u043f\u043b\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u043c\u043d\u043e\u0433\u043e \u0432\u0435\u0440\u0441\u0438\u0439 \u043e\u0434\u043d\u043e\u0433\u043e \u0438 \u0442\u043e\u0433\u043e \u0436\u0435 \u043a\u043e\u0440\u0442\u0435\u0436\u0430. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u043e\u0447\u0435\u043d\u044c \u0432\u0430\u0436\u043d\u043e \u0443\u043c\u0435\u0442\u044c \u0441\u043e\u0437\u0434\u0430\u0432\u0430\u0442\u044c \u0438 \u0441\u043e\u043f\u0440\u043e\u0432\u043e\u0436\u0434\u0430\u0442\u044c \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u044b\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u0434\u043b\u044f \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0439. \u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u0432\u0430\u0448\u0435\u043c\u0443 \u0432\u043d\u0438\u043c\u0430\u043d\u0438\u044e […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":25931,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-34419","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Shfryt\u00ebzojm\u00eb t\u00eb gjitha mund\u00ebsit\u00eb e indekseve n\u00eb PostgreSQL | ProHoster","description":"N\u00eb bot\u00ebn e Postgres, indekset jan\u00eb jasht\u00ebzakonisht t\u00eb r\u00ebnd\u00ebsishme p\u00ebr navigimin efikas n\u00eb ruajtjen e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave (e njohur si \u00abheap\u00bb). Postgres nuk mb\u00ebshtet grumbullimin p\u00ebr t\u00eb, dhe arkitektura MVCC b\u00ebn q\u00eb t\u00eb keni shum\u00eb versione t\u00eb nj\u00ebjtit tuple. Prandaj, \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme t\u00eb jeni n\u00eb gjendje t\u00eb krijoni dhe t\u00eb mbani indekse efikase p\u00ebr t\u00eb mb\u00ebshtetur aplikacionet. Ju paraqes","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0418\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c \u0432\u0441\u0435 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0432 PostgreSQL | ProHoster","og:description":"\u0412 \u043c\u0438\u0440\u0435 Postgres \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u043a\u0440\u0430\u0439\u043d\u0435 \u0432\u0430\u0436\u043d\u044b \u0434\u043b\u044f \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0439 \u043d\u0430\u0432\u0438\u0433\u0430\u0446\u0438\u0438 \u043f\u043e \u0445\u0440\u0430\u043d\u0438\u043b\u0438\u0449\u0443 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 (\u0435\u0433\u043e \u043d\u0430\u0437\u044b\u0432\u0430\u044e\u0442 \u00ab\u043a\u0443\u0447\u0430\u00bb, heap). Postgres \u043d\u0435 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442 \u0434\u043b\u044f \u043d\u0435\u0433\u043e \u043a\u043b\u0430\u0441\u0442\u0435\u0440\u0438\u0437\u0430\u0446\u0438\u044e, \u0438 \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u0430 MVCC \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442 \u043a \u0442\u043e\u043c\u0443, \u0447\u0442\u043e \u0443 \u0432\u0430\u0441 \u043d\u0430\u043a\u0430\u043f\u043b\u0438\u0432\u0430\u0435\u0442\u0441\u044f \u043c\u043d\u043e\u0433\u043e \u0432\u0435\u0440\u0441\u0438\u0439 \u043e\u0434\u043d\u043e\u0433\u043e \u0438 \u0442\u043e\u0433\u043e \u0436\u0435 \u043a\u043e\u0440\u0442\u0435\u0436\u0430. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u043e\u0447\u0435\u043d\u044c \u0432\u0430\u0436\u043d\u043e \u0443\u043c\u0435\u0442\u044c \u0441\u043e\u0437\u0434\u0430\u0432\u0430\u0442\u044c \u0438 \u0441\u043e\u043f\u0440\u043e\u0432\u043e\u0436\u0434\u0430\u0442\u044c \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u044b\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b \u0434\u043b\u044f \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u043a\u0438 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0439. \u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u0432\u0430\u0448\u0435\u043c\u0443 \u0432\u043d\u0438\u043c\u0430\u043d\u0438\u044e","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:58:13+00:00","article:modified_time":"2019-10-31T18:58:13+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"34419","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 19:13:20","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:52:22","updated":"2026-01-21 19:13:20","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/34419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=34419"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/34419\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/25931"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=34419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=34419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=34419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}