A Postgres világában az indexek rendkívül fontosak az adatbázis tárolójában való hatékony navigációhoz (ezt hívják "heap"-nek). A Postgres nem támogatja a klaszterezést, és az MVCC architektúra miatt számos példány halmozódik fel ugyanarról a tuple-ről. Ezért rendkívül fontos, hogy képesek legyünk hatékony indexek létrehozására és fenntartására az alkalmazások támogatásához.
Javaslom néhány tipp megfontolását az indexek optimalizálására és felhasználásuk javítására.
Megjegyzés: az alábbi lekérdezések módosítatlan adatbázis példa alapján működnek. .
A borító indexek használata (Covering Indexes)
Nézzük meg a nem aktív felhasználók e-mail címeinek lekérdezését. A "customer" táblában customer van egy oszlop, aktív, így a lekérdezés nem bonyolult:
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) A lekérdezés teljes táblaszkennelést indít el. customerHozzunk létre indexet az oszlophoz, aktív:
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) Ez segített, a következő szkennelés "index scan"-ra változott. Ez azt jelenti, hogy a Postgres az "idx_cust1" indexet fogja átvizsgálni, majd folytatja a keresést a táblában, hogy elolvassa a lekérdezéshez szükséges többi oszlop értékét (jelen esetben az oszlopot,),amelyekre a lekérdezésnek szüksége van. emailA PostgreSQL 11-ben érkeztek a borító indexek. Ezek lehetővé teszik, hogy az indexbe egy vagy több további oszlopot is beépítsünk – azok értékei az index adatbázis tárolójában kerülnek tárolásra.
Ha ezt a lehetőséget használnánk, és az e-mail cím értékét beemelnénk az indexbe, akkor a Postgres-nek nem lenne szüksége a táblába való keresésre az értékért.
Nézzük meg, működne-e: emailpagila=# 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 «" azt jelzi, hogy a lekérdezéshez most már elegendő csak az index, ami segít elkerülni az összes lemezes I/O műveletet a táblához való hozzáféréshez.» azt jelenti számunkra, hogy a lekérdezéshez most már elegendő csak egy index, ami segít elkerülni az összes lemezműveletet a táblázat halmazának olvasásához.
A kiemelt indexek ma már csak B-fákhoz elérhetőek. Azonban ebben az esetben a karbantartási erőfeszítések magasabbak lesznek.
Részleges indexek használata
A részleges indexek csak a táblázat sorainak egy részhalmazát indexelik. Ez lehetővé teszi az indexek méretének csökkentését és a gyorsabb átvizsgálást.
Tegyük fel, hogy ki szeretnénk nyerni az ügyfeleink e-mail címeit Kaliforniából. A lekérdezés így fog kinézni:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
amihez egy lekérdezési terv tartozik, amely mindkét csatlakoztatott táblának az átvizsgálását magában foglalja:
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)Mit adnak nekünk a hagyományos indexek:
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) Átvizsgálás address legalábbis indexelési átvizsgálással lesz helyettesítve idx_address1, majd az adathalmazon keresztül átvizsgálva address.
Mivel ez egy gyakori lekérdezés és optimalizálni kell, használhatunk egy részleges indexet, amely csak azokat a sorokat indexeli, ahol a kerület ‘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) Most a lekérdezés csak idx_address2 és nem érinti a táblázatot address.
Többértékű indexek használata (Multi-Value Indexes)
Bizonyos oszlopok, amelyeket indexelni kell, nem tartalmazhatnak skaláris típusú adatokat. Az oszloptípusok, mint például jsonb, tömbök és tsvector összetett vagy többszörös értékeket tartalmazhatnak. Ha ilyen oszlopokat szeretne indexelni, általában minden egyes értéket meg kell keresnie ezekben az oszlopokban.
Próbáljuk megkeresni az összes olyan film címét, amely tartalmazza a hibás szinkronsorok vágatait. A táblázatban film van egy szöveges oszlop, amelynek neve special_features. Ha a filmnek van ez az "különleges tulajdonság", akkor az oszlopban egy szöveges tömb elem található Behind The Scenes. Az összes ilyen film kereséséhez ki kell választanunk azokat a sorokat, amelyek tartalmazzák a "Behind The Scenes"-t bármely tömb értéknél special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; A tartalmazási operátor (containment operator) @> ellenőrzi, hogy a jobb oldal a bal oldal részhalmaza-e.
Lekérdezési terv:
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)Ami teljes veremvizsgálatot kér, 67-es költséggel.
Nézzük meg, hogy segít-e nekünk a szokásos B-fa index:
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)Az indexet még csak nem is vizsgálták. A B-fa index nem sejt a külön elem létezéséről az indexelt értékekben.
GIN-indexre van szükségünk.
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)A GIN-index támogatja az egyes értékek párosítását az indexelt összetett értékekkel, ennek következményeként a lekérdezési terv költsége több mint a felére csökken.
Megszabadulunk az indexek duplikálásától
Az indexek idővel felhalmozódnak, és néha egy új index ugyanazt a definíciót tartalmazhatja, mint az egyik korábbi. Az ember által olvasható SQL-index definíciók megszerzéséhez használhatja a katalogizáló nézetet. pg_indexes. Könnyen megtalálhatja az azonos definíciókat is:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
És itt a végeredmény, amikor futtatják a stock pagila adatbázison:
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 sor)
Több alkategória indexek (Superset Indexes)
Előfordulhat, hogy sok indexet gyűjt össze, ezek közül az egyik index olyan oszlopok fölé indexel, amelyeket más indexek is indexelnek. Ez lehet kívánatos, de nem is - a superset arra vezethet, hogy csak az indexeken zajlik a beolvasás, ami jó, de sok helyet is foglalhat, vagy a lekérdezés, amelynek optimalizálására ez a superset készült, már nem használják.
Ha automatizálni kívánja az ilyen indexek azonosítását, akkor kezdheti a következővel: a táblázatból pg_catalog.
Használatlan indexek
Ahogy a meglévő adatbázisokat használó alkalmazások fejlődnek, úgy a használt lekérdezések is fejlődnek. A korábban hozzáadott indexek már lehet, hogy egy lekérdezés által sem használtak. Minden egyes indexbeolvasásnál a statisztikai ügynök megjegyzi, és a rendszerkatalógus nézetben pg_stat_user_indexes megnézheti az értéket. idx_scan, amely egy kumulatív számláló. Ennek az értéknek a nyomon követése egy bizonyos időszak alatt (mondjuk egy hónap) jó képet ad arról, hogy mely indexek nem használatosak és törölhetők.
Itt van a lekérdezés az összes index aktuális vizsgálati számlálójának lekérésére a sémában ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
azzal a kimenettel, mint ez:
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 sor)Indexek újbóli létrehozása kevesebb zárolással
Gyakran szükség van indexek újbóli létrehozására, például amikor azok megnőnek, és a recreálás felgyorsíthatja a keresést. Az indexek megsérülhetnek is. Az indexparaméterek megváltoztatása is megkövetelheti az újra létrehozást.
Bontsuk ki a párhuzamos index létrehozást
A PostgreSQL 11-ben a B-Tree index létrehozása párhuzamos. Az index létrehozásának folyamatát több párhuzamosan működő munkaerő is felgyorsíthatja. Kérjük, győződjön meg róla, hogy ezek a konfigurációs paraméterek helyesen vannak beállítva:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Az alapértelmezett értékek túl alacsonyak. Ideális esetben ezeket a számokat az összes CPU mag számával együtt kell növelni. További részletekért olvassa el a .
Háttérben végzett index létrehozás
Indexet hozhat létre háttérben a következő paraméter alkalmazásával CONCURRENTLY parancsok CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEz az index létrehozási eljárás eltér az általánostól, mivel nem igényel táblazárolást, így nem blokkolja az írási műveleteket sem. Másrészt több időt és több erőforrást igényel.
A Postgres számos rugalmas lehetőséget kínál indexek létrehozására és a különleges esetek kezelésére, valamint módszereket biztosít az adatbázis kezelésére alkalmazásának robbanásszerű növekedése esetén. Reméljük, hogy ezek a tippek segítenek Önnek a lekérdezések gyorsításában, és az adatbázis felkészítésében a skálázásra.
Forrás: habr.com
