ProHoster > Блог > Administracija > Koristeći sve karakteristike indeksa u PostgreSQL-u

Koristeći sve karakteristike indeksa u PostgreSQL-u

Koristeći sve karakteristike indeksa u PostgreSQL-u
U svetu Postgresa, indeksi su neophodni za efikasnu navigaciju pohranom baze podataka (koja se naziva "gomila"). Postgres ne podržava grupisanje za njega, a MVCC arhitektura uzrokuje da završite sa mnogim verzijama istog tuple-a. Stoga je vrlo važno biti u mogućnosti kreirati i održavati efikasne indekse za podršku aplikacijama.

Evo nekoliko savjeta za optimizaciju i poboljšanje upotrebe indeksa.

Napomena: upiti prikazani ispod rade na nepromijenjenom pagila uzorak baze podataka.

Korištenje indeksa pokrivanja

Pogledajmo zahtjev za izdvajanje adresa e-pošte za neaktivne korisnike. Table customer postoji kolona active, a upit je jednostavan:

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)

Upit poziva čitav niz skeniranja tablice customer. Kreirajmo indeks na koloni 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)

Pomoglo je, naknadno skeniranje se pretvorilo u "index scan". To znači da će Postgres skenirati indeks "idx_cust1", a zatim nastavite s pretraživanjem hrpe tablice kako biste pročitali vrijednosti drugih stupaca (u ovom slučaju kolone email) koje je potrebno upitu.

Pokrivajući indeksi su predstavljeni u PostgreSQL 11. Omogućuju vam da uključite jednu ili više dodatnih kolona u sam indeks - njihove vrijednosti se pohranjuju u skladištu podataka indeksa.

Ako bismo koristili ovu funkciju i dodali vrijednost e-pošte unutar indeksa, Postgres ne bi trebao tražiti vrijednost u hrpi tablice. email. Da vidimo hoće li ovo uspjeti:

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' nam govori da je upitu sada potreban samo indeks, koji pomaže da se izbjegne sav I/O disk za čitanje gomile tablice.

Indeksi pokrivanja trenutno su dostupni samo za B-stabla. Međutim, u ovom slučaju, napor održavanja će biti veći.

Korištenje parcijalnih indeksa

Djelomični indeksi indeksiraju samo podskup redova u tabeli. Ovo štedi veličinu indeksa i čini skeniranje bržim.

Recimo da želimo da dobijemo listu adresa e-pošte naših klijenata u Kaliforniji. Zahtjev će biti ovakav:

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)

Šta će nam dati obični indeksi:

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)

Skeniraj address je zamijenjen indeksnim skeniranjem idx_address1a zatim skenirao hrpu address.

Pošto je ovo čest upit i treba ga optimizirati, možemo koristiti parcijalni indeks, koji indeksira samo one redove s adresama u kojima je okrug ‘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)

Sada upit samo čita idx_address2 i ne dodiruje sto address.

Korištenje viševrijednih indeksa

Neke kolone koje treba indeksirati možda neće sadržavati skalarni tip podataka. Tipovi kolona poput jsonb, arrays и tsvector sadrže složene ili višestruke vrijednosti. Ako trebate indeksirati takve stupce, obično morate pretraživati ​​sve pojedinačne vrijednosti u tim stupcima.

Pokušajmo pronaći naslove svih filmova koji sadrže rezove iz neuspješnih snimaka. Table film postoji tekstualna kolona pod nazivom special_features. Ako film ima ovo "posebno svojstvo", kolona sadrži element kao tekstualni niz Behind The Scenes. Da bismo pretražili sve takve filmove, moramo odabrati sve redove sa "Iza kulisa" kada bilo koji vrijednosti niza special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Operator gniježđenja @> provjerava da li je desna strana podskup lijeve strane.

Zatražite plan:

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)

Što zahtijeva potpuno skeniranje hrpe uz cijenu od 67.

Hajde da vidimo da li nam običan indeks B-stabla pomaže:

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)

Indeks nije ni razmatran. Indeks B-stabla nije svjestan postojanja pojedinačnih elemenata u indeksiranim vrijednostima.

Treba nam GIN indeks.

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)

GIN indeks podržava mapiranje pojedinačnih vrijednosti u odnosu na indeksirane složene vrijednosti, što rezultira troškom plana upita koji je više nego prepolovljen.

Riješite se duplih indeksa

Indeksi se akumuliraju tokom vremena, a ponekad novi indeks može sadržavati istu definiciju kao i jedan od prethodnih. Možete koristiti kataloški prikaz da dobijete čovjeku čitljive SQL definicije indeksa. pg_indexes. Također možete lako pronaći identične definicije:

 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)

Superset Indexes

Može se dogoditi da završite s mnogo indeksa, od kojih jedan indeksira nadskup kolona koji indeksiraju druge indekse. Ovo može ili ne mora biti poželjno – superskup može rezultirati skeniranjem samo indeksa, što je dobro, ali može zauzeti previše prostora ili se više ne koristi upit koji je nadskup trebao optimizirati.

Ako trebate automatizirati definiciju takvih indeksa, možete početi s pg_index sa stola pg_catalog.

Nekorišteni indeksi

Kako se razvijaju aplikacije koje koriste baze podataka, tako se razvijaju i upiti koje koriste. Indeksi dodani ranije ne mogu se više koristiti ni za jedan upit. Svaki put kada se indeks skenira, on je označen od strane menadžera statistike, au prikazu sistemskog kataloga pg_stat_user_indexes možete vidjeti vrijednost idx_scan, što je kumulativni brojač. Praćenje ove vrijednosti tokom određenog vremenskog perioda (recimo mjesec dana) dat će dobru predstavu o tome koji se indeksi ne koriste i koji bi mogli biti odbačeni.

Ovdje je upit za dobivanje trenutnog broja skeniranja svih indeksa u šemi ‘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)

Obnova indeksa sa manje zaključavanja

Indekse je često potrebno ponovo izgraditi, na primjer kada postanu napuhani, a ponovna izgradnja može ubrzati skeniranje. Takođe, indeksi se mogu oštetiti. Promjena parametara indeksa može također zahtijevati njegovu ponovnu izgradnju.

Omogućite kreiranje paralelnog indeksa

U PostgreSQL 11, kreiranje indeksa B-stabla je istovremeno. Da bi se ubrzao proces kreiranja, može se koristiti nekoliko paralelnih radnika. Međutim, provjerite jesu li ove opcije konfiguracije ispravno postavljene:

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Zadane vrijednosti su premale. U idealnom slučaju, ovi brojevi bi trebali rasti zajedno s brojem procesorskih jezgara. Pročitajte više u dokumentaciju.

Kreiranje pozadinskog indeksa

Možete kreirati indeks u pozadini koristeći opciju CONCURRENTLY naredbe CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Ova procedura kreiranja indeksa razlikuje se od uobičajene po tome što ne zahtijeva zaključavanje tablice i stoga ne blokira operacije pisanja. S druge strane, potrebno je više vremena i troši više resursa.

Postgres pruža veliku fleksibilnost za kreiranje indeksa i načina za rješavanje posebnih slučajeva, kao i načina upravljanja bazom podataka u slučaju da vaša aplikacija eksplodira. Nadamo se da će vam ovi savjeti pomoći da brzo postavite upite i pripremite bazu podataka za skaliranje.

izvor: www.habr.com

Dodajte komentar