A Postgres világában az indexek elengedhetetlenek az adatbázis tárhelyén (úgynevezett „kupac”) való hatékony navigációhoz. A Postgres nem támogatja a fürtözést, és az MVCC architektúra azt eredményezi, hogy ugyanannak a tuple-nek több verziója is elérhető. Ezért nagyon fontos, hogy hatékony indexeket tudjunk létrehozni és karbantartani az alkalmazások támogatására.
Íme néhány tipp az indexek használatának optimalizálásához és javításához.
Megjegyzés: az alább látható lekérdezések nem módosítva működnek .
Lefedő indexek használata
Nézzük meg az inaktív felhasználók e-mail címeinek kinyerésére irányuló kérést. asztal customer van egy oszlop active, és a lekérdezés egyszerű:
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 meghívja a teljes tábla vizsgálati sorozatot customer. Hozzunk létre egy indexet egy oszlopon active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
Segített, az ezt követő szkennelésből "index scan". Ez azt jelenti, hogy a Postgres átvizsgálja az indexet "idx_cust1", majd folytassa a keresést a táblázatkupacban, hogy beolvassa a többi oszlop (ebben az esetben az oszlop) értékét email), amelyre a lekérdezésnek szüksége van.
A fedőindexeket a PostgreSQL 11 vezeti be. Lehetővé teszik egy vagy több további oszlop felvételét az indexbe - ezek értékeit az index adattárában tárolják.
Ha kihasználnánk ezt a funkciót, és hozzáadnánk az e-mail értéket az indexhez, akkor a Postgres-nek nem kellene a táblázatkupacban keresnie az értéket. email. Lássuk, hogy ez sikerül-e:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan' azt jelzi, hogy a lekérdezésnek most már csak egy indexre van szüksége, ami segít elkerülni, hogy minden lemez I/O legyen a táblakupac olvasásához.
A lefedő indexek jelenleg csak a B-fák esetében érhetők el. Ebben az esetben azonban a karbantartási ráfordítás nagyobb lesz.
Részleges indexek használata
A részleges indexek a tábla sorainak csak egy részét indexelik. Ez menti az indexek méretét, és gyorsabbá teszi a szkennelést.
Tegyük fel, hogy szeretnénk listát kapni ügyfeleink kaliforniai e-mail címeiről. A kérés a következő lesz:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)Mit adnak nekünk a szokásos 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)
letapogatás address felváltotta az index scan idx_address1majd pásztázta a kupacot address.
Mivel ez egy gyakori lekérdezés és optimalizálásra szorul, használhatunk részleges indexet, amely csak azokat a sorokat indexeli, amelyekben 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 olvasható idx_address2 és nem érinti az asztalt address.
Többértékű indexek használata
Előfordulhat, hogy egyes indexelendő oszlopok nem tartalmaznak skaláris adattípust. Oszloptípusok, mint pl jsonb, arrays и tsvector összetett vagy több értéket tartalmaznak. Ha indexelnie kell az ilyen oszlopokat, akkor általában meg kell keresnie az összes egyedi értéket ezekben az oszlopokban.
Próbáljuk meg megtalálni a sikertelen felvételekből származó vágásokat tartalmazó filmek címét. asztal film nevű szövegoszlop van special_features. Ha a film rendelkezik ezzel a "speciális tulajdonsággal", akkor az oszlop szövegtömbként tartalmazza az elemet Behind The Scenes. Az összes ilyen film kereséséhez ki kell jelölnünk az összes sort a "Behind The Scenes" felirattal bármilyen tömbértékek special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Beágyazó operátor @> ellenőrzi, hogy a jobb oldal a bal oldal részhalmaza-e.
Terv kérése:
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)Amely teljes kupac vizsgálatot igényel 67 költséggel.
Lássuk, segít-e nekünk egy normál 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 nem is vették figyelembe. A B-fa index nem ismeri az egyes elemek létezését az indexelt értékekben.
Szükségünk van egy GIN indexre.
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 leképezését az indexelt összetett értékekhez, így a lekérdezési terv költsége több mint felére csökken.
Megszabadulni az ismétlődő indexektől
Az indexek idővel felhalmozódnak, és néha egy új index ugyanazt a meghatározást tartalmazhatja, mint az előzőek egyike. Használhatja a katalógusnézetet az indexek ember által olvasható SQL-definícióinak lekéréséhez. 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;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
Szuperkészlet indexek
Előfordulhat, hogy sok indexhez jut, amelyek közül az egyik olyan oszlopokat indexel, amelyek más indexeket indexelnek. Ez lehet kívánatos, de lehet, hogy nem – a szuperkészlet csak index-ellenőrzést eredményezhet, ami jó, de előfordulhat, hogy túl sok helyet foglal el, vagy a lekérdezés, amelyet a szuperkészlet optimalizálni kívánt, már nem használatos.
Ha automatizálnia kell az ilyen indexek meghatározását, akkor kezdheti az asztaltól pg_catalog.
Nem használt indexek
Ahogy az adatbázisokat használó alkalmazások fejlődnek, úgy fejlődnek az általuk használt lekérdezések is. A korábban hozzáadott indexeket már nem használhatja semmilyen lekérdezés. Valahányszor egy index vizsgálatra kerül, a statisztikai kezelő megjelöli azt a rendszerkatalógus nézetben pg_stat_user_indexes láthatod az értéket idx_scan, ami egy kumulatív számláló. Ha nyomon követi ezt az értéket egy bizonyos időszakon (például egy hónapon keresztül), akkor jó képet ad arról, hogy mely indexek nincsenek használatban, és mely indexeket lehet elvetni.
Itt található egy lekérdezés a séma összes indexének aktuális vizsgálati számának lekéréséhez ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)Indexek újjáépítése kevesebb zárral
Az indexeket gyakran újra kell építeni, például ha felduzzadnak, és az újraépítés felgyorsíthatja a vizsgálatot. Az indexek is megsérülhetnek. Az index paramétereinek módosítása újjáépítést is igényelhet.
Párhuzamos index létrehozásának engedélyezése
A PostgreSQL 11-ben a B-Tree index létrehozása párhuzamos. A létrehozási folyamat felgyorsítása érdekében több párhuzamos dolgozó is használható. Azonban győződjön meg arról, hogy ezek a konfigurációs beállítások megfelelően vannak beállítva:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Az alapértelmezett értékek túl kicsik. Ideális esetben ezeknek a számoknak a processzormagok számával együtt kell növekedniük. Bővebben itt .
Háttérindex létrehozása
Az opció segítségével indexet hozhat létre a háttérben CONCURRENTLY parancsokat CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXEz az indexkészítési eljárás abban különbözik a szokásostól, hogy nem igényel zárolást a táblán, ezért nem blokkolja az írási műveleteket. Másrészt több időt vesz igénybe, és több erőforrást fogyaszt.
A Postgres nagy rugalmasságot biztosít az indexek létrehozásához és a speciális esetek megoldásának módjaihoz, valamint az adatbázis kezeléséhez, ha az alkalmazás robbanásszerűen növekedne. Reméljük, hogy ezek a tippek segítenek a lekérdezések gyors lekérdezésében, és az adatbázis skálázhatóságában.
Forrás: will.com