{"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\/et\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","title":{"rendered":"Kasutame PostgreSQL-i indekseerimise k\u00f5iki v\u00f5imalusi","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Kasutame
\nPostgreses on indeksid \u00e4\u00e4rmiselt olulised andmebaasi salvestamise (tuntud ka kui 'heap') efektiivsel navigeerimisel. Postgres ei toeta sellele klasterdamist ning MVCC arhitektuur toob kaasa selle, et teil koguneb sama tupikute versioone. Seet\u00f5ttu on v\u00e4ga oluline osata luua ja hallata efektiivseid indexeid rakenduste toetamiseks.<\/p>\n

Esitan teile m\u00f5ned n\u00e4pun\u00e4ited indeksite optimeerimiseks ja t\u00f5husamaks kasutamiseks.<\/p>\n

M\u00e4rkus: allpool toodud p\u00e4ringud t\u00f6\u00f6tavad muutmata pagila andmebaasi n\u00e4idisel<\/a><\/noindex>.<\/i>
\n<\/a><\/noindex><\/p>\n

Katvuse indeksite kasutamine (Covering Indexes)<\/h3>\n

\nVaadakem p\u00e4ringut, et saada k\u00e4tte mitteaktiivsete kasutajate e-posti aadressid. Tabelis customer<\/code> on veerg active<\/code>, ja p\u00e4ring on lihtne:<\/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
\nP\u00e4ring n\u00f5uab tabeli t\u00e4ielikku j\u00e4rjestikust skaneerimist customer<\/code>. Loome indeksi veeru jaoks active<\/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
\n\u041f\u043e\u043c\u043e\u0433\u043b\u043e, \u043f\u043e\u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0435 \u0441\u043a\u0430\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u043f\u0440\u0435\u0432\u0440\u0430\u0442\u0438\u043b\u043e\u0441\u044c \u0432 «indeksi skaneerimiseks<\/code>«. \u042d\u0442\u043e \u043e\u0437\u043d\u0430\u0447\u0430\u0435\u0442, \u0447\u0442\u043e Postgres \u043f\u0440\u043e\u0441\u043a\u0430\u043d\u0438\u0440\u0443\u0435\u0442 \u0438\u043d\u0434\u0435\u043a\u0441 «idx_cust1<\/code>«, \u0430 \u0437\u0430\u0442\u0435\u043c \u0434\u0430\u043b\u044c\u0448\u0435 \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0438\u0442 \u043f\u043e\u0438\u0441\u043a \u043f\u043e \u043a\u0443\u0447\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b, \u0447\u0442\u043e\u0431\u044b \u043f\u0440\u043e\u0447\u0435\u0441\u0442\u044c \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0434\u0440\u0443\u0433\u0438\u0445 \u043a\u043e\u043b\u043e\u043d\u043e\u043a (\u0432 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435, \u043a\u043e\u043b\u043e\u043d\u043a\u0443 email<\/code>), mis on p\u00e4ringuks vajalik.<\/p>\n
PostgreSQL 11-s lisandus katvad indeksid. Need v\u00f5imaldavad indeksi enda sisse lisada \u00fche v\u00f5i mitu t\u00e4iendavat veergu \u2014 nende v\u00e4\u00e4rtused salvestatakse indeksi andmete hoidlas.<\/p>\n
Kui me kasutaksime seda v\u00f5imalust ja lisaksime indeksi sisse e-posti v\u00e4\u00e4rtuse, siis Postgresel ei oleks vaja otsida tabeli hunnikust v\u00e4\u00e4rtust email<\/code>. Vaatame, kas see t\u00f6\u00f6tab:<\/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\u00abIndex Only Scan<\/code>» \u0433\u043e\u0432\u043e\u0440\u0438\u0442 \u043d\u0430\u043c, \u0447\u0442\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0443 \u0442\u0435\u043f\u0435\u0440\u044c \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u043e\u0434\u043d\u043e\u0433\u043e \u043b\u0438\u0448\u044c \u0438\u043d\u0434\u0435\u043a\u0441\u0430, \u0447\u0442\u043e \u043f\u043e\u043c\u043e\u0433\u0430\u0435\u0442 \u0438\u0437\u0431\u0435\u0433\u0430\u0442\u044c \u0432\u0441\u0435\u0445 \u0434\u0438\u0441\u043a\u043e\u0432\u044b\u0445 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u0432\u0432\u043e\u0434\u0430\/\u0432\u044b\u0432\u043e\u0434\u0430 \u0434\u043b\u044f \u0447\u0442\u0435\u043d\u0438\u044f \u043a\u0443\u0447\u0438 \u0442\u0430\u0431\u043b\u0438\u0446\u044b.<\/p>\n
T\u00e4na on katvad indeksid saadaval ainult B-puude jaoks. Kuid sel juhul on hoolduskohustused suuremad.<\/p>\n
Osaliste indeksite kasutamine<\/h3>\n
\nOsalised indeksid indekseerivad vaid tabeli rida alamhulga. See aitab indeksite suurust s\u00e4\u00e4sta ja skaneerimist kiiremini teostada.<\/p>\n
Oletame, et peame saama nimekirja meie Kaliforniast p\u00e4rit klientide e-posti aadressidest. P\u00e4ring oleks j\u00e4rgmine:<\/p>\n
SELECT c.email FROM customer c\nJOIN address a ON c.address_id = a.address_id\nWHERE a.district = 'California';\nmis sisaldab p\u00e4ringukava, mis h\u00f5lmab m\u00f5lema \u00fchendatud tabeli skaneerimist:\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                              QUERY PLAN\n----------------------------------------------------------------------\n Hash Join  (cost=15.65..32.22 rows=9 width=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)\n   ->  Hash  (cost=15.54..15.54 rows=9 width=4)\n         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)\n               Filter: (district = 'California'::text)\n(6 rows)<\/code><\/pre>\n
\nMida pakuvad meile tavain eksid:<\/p>\n
pagila=# LOOMI INDIREKT idx_address1 ON address(district);\nLOOMI INDIREKT\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                                      K\u00dcSIMUSE PLANEERIMINE\n---------------------------------------------------------------------------------------\n Hash Joint  (kulus=12.98..29.55 read=9 laiuse=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan on customer c  (kulus=0.00..14.99 read=599 laiuse=34)\n   ->  Hash  (kulus=12.87..12.87 read=9 laiuse=4)\n         ->  Bitmap Heap Scan on address a  (kulus=4.34..12.87 read=9 laiuse=4)\n               Uuesti Kontrolli Tingimus: (district = 'California'::text)\n               ->  Bitmap Indeksi Skaneerimine on idx_address1  (kulus=0.00..4.34 read=9 laiuse=0)\n                     Indeksi Tingimus: (district = 'California'::text)\n(8 read)<\/code><\/pre>\n
\nSkaneerimine address<\/code> oli asendatud indeksi skaneerimisega idx_address1<\/code>, ja seej\u00e4rel skaneeriti hunnik address<\/code>.<\/p>\n
Kuna see on sagedane p\u00e4ring ja seda tuleb optimeerida, saame kasutada osalist indeksit, mis indekseerib vaid need read aadresside kohta, kus piirkond \u2018California\u2019<\/code>:<\/p>\n
pagila=# LOOME INDEX idx_address2 ON address(address_id) WHERE district='California';\nLOOME INDEX\npagila=# SELGITA SELECT c.email FROM customer c\npagila-# JOIN address a ON c.address_id = a.address_id\npagila-# WHERE a.district = 'California';\n                                           K\u00dcSIMUSE PLANEERIMINE\n------------------------------------------------------------------------------------------------\n Hash Join  (maksumus=12.38..28.96 read=9 laius=32)\n   Hash Cond: (c.address_id = a.address_id)\n   ->  Seq Scan on customer c  (maksumus=0.00..14.99 read=599 laius=34)\n   ->  Hash  (maksumus=12.27..12.27 read=9 laius=4)\n         ->  Index Only Scan using idx_address2 on address a  (maksumus=0.14..12.27 read=9 laius=4)\n(5 read)<\/code><\/pre>\n
\nN\u00fc\u00fcd k\u00fcsib p\u00e4ring ainult idx_address2<\/code> ja ei puutu tabelisse address<\/code>.<\/p>\n
Mitme v\u00e4\u00e4rtuse indeksite kasutamine (Multi-Value Indexes)<\/h3>\n
\nM\u00f5ned veerud, mida tuleb indekseerida, ei pruugi sisaldada skalaarset andmet\u00fc\u00fcpi. T\u00fc\u00fcbid nagu jsonb<\/code>, massivid<\/code> ja tsvector<\/code> v\u00f5ivad sisaldada komposiit- v\u00f5i mitme v\u00e4\u00e4rtusega. Kui peate selliseid veerge indekseerima, tuleb tavaliselt otsida k\u00f5igi individuaalsete v\u00e4\u00e4rtuste kaudu nendes veergudes.<\/p>\n
Proovime leida k\u00f5igi filmide pealkirju, mis sisaldavad eba\u00f5nnestunud dubleerimisse l\u00f5ike. Tabelis film<\/code> on tekstiline veerg, mida nimetatakse special_features<\/code>. Kui filmil on see \"eriline omadus\", siis veerus on elementi teksti massiivi kujul Behind The Scenes<\/code>. K\u00f5ikide nende filmide leidmiseks peame valima k\u00f5ik read, kus on \u00abBehind The Scenes\u00bb iga\u00fchega <\/b>massivi v\u00e4rdja special_features<\/code>:<\/p>\n
SELECT title FROM film WHERE special_features @> '{\"Behind The Scenes\"}';<\/code><\/pre>\n
\nS\u00fcntaks Operator @><\/code> kontrollib, kas parempoolne k\u00fclg on vasakpoolse k\u00fclje alamkogum.<\/p>\n
K\u00fcsimuste plaan:<\/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
\nMis kutsub t\u00e4ielikku partii skaneerimist, mille maksumus on 67.<\/p>\n
Vaadake, kas tavap\u00e4rane B-puu indeks aitab meid:<\/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
\nIndeksit ei arvestatud. B-puu indeks ei tea, et indeksit sisaldavad v\u00e4\u00e4rtused sisaldavad loetletud elemente.<\/p>\n
Me vajame GIN-indeksi.<\/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                                QUERY PLAN\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-indeks toetab eriv\u00e4\u00e4rtuste v\u00f5rdlemist indekseeritud komposiitv\u00e4\u00e4rtustega, mis v\u00e4hendab p\u00e4ringu plaani maksumust rohkem kui poole v\u00f5rra.<\/p>\n
Vabaneme indeksite dubleerimisest<\/h3>\n
\nIndeksid kogunevad aja jooksul ja m\u00f5nikord v\u00f5ib uus indeks sisaldada sama m\u00e4\u00e4ratlust kui \u00fcks varasematest. Inimesele arusaadavate SQL-i indeksim\u00e4\u00e4ratluste saamiseks saab kasutada kataloogivaadet pg_indexes<\/code>. Samuti leiate kergesti sama m\u00e4\u00e4ratluse:<\/p>\n
 VALIGE array_agg(indexname) AS indeksid, asenda(indexdef, indexname, '') AS defn\n    FROM pg_indexes\nGROUP BY defn\n  HAVING count(*) > 1;\nJa siin on tulemus, kui k\u00e4itada stock pagila andmebaasis:\npagila=#   VALIGE array_agg(indexname) AS indeksid, asenda(indexdef, indexname, '') AS defn\npagila-#     FROM pg_indexes\npagila-# GROUP BY defn\npagila-#   HAVING count(*) > 1;\n                                indeksid                                 |                                defn\n------------------------------------------------------------------------+------------------------------------------------------------------\n {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | LOO INDEX  PUBLIC.payment_p2017_01 KASUTADES btree (customer_id\n {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | LOO INDEX  PUBLIC.payment_p2017_02 KASUTADES btree (customer_id\n {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | LOO INDEX  PUBLIC.payment_p2017_03 KASUTADES btree (customer_id\n {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | LOO INDEX  PUBLIC.payment_p2017_04 KASUTADES btree (customer_id\n {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | LOO INDEX  PUBLIC.payment_p2017_05 KASUTADES btree (customer_id\n {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | LOO INDEX  PUBLIC.payment_p2017_06 KASUTADES btree (customer_id\n(6 rida)\n<\/code><\/pre>\n
<\/p>\n
\u00dcleminekuindeksid (Superset Indexes)<\/h3>\n
\nV\u00f5ib juhtuda, et teil on palju indekse, millest \u00fcks indekseerib veergude \u00fclemkogumi, mis indekseerivad teisi indekseid. See v\u00f5ib olla soovitav v\u00f5i mitte \u2013 \u00fclemkogum v\u00f5ib viia indekseid kasutades skaneerimiseni, mis on hea, kuid samas v\u00f5ib see v\u00f5tta liiga palju ruumi, v\u00f5i p\u00e4ringud, mille optimeerimiseks see \u00fclemkogum m\u00f5eldud oli, ei pruugi olla enam kasutusel.<\/p>\n
Kui peate automateerima selliste indekste m\u00e4\u00e4ramise, siis v\u00f5ite alustada pg_index<\/a><\/noindex> tabelist pg_catalog<\/code>.<\/p>\n
Kasutamata indeksid<\/h3>\n
\nKuna rakendused, mis kasutavad andmebaase, arenevad, arenevad ka nende p\u00e4ringud. Varem lisatud indeksid v\u00f5ivad enam mitte \u00fchtegi p\u00e4ringut puudutada. Iga indeksi skaneerimise ajal m\u00e4rgib statistikahaldaja selle ning s\u00fcsteemikatalooge pg_stat_user_indexes<\/code> v\u00f5ib vaadata v\u00e4\u00e4rtust idx_scan<\/code>, mis on kumulatiivne loendur. Selle v\u00e4\u00e4rtuse j\u00e4lgimine mingi ajavahemiku jooksul (\u00fctleme kuu) annab hea \u00fclevaate, millised indeksid ei ole kasutuses ja v\u00f5ivad eemaldada.<\/p>\n
Siin on p\u00e4ring, et saada k\u00f5igi skeemi indeksite skaneerimise praeguseid loendureid 'public'<\/code>:<\/p>\n
SELECT relname, indexrelname, idx_scan\nFROM   pg_catalog.pg_stat_user_indexes\nWHERE  schemaname = 'public';\nv\u00e4ljastatud niimoodi:\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 rida)<\/code><\/pre>\n
<\/p>\n
Indeksite uuesti loomine v\u00e4iksema lukustuste arvuga<\/h3>\n
\nTihti tuleb indekseid uuesti luua, n\u00e4iteks siis, kui need suurenevad ja uuesti loomine v\u00f5ib kiirendada skaneerimist. Samuti v\u00f5ivad indeksid kahjustuda. Indeksi parameetrite muutmine v\u00f5ib samuti n\u00f5uda selle uuesti loomist.<\/p>\n
L\u00fclitame sisse indeksite paralleelse loomise<\/h3>\n
\nPostgreSQL 11-s saab B-Tree indeksi loomine toimuda konkurentsiv\u00f5imeliselt. Protsessi kiirendamiseks v\u00f5ib kasutada mitmeid samaaegselt t\u00f6\u00f6tavaid t\u00f6\u00f6tlusi. Siiski veenduge, et need konfiguratsiooniparametrid on \u00f5igesti seadistatud:<\/p>\n
SET max_parallel_workers = 32;\nSET max_parallel_maintenance_workers = 16;<\/code><\/pre>\n
\nVaikimisi v\u00e4\u00e4rtused on liiga madalad. Ideaalis tuleks neid arvu k\u00e4rpimisega suurendada koos protsessorite arvu kasvuga. Lisainfot leiate dokumentatsioonis<\/a><\/noindex>.<\/p>\n
Taustal indekseerimise loomine<\/h3>\n
\nSaate luua indeksi taustal, kasutades parameetrit CONCURRENTLY<\/code> k\u00e4sklused CREATE INDEX<\/code>:<\/p>\n
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);\nCREATE INDEX<\/code><\/pre>\n
See indeksi loomise protseduur erineb tavalisest, kuna see ei n\u00f5ua tabeli lukustamist, mis t\u00e4hendab, et see ei blokeeri kirjutamisoperatsioone. Teisest k\u00fcljest kestab see kauem ja tarbib rohkem ressursse.<\/p>\n
Postgres pakub palju paindlikke v\u00f5imalusi indeksite loomiseks ja konkreetsete juhtumite lahendamiseks ning samuti viise andmebaasi haldamiseks juhul, kui teie rakenduse kasv on j\u00e4rsk. Loodame, et need n\u00e4pun\u00e4ited aitavad teil suurendada p\u00e4ringute kiirus ja muuta andmebaasi skaleerimise valmidus.
\n
Allikas: 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\udd47Kasutame PostgreSQL indekste maksimaalselt | ProHoster","description":"PostgreSQL'is on \u0438\u043d\u0434\u0435\u043a\u0441id \u00e4\u00e4rmiselt olulised andmebaasi salvestuse (tuntud kui \u00abkuhi\u00bb, heap) t\u00f5husaks navigeerimiseks. PostgreSQL ei toeta selle jaoks klasterdamist, ja MVCC arhitektuur viib selleni, et teil koguneb palju versioone samast tupest. Seet\u00f5ttu on v\u00e4ga oluline osata luua ja hallata t\u00f5husaid indekseid rakenduste toetamiseks. Pakun teie t\u00e4helepanu","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/ispolzuem-vse-vozmozhnosti-indeksov-v-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/34419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=34419"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/34419\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/25931"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=34419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=34419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=34419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}