W świecie Postgres indeksy są niezwykle ważne dla efektywnej nawigacji po magazynie bazy danych (nazywanym „kupą”, heap). Postgres nie wspiera dla niego klasteryzacji, a architektura MVCC prowadzi do gromadzenia wielu wersji tego samego krotki. Dlatego bardzo ważne jest umiejętne tworzenie i utrzymywanie efektywnych indeksów wspierających aplikacje.
Przedstawiam kilka wskazówek dotyczących optymalizacji i poprawy wykorzystania indeksów.
Uwaga: przedstawione poniżej zapytania działają na niezmodyfikowanym .
Wykorzystanie indeksów pokrywających (Covering Indexes)
Rozważmy zapytanie o wyciąganie adresów e-mail dla nieaktywnych użytkowników. W tabeli customer jest kolumna active, a zapytanie jest stosunkowo proste:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN ZAPYTANIA
-----------------------------------------------------------
Sekwencyjne skanowanie na customer (koszt=0.00..16.49 wierszy=15 szerokość=32)
Filtr: (active = 0)
(2 wiersze) W zapytaniu następuje pełne sekwencyjne skanowanie tabeli. customerStwórzmy indeks dla kolumny active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN ZAPYTANIA
-----------------------------------------------------------------------------
Skanowanie indeksu za pomocą idx_cust1 na customer (koszt=0.28..12.29 wierszy=15 szerokość=32)
Warunek Indeksu: (active = 0)
(2 wiersze) Pomogło, kolejne skanowanie zmieniło się na „skanowanie indeksu„. Oznacza to, że Postgres przeskanuje indeks „idx_cust1„, a następnie kontynuuje wyszukiwanie w kupie tabeli, aby odczytać wartości innych kolumn (w tym przypadku kolumny email), które są potrzebne zapytaniu.
W PostgreSQL 11 pojawiły się indeksy pokrywające. Dzięki nim można włączyć do samego indeksu jedną lub więcej dodatkowych kolumn — ich wartości są przechowywane w magazynie danych indeksu.
Gdybyśmy skorzystali z tej możliwości i dodali wartość e-maila do indeksu, to Postgres nie musiałby szukać w kupie tabeli wartości email. Zobaczmy, czy to zadziała:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
PLAN ZAPYTANIA
----------------------------------------------------------------------------------
Skanowanie tylko indeksu używając idx_cust2 na customer (koszt=0.28..12.29 wierszy=15 szerokość=32)
Warunek Indeksu: (active = 0)
(2 wiersze) «Skanowanie tylko indeksu„ mówi nam, że zapytanie teraz wymaga tylko samego indeksu, co pomaga uniknąć wszystkich operacji wejścia/wyjścia na dysku w celu odczytu z kupy tabeli.
Obecnie indeksy pokrywające są dostępne tylko dla drzew B. Jednak w tym przypadku koszty utrzymania będą wyższe.
Użycie indeksów częściowych
Indeksy częściowe indeksują tylko podzbiór wierszy tabeli. Pozwala to zaoszczędzić miejsce na indeksach i przyspieszyć skanowanie.
Załóżmy, że musimy uzyskać listę adresów e-mail naszych klientów z Kalifornii. Zapytanie będzie wyglądać tak:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
które ma plan zapytania obejmujący skanowanie obu połączonych tabel:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLAN ZAPYTANIA
----------------------------------------------------------------------
Wewnętrzne połączenie (koszt=15.65..32.22 wierszy=9 szerokość=32)
Warunek skrótu: (c.address_id = a.address_id)
-> Skanowanie sekwencyjne na customer c (koszt=0.00..14.99 wierszy=599 szerokość=34)
-> Skrót (koszt=15.54..15.54 wierszy=9 szerokość=4)
-> Skanowanie sekwencyjne na address a (koszt=0.00..15.54 wierszy=9 szerokość=4)
Filtr: (district = 'California'::text)
(6 wierszy)Co nam dadzą zwykłe indeksy:
pagila=# CREATE INDEX idx_address1 ON address(district);
INDeks został utworzony
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLAN ZAPYTANIA
---------------------------------------------------------------------------------------
Wewnętrzne połączenie (koszt=12.98..29.55 wierszy=9 szerokość=32)
Warunek skrótu: (c.address_id = a.address_id)
-> Skanowanie sekwencyjne na customer c (koszt=0.00..14.99 wierszy=599 szerokość=34)
-> Skrót (koszt=12.87..12.87 wierszy=9 szerokość=4)
-> Skanowanie bitmapy na address a (koszt=4.34..12.87 wierszy=9 szerokość=4)
Ponowne sprawdzenie warunku: (district = 'California'::text)
-> Skanowanie indeksu bitmapy na idx_address1 (koszt=0.00..4.34 wierszy=9 szerokość=0)
Warunek indeksu: (district = 'California'::text)
(8 wierszy) Skanowanie address zostało zastąpione skanowaniem indeksu idx_address1, a następnie przeskanowano kopię address.
Ponieważ jest to częste zapytanie i musi być zoptymalizowane, możemy użyć indeksu częściowego, który indeksuje tylko te wiersze z adresami, w których dzielnica ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
INDeks został utworzony
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
PLAN ZAPYTANIA
------------------------------------------------------------------------------------------------
Wewnętrzne połączenie (koszt=12.38..28.96 wierszy=9 szerokość=32)
Warunek skrótu: (c.address_id = a.address_id)
-> Skanowanie sekwencyjne na customer c (koszt=0.00..14.99 wierszy=599 szerokość=34)
-> Skrót (koszt=12.27..12.27 wierszy=9 szerokość=4)
-> Skanowanie tylko indeksów, korzystając z idx_address2 na address a (koszt=0.14..12.27 wierszy=9 szerokość=4)
(5 wierszy) Teraz zapytanie odczytuje tylko idx_address2 i nie dotyka tabeli address.
Użycie indeksów wielowartościowych (Multi-Value Indexes)
Niektóre kolumny, które trzeba zindeksować, mogą nie zawierać typu danych skalarnego. Typy kolumn takie jak jsonb, tablice i tsvector mogą zawierać wartości złożone lub mnogie. Jeśli musisz zindeksować takie kolumny, zazwyczaj trzeba przeszukać wszystkie pojedyncze wartości w tych kolumnach.
Spróbujemy znaleźć tytuły wszystkich filmów, zawierających sceny z nieudanych dubli. W tabeli film jest kolumna tekstowa, nazywana special_features. Jeśli film ma to „specjalne właściwość”, to w kolumnie znajduje się element w postaci tablicy tekstowej Behind The Scenes. Aby znaleźć wszystkie takie filmy, musimy wybrać wszystkie wiersze z „Behind The Scenes” przy wszystkich wartości tablicy special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; Operator kontentu (containment operator) @> sprawdza, czy prawa strona jest podzbiorem lewej strony.
Plan zapytania:
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)Który żąda pełnego skanowania heapu o koszcie 67.
Zobaczmy, czy zwykły indeks B-drzewa nam pomoż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 nawet nie został rozważony. Indeks B-drzewa nie ma pojęcia o istnieniu pojedynczych elementów w indeksowanych wartościach.
Potrzebujemy indeksu GIN.
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)Indeks GIN wspiera dopasowywanie pojedynczych wartości do indeksowanych wartości złożonych, co skutkuje tym, że koszt planu zapytania zmniejszy się o ponad połowę.
Pozbywamy się duplikacji indeksów.
Indeksy gromadzą się z czasem i czasami nowy indeks może zawierać tę samą definicję, co jeden z wcześniejszych. Aby uzyskać czytelne dla człowieka definicje SQL indeksów, można użyć widoku katalogu pg_indexes. Możesz też łatwo znaleźć identyczne definicje:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
A oto wynik po uruchomieniu na domyślnej bazie danych pagila:
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)
Indeksy nadmujące (Superset Indexes)
Może się zdarzyć, że zgromadzisz wiele indeksów, z których jeden indeksuje nadzbiory kolumn, które indeksują inne indeksy. Może to być zarówno korzystne, jak i nie — nadzbiór może prowadzić do skanowania tylko po indeksach, co jest dobre, ale może zajmować zbyt dużo miejsca, lub zapytanie, dla którego to nadzbiory miały być zoptymalizowane, już nie jest używane.
Jeśli musisz zautomatyzować określanie takich indeksów, to można zacząć od z tabeli pg_catalog.
Nieczytelne indeksy
W miarę jak rozwijają się aplikacje korzystające z baz danych, rozwijają się również wykorzystywane przez nie zapytania. Indeksy dodane wcześniej mogą już nie być używane przez żadne zapytanie. Przy każdym skanowaniu indeksu jest on oznaczany przez menedżera statystyk, a w widoku katalogu systemowego pg_stat_user_indexes można sprawdzić wartość idx_scan, która jest kumulatywnym licznikiem. Monitorowanie tej wartości przez pewien okres czasu (na przykład miesiąc) da dobry obraz tego, które indeksy nie są używane i mogą być usunięte.
Oto zapytanie o uzyskanie bieżących liczników skanowania wszystkich indeksów w schemacie 'public':
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
wyjście powinno wyglądać następująco:
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 wierszy)Rekreacja indeksów z mniejszą liczbą blokad
Często konieczne jest rekreowanie indeksów, na przykład gdy się powiększają, a rekreacja może przyspieszyć skanowanie. Indeksy mogą również ulegać uszkodzeniu. Zmiana parametrów indeksu może również wymagać jego rekreacji.
Włączamy równoległe tworzenie indeksów
W PostgreSQL 11 tworzenie indeksu B-Tree jest konkurencyjne. Aby przyspieszyć proces tworzenia, można wykorzystać kilku równolegle działających pracowników. Upewnij się jednak, że te parametry konfiguracyjne są ustawione poprawnie:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Wartości domyślne są zbyt małe. Idealnie, te liczby powinny być zwiększane wraz z liczbą rdzeni procesora. Więcej informacji znajdziesz w .
Tworzenie indeksów w tle
Możesz stworzyć indeks w tle, korzystając z parametru CONCURRENTLY poleceń CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXTa procedura tworzenia indeksu różni się od zwykłej tym, że nie wymaga blokowania tabeli, co oznacza, że nie blokuje operacji zapisu. Z drugiej strony zajmuje więcej czasu i zużywa więcej zasobów.
Postgres oferuje wiele elastycznych możliwości tworzenia indeksów oraz rozwiązywania wszelkich szczególnych przypadków, a także zapewnia sposoby zarządzania bazą danych na wypadek gwałtownie rosnącej popularności twojej aplikacji. Mamy nadzieję, że te wskazówki pomogą ci przyspieszyć zapytania i przygotować bazę do skalowania.
Źródło: habr.com
