{"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\/cs\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","title":{"rendered":"Vyu\u017e\u00edv\u00e1me v\u0161echny mo\u017enosti index\u016f v PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Vyu\u017e\u00edv\u00e1me
\nVe sv\u011bt\u011b Postgres jsou indexy mimo\u0159\u00e1dn\u011b d\u016fle\u017eit\u00e9 pro efektivn\u00ed navigaci v datab\u00e1zov\u00e9m \u00falo\u017ei\u0161ti (naz\u00fdv\u00e1 se \u201ehromada\u201c, heap). Postgres nepodporuje jeho klasifikaci a architektura MVCC zp\u016fsobuje, \u017ee se hromad\u00ed mnoho verz\u00ed stejn\u00e9ho tuple. Proto je velmi d\u016fle\u017eit\u00e9 um\u011bt vytv\u00e1\u0159et a udr\u017eovat efektivn\u00ed indexy pro podporu aplikac\u00ed.<\/p>\n

P\u0159edkl\u00e1d\u00e1m v\u00e1m n\u011bkolik tip\u016f na optimalizaci a zlep\u0161en\u00ed vyu\u017eit\u00ed index\u016f.<\/p>\n

Pozn\u00e1mka: N\u00ed\u017ee uveden\u00e9 dotazy funguj\u00ed na nemodifikovan\u00e9m vzorku datab\u00e1ze pagila<\/a><\/noindex>.<\/i>
\n<\/a><\/noindex><\/p>\n

Vyu\u017eit\u00ed pokr\u00fdvaj\u00edc\u00edch index\u016f (Covering Indexes)<\/h3>\n

\nPod\u00edvejme se na dotaz pro z\u00edsk\u00e1n\u00ed e-mailov\u00fdch adres neaktivn\u00edch u\u017eivatel\u016f. V tabulce customer<\/code> je sloupec aktivn\u00ed<\/code>, a dotaz je pom\u011brn\u011b jednoduch\u00fd:<\/p>\n

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;\n                        QUERY PLAN\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
\nV dotazu se prov\u00e1d\u00ed kompletn\u00ed sekven\u010dn\u00ed skenov\u00e1n\u00ed tabulky. customer<\/code>Poj\u010fme vytvo\u0159it index pro sloupec aktivn\u00ed<\/code>:<\/p>\n
pagila=# CREATE INDEX idx_cust1 ON customer(active);\nCREATE INDEX\npagila=# EXPLAIN SELECT email FROM customer WHERE active=0;\n                                 QUERY PLAN\n-----------------------------------------------------------------------------\n Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)\n   Index Cond: (active = 0)\n(2 rows)<\/code><\/pre>\n
\nPomohlo, n\u00e1sleduj\u00edc\u00ed skenov\u00e1n\u00ed se stalo «index scan<\/code>«. To znamen\u00e1, \u017ee Postgres proskenuje index «idx_cust1<\/code>«, a pot\u00e9 bude pokra\u010dovat v hled\u00e1n\u00ed ve hromad\u011b tabulky, aby p\u0159e\u010detl hodnoty dal\u0161\u00edch sloupc\u016f (v tomto p\u0159\u00edpad\u011b sloupec email<\/code>), kter\u00e9 jsou pro dotaz pot\u0159eba.<\/p>\n
V PostgreSQL 11 byly zavedeny pokr\u00fdvaj\u00edc\u00ed indexy. Umo\u017e\u0148uj\u00ed zahrnout do samotn\u00e9ho indexu jeden nebo v\u00edce dodate\u010dn\u00fdch sloupc\u016f \u2014 jejich hodnoty jsou ulo\u017een\u00e9 v \u00falo\u017ei\u0161ti dat indexu.<\/p>\n
Pokud bychom tuto mo\u017enost vyu\u017eili a p\u0159idali hodnotu e-mailu do indexu, Postgres by nemusel hledat v hromad\u011b tabulky hodnotu email<\/code>. Pod\u00edvejme se, jestli to bude fungovat:<\/p>\n
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);\nCREATE INDEX\npagila=# EXPLAIN SELECT email FROM customer WHERE active=0;\n                                    QUERY PLAN\n----------------------------------------------------------------------------------\n Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)\n   Index Cond: (active = 0)\n(2 rows)<\/code><\/pre>\n
\n\u201eIndex Only Scan<\/code>» n\u00e1m \u0159\u00edk\u00e1, \u017ee dotazu nyn\u00ed sta\u010d\u00ed pouze jeden index, co\u017e pom\u00e1h\u00e1 vyhnout se v\u0161em diskov\u00fdm vstupn\u011b\/v\u00fdstupn\u00edm operac\u00edm pro \u010dten\u00ed hromady tabulky.<\/p>\n
V sou\u010dasnosti jsou indexy pokryt\u00ed dostupn\u00e9 pouze pro B-stromy. Nicm\u00e9n\u011b v tomto p\u0159\u00edpad\u011b budou n\u00e1klady na \u00fadr\u017ebu vy\u0161\u0161\u00ed.<\/p>\n
Pou\u017eit\u00ed \u010d\u00e1ste\u010dn\u00fdch index\u016f<\/h3>\n
\n\u010c\u00e1ste\u010dn\u00e9 indexy indexuj\u00ed pouze podmno\u017einu \u0159\u00e1dk\u016f tabulky. To pom\u00e1h\u00e1 \u0161et\u0159it velikost index\u016f a zrychluje skenov\u00e1n\u00ed.<\/p>\n
P\u0159edstavme si, \u017ee pot\u0159ebujeme z\u00edskat seznam e-mailov\u00fdch adres na\u0161ich klient\u016f z Kalifornie. Dotaz by byl n\u00e1sleduj\u00edc\u00ed:<\/p>\n
SELECT c.email FROM customer c\nJOIN address a ON c.address_id = a.address_id\nWHERE a.district = 'California';\nco\u017e m\u00e1 pl\u00e1n dotazu, kter\u00fd zahrnuje skenov\u00e1n\u00ed obou tabulek:\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                              PL\u00c1N DOTAZU\n----------------------------------------------------------------------\n Hash Join  (n\u00e1klady=15.65..32.22 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan na customer c  (n\u00e1klady=0.00..14.99 \u0159\u00e1dk\u016f=599 \u0161\u00ed\u0159ka=34)\n   ->  Hash  (n\u00e1klady=15.54..15.54 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n         ->  Seq Scan na address a  (n\u00e1klady=0.00..15.54 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n               Filtr: (district = 'California'::text)\n(6 \u0159\u00e1dk\u016f)<\/code><\/pre>\n
\nCo n\u00e1m daj\u00ed b\u011b\u017en\u00e9 indexy:<\/p>\n
pagila=# CREATE INDEX idx_address1 ON address(district);\nVYTVO\u0158IT INDEX\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                                      PL\u00c1N DOTAZU\n---------------------------------------------------------------------------------------\n Hash Join  (n\u00e1klady=12.98..29.55 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan na customer c  (n\u00e1klady=0.00..14.99 \u0159\u00e1dk\u016f=599 \u0161\u00ed\u0159ka=34)\n   ->  Hash  (n\u00e1klady=12.87..12.87 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n         ->  Bitmap Heap Scan na address a  (n\u00e1klady=4.34..12.87 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n               Znovu kontrolovat podm\u00ednku: (district = 'California'::text)\n               ->  Bitmap Index Scan na idx_address1  (n\u00e1klady=0.00..4.34 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=0)\n                     Index Cond: (district = 'California'::text)\n(8 \u0159\u00e1dk\u016f)<\/code><\/pre>\n
\nSkenov\u00e1n\u00ed address<\/code> bylo nahrazeno skenov\u00e1n\u00edm indexu idx_address1<\/code>, a pot\u00e9 byla skenov\u00e1na hromada address<\/code>.<\/p>\n
Jeliko\u017e se jedn\u00e1 o \u010dast\u00fd dotaz a je nutn\u00e9 jej optimalizovat, m\u016f\u017eeme pou\u017e\u00edt \u010d\u00e1ste\u010dn\u00fd index, kter\u00fd indexuje pouze ty \u0159\u00e1dky s adresami, kde okres \u2018California\u2019<\/code>:<\/p>\n
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';\nVYTVO\u0158IT INDEX\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                                           PL\u00c1N DOTAZU\n------------------------------------------------------------------------------------------------\n Hash Join  (n\u00e1klady=12.38..28.96 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan na customer c  (n\u00e1klady=0.00..14.99 \u0159\u00e1dk\u016f=599 \u0161\u00ed\u0159ka=34)\n   ->  Hash  (n\u00e1klady=12.27..12.27 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n         ->  Index Only Scan pou\u017e\u00edvaj\u00edc\u00ed idx_address2 na address a  (n\u00e1klady=0.14..12.27 \u0159\u00e1dk\u016f=9 \u0161\u00ed\u0159ka=4)\n(5 \u0159\u00e1dk\u016f)<\/code><\/pre>\n
\nNyn\u00ed dotaz \u010dte pouze idx_address2<\/code> a nezasahuje do tabulky address<\/code>.<\/p>\n
Pou\u017eit\u00ed v\u00edcerozm\u011brn\u00fdch index\u016f (Multi-Value Indexes)<\/h3>\n
\nN\u011bkter\u00e9 sloupce, kter\u00e9 je t\u0159eba indexovat, nemus\u00ed obsahovat skal\u00e1rn\u00ed datov\u00e9 typy. Typy sloupc\u016f, jako jsou jsonb<\/code>, pole<\/code> a tsvector<\/code> mohou obsahovat slo\u017een\u00e9 nebo v\u00edcerozm\u011brn\u00e9 hodnoty. Pokud pot\u0159ebujete indexovat takov\u00e9 sloupce, obvykle je nutn\u00e9 proch\u00e1zet jednotliv\u00e9 hodnoty v t\u011bchto sloupc\u00edch.<\/p>\n
Zkus\u00edme naj\u00edt n\u00e1zvy v\u0161ech film\u016f, kter\u00e9 obsahuj\u00ed uk\u00e1zky z ne\u00fasp\u011b\u0161n\u00fdch dabing\u016f. V tabulce film<\/code> je textov\u00fd sloupec nazvan\u00fd special_features<\/code>. Pokud m\u00e1 film toto \u201especi\u00e1ln\u00ed vlastnost\u201c, obsahuje sloupec prvek ve form\u011b textov\u00e9ho pole Behind The Scenes<\/code>. Pro vyhled\u00e1n\u00ed v\u0161ech t\u011bchto film\u016f pot\u0159ebujeme vybrat v\u0161echny \u0159\u00e1dky s \u201eBehind The Scenes\u201c p\u0159i jak\u00fdchkoliv <\/b>hodnot\u00e1ch pole special_features<\/code>:<\/p>\n
SELECT title FROM film WHERE special_features @> '{\"Behind The Scenes\"}';<\/code><\/pre>\n
\nOper\u00e1tor zapln\u011bn\u00ed (containment operator) @><\/code> ov\u011b\u0159uje, zda je prav\u00e1 \u010d\u00e1st podmno\u017einou lev\u00e9 \u010d\u00e1sti.<\/p>\n
Pl\u00e1n dotazu:<\/p>\n
pagila=# EXPLAIN SELECT title FROM film\npagila-# WHERE special_features @> '{\"Behind The Scenes\"}';\n                           DOTAZOV\u00dd PL\u00c1N\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
\nKter\u00fd vy\u017eaduje pln\u00e9 skenov\u00e1n\u00ed hromady s cenou 67.<\/p>\n
Pod\u00edv\u00e1me se, zda n\u00e1m pom\u016f\u017ee b\u011b\u017en\u00fd index B-stromu:<\/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                           DOTAZOV\u00dd PL\u00c1N\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
\nIndex nebyl ani zva\u017eov\u00e1n. Index B-stromu nezjist\u00ed existenci jednotliv\u00fdch prvk\u016f ve indexovan\u00fdch hodnot\u00e1ch.<\/p>\n
Pot\u0159ebujeme GIN-index.<\/p>\n
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);\nCREATE INDEX\npagila=# EXPLAIN SELECT title FROM film\npagila-# WHERE special_features @> '{\"Behind The Scenes\"}';\n                                DOTAZOV\u00dd PL\u00c1N\n---------------------------------------------------------------------------\n Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)\n   Recheck Cond: (special_features @> '{\"Behind The Scenes\"}'::text[])\n   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)\n         Index Cond: (special_features @> '{\"Behind The Scenes\"}'::text[])\n(4 rows)<\/code><\/pre>\n
\nGIN-index podporuje porovn\u00e1v\u00e1n\u00ed jednotliv\u00fdch hodnot s indexovan\u00fdmi slo\u017een\u00fdmi hodnotami, co\u017e vedlo k poklesu ceny dotazov\u00e9ho pl\u00e1nu v\u00edce ne\u017e na polovinu.<\/p>\n
Zbavujeme se duplikace index\u016f<\/h3>\n
\nIndexy se s \u010dasem hromad\u00ed a n\u011bkdy nov\u00fd index m\u016f\u017ee obsahovat stejn\u00e9 definice jako jeden z p\u0159edchoz\u00edch. Pro \u010diteln\u011bj\u0161\u00ed SQL definice index\u016f lze pou\u017e\u00edt katalogov\u00e9 zobrazen\u00ed. pg_indexes<\/code>. Budete tak\u00e9 moci snadno naj\u00edt shodn\u00e9 definice:<\/p>\n
 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn\n    FROM pg_indexes\nGROUP BY defn\n  HAVING count(*) > 1;\nA zde je v\u00fdsledek p\u0159i proveden\u00ed na z\u00e1kladn\u00ed datab\u00e1zi pagila:\npagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn\npagila-#     FROM pg_indexes\npagila-# GROUP BY defn\npagila-#   HAVING count(*) > 1;\n                                indexes                                 |                                defn\n------------------------------------------------------------------------+------------------------------------------------------------------\n {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id\n {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id\n {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id\n {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id\n {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id\n {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id\n(6 rows)\n<\/code><\/pre>\n
<\/p>\n
Indexy nadmno\u017estv\u00ed (Superset Indexes)<\/h3>\n
\nM\u016f\u017ee se st\u00e1t, \u017ee se v\u00e1m nahromad\u00ed mnoho index\u016f, z nich\u017e jeden indexuje nadmno\u017estv\u00ed sloupc\u016f, kter\u00e9 indexuj\u00ed dal\u0161\u00ed indexy. To m\u016f\u017ee b\u00fdt \u017e\u00e1douc\u00ed, ale tak\u00e9 ne \u2014 nadmno\u017estv\u00ed m\u016f\u017ee v\u00e9st ke skenov\u00e1n\u00ed pouze p\u0159es indexy, co\u017e je dobr\u00e9, ale tak\u00e9 m\u016f\u017ee zab\u00edrat p\u0159\u00edli\u0161 mnoho m\u00edsta, nebo dotaz, pro jeho\u017e optimalizaci bylo toto nadmno\u017estv\u00ed ur\u010deno, u\u017e nemus\u00ed b\u00fdt pou\u017e\u00edv\u00e1n.<\/p>\n
Pokud pot\u0159ebujete automatizovat ur\u010den\u00ed takov\u00fdch index\u016f, m\u016f\u017eete za\u010d\u00edt s pg_index<\/a><\/noindex> z tabulky pg_catalog<\/code>.<\/p>\n
Nepou\u017e\u00edvan\u00e9 indexy<\/h3>\n
\nJak se vyv\u00edjej\u00ed aplikace, kter\u00e9 pou\u017e\u00edvaj\u00ed datab\u00e1ze, vyv\u00edjej\u00ed se tak\u00e9 dotazy, kter\u00e9 pou\u017e\u00edvaj\u00ed. D\u0159\u00edve p\u0159idan\u00e9 indexy mohou b\u00fdt nyn\u00ed pou\u017eity \u017e\u00e1dn\u00fdm dotazem. P\u0159i ka\u017ed\u00e9m skenov\u00e1n\u00ed indexu jej ozna\u010duje spr\u00e1vce statistik a ve zobrazen\u00ed syst\u00e9mov\u00e9ho katalogu pg_stat_user_indexes<\/code> m\u016f\u017eete zkontrolovat hodnotu idx_scan<\/code>, kter\u00fd je akumula\u010dn\u00edm po\u010d\u00edtadlem. Sledov\u00e1n\u00ed t\u00e9to hodnoty za ur\u010dit\u00e9 \u010dasov\u00e9 obdob\u00ed (\u0159ekn\u011bme m\u011bs\u00edc) poskytne dobr\u00fd p\u0159ehled o tom, kter\u00e9 indexy nejsou pou\u017e\u00edvan\u00e9 a mohou b\u00fdt odstran\u011bny.<\/p>\n
Zde je dotaz na z\u00edsk\u00e1n\u00ed aktu\u00e1ln\u00edch po\u010d\u00edtadel skenov\u00e1n\u00ed v\u0161ech index\u016f ve sch\u00e9matu 'public'<\/code>:<\/p>\n
SELECT relname, indexrelname, idx_scan\nFROM   pg_catalog.pg_stat_user_indexes\nWHERE  schemaname = 'public';\n s v\u00fdstupem jako je tento:\npagila=# SELECT relname, indexrelname, idx_scan\npagila-# FROM   pg_catalog.pg_stat_user_indexes\npagila-# WHERE  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 \u0159\u00e1dk\u016f)<\/code><\/pre>\n
<\/p>\n
P\u0159ecreation index\u016f s men\u0161\u00edm po\u010dtem blokac\u00ed<\/h3>\n
\nIndexy je \u010dasto nutn\u00e9 znovu vytv\u00e1\u0159et, nap\u0159\u00edklad kdy\u017e se zv\u011bt\u0161\u00ed na velikosti a op\u011btovn\u00e9 vytvo\u0159en\u00ed m\u016f\u017ee urychlit skenov\u00e1n\u00ed. Tak\u00e9 indexy se mohou po\u0161kodit. Zm\u011bna parametr\u016f indexu m\u016f\u017ee tak\u00e9 vy\u017eadovat jeho p\u0159epracov\u00e1n\u00ed.<\/p>\n
Povolujeme paraleln\u00ed vytv\u00e1\u0159en\u00ed index\u016f<\/h3>\n
\nV PostgreSQL 11 je vytv\u00e1\u0159en\u00ed indexu B-Tree konkurentn\u00ed. K urychlen\u00ed procesu vytv\u00e1\u0159en\u00ed m\u016f\u017ee b\u00fdt pou\u017eito n\u011bkolik paraleln\u011b pracuj\u00edc\u00edch pracovn\u00edk\u016f. Ujist\u011bte se v\u0161ak, \u017ee jsou tyto konfigura\u010dn\u00ed parametry nastaveny spr\u00e1vn\u011b:<\/p>\n
SET max_parallel_workers = 32;\nSET max_parallel_maintenance_workers = 16;<\/code><\/pre>\n
\nV\u00fdchoz\u00ed hodnoty jsou p\u0159\u00edli\u0161 n\u00edzk\u00e9. Ide\u00e1ln\u011b byste m\u011bli tato \u010d\u00edsla zvy\u0161ovat spolu s po\u010dtem j\u00e1der procesoru. Podrobnosti najdete v dokumentaci<\/a><\/noindex>.<\/p>\n
Pozad\u00ed pro vytv\u00e1\u0159en\u00ed index\u016f<\/h3>\n
\nM\u016f\u017eete vytvo\u0159it index na pozad\u00ed pomoc\u00ed parametru CONCURRENTLY<\/code> p\u0159\u00edkaz\u016f. CREATE INDEX<\/code>:<\/p>\n
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);\nCREATE INDEX<\/code><\/pre>\n
Tento postup vytv\u00e1\u0159en\u00ed indexu se li\u0161\u00ed od b\u011b\u017en\u00e9ho t\u00edm, \u017ee nevy\u017eaduje blokaci tabulky, co\u017e znamen\u00e1, \u017ee neblokuje operace z\u00e1pisu. Na druh\u00e9 stran\u011b to zabere v\u00edce \u010dasu a spot\u0159ebov\u00e1v\u00e1 v\u00edce zdroj\u016f.<\/p>\n
Postgres nab\u00edz\u00ed \u0159adu flexibiln\u00edch mo\u017enost\u00ed pro vytv\u00e1\u0159en\u00ed index\u016f a \u0159e\u0161en\u00ed r\u016fzn\u00fdch specifick\u00fdch p\u0159\u00edpad\u016f, stejn\u011b jako mo\u017enosti spr\u00e1vy datab\u00e1ze pro p\u0159\u00edpad explozivn\u00edho r\u016fstu va\u0161\u00ed aplikace. Douf\u00e1me, \u017ee tyto tipy v\u00e1m pomohou u\u010dinit dotazy rychlej\u0161\u00edmi a datab\u00e1zi p\u0159ipravenou na \u0161k\u00e1lov\u00e1n\u00ed.
\n
Zdroj: 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\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\udd47Vyu\u017e\u00edv\u00e1me v\u0161echny mo\u017enosti index\u016f v PostgreSQL | ProHoster","description":"V sv\u011bt\u011b Postgres jsou indexy nesm\u00edrn\u011b d\u016fle\u017eit\u00e9 pro.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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.","og:url":"https:\/\/prohoster.info\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/34419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=34419"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/34419\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/25931"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=34419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=34419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=34419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}