W świecie Postgresa indeksy są niezbędne do efektywnego poruszania się po magazynie bazy danych (zwanym „stertą”). Postgres nie obsługuje klastrowania, a architektura MVCC powoduje, że masz wiele wersji tej samej krotki. Dlatego bardzo ważna jest umiejętność tworzenia i utrzymywania wydajnych indeksów do obsługi aplikacji.
Oto kilka wskazówek dotyczących optymalizacji i ulepszania korzystania z indeksów.
Uwaga: zapytania pokazane poniżej działają na niezmodyfikowanym .
Korzystanie z indeksów obejmujących
Przyjrzyjmy się prośbie o wyodrębnienie adresów e-mail dla nieaktywnych użytkowników. Tabela customer jest kolumna active, a zapytanie jest proste:
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)
Zapytanie wywołuje sekwencję pełnego skanowania tabeli customer. Utwórzmy indeks na kolumnie 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)
Pomogło, kolejne skanowanie zmieniło się w „index scan". Oznacza to, że Postgres przeskanuje indeks „idx_cust1”, a następnie kontynuuj przeszukiwanie sterty tabeli, aby odczytać wartości innych kolumn (w tym przypadku kolumny email), których potrzebuje zapytanie.
Indeksy pokrywające zostały wprowadzone w PostgreSQL 11. Pozwalają zawrzeć jedną lub więcej dodatkowych kolumn w samym indeksie – ich wartości są przechowywane w magazynie danych indeksu.
Gdybyśmy użyli tej funkcji i dodali wartość e-mail do indeksu, Postgres nie musiałby przeszukiwać sterty tabeli w poszukiwaniu 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;
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' mówi nam, że zapytanie potrzebuje teraz tylko indeksu, co pomaga uniknąć wszystkich dyskowych operacji we/wy w celu odczytania sterty tabeli.
Indeksy obejmujące są obecnie dostępne tylko dla B-drzew. Jednak w tym przypadku nakłady na konserwację będą większe.
Używanie indeksów częściowych
Indeksy częściowe indeksują tylko podzbiór wierszy w tabeli. Oszczędza to rozmiar indeksów i przyspiesza skanowanie.
Załóżmy, że chcemy uzyskać listę adresów e-mail naszych klientów w Kalifornii. Żądanie będzie wyglądać następująco:
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)Co dadzą nam zwykłe indeksy:
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)
Skanuj address został zastąpiony skanowaniem indeksu idx_address1a następnie przeskanowałem stertę address.
Ponieważ jest to częste zapytanie i wymaga optymalizacji, 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';
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)
Teraz zapytanie tylko czyta idx_address2 i nie dotyka stołu address.
Używanie indeksów wielowartościowych
Niektóre kolumny do indeksowania mogą nie zawierać skalarnego typu danych. Typy kolumn, takie jak jsonb, arrays и tsvector zawierać wartości złożone lub wielokrotne. Jeśli potrzebujesz zindeksować takie kolumny, zwykle musisz przeszukać wszystkie poszczególne wartości w tych kolumnach.
Spróbujmy znaleźć tytuły wszystkich filmów zawierających fragmenty nieudanych ujęć. Tabela film istnieje kolumna tekstowa o nazwie special_features. Jeśli film ma tę „specjalną właściwość”, wówczas kolumna zawiera element jako tablicę tekstową Behind The Scenes. Aby wyszukać wszystkie takie filmy, musimy zaznaczyć wszystkie wiersze z „Za kulisami”, kiedy dowolny wartości tablicy special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator zagnieżdżania @> sprawdza, czy prawa strona jest podzbiorem lewej strony.
Poproś o 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)Który żąda pełnego skanowania sterty z kosztem 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 nie był nawet brany pod uwagę. Indeks B-drzewa nie jest świadomy istnienia poszczególnych 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 obsługuje mapowanie pojedynczych wartości na zindeksowane wartości złożone, co skutkuje zmniejszeniem o ponad połowę kosztu planu zapytań.
Pozbycie się zduplikowanych indeksów
Indeksy gromadzą się w czasie, a czasami nowy indeks może zawierać tę samą definicję, co jeden z poprzednich. Możesz użyć widoku katalogu, aby uzyskać czytelne dla człowieka definicje indeksów SQL. pg_indexes. Możesz również łatwo znaleźć identyczne definicje:
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)
Indeksy supersetów
Może się zdarzyć, że otrzymasz wiele indeksów, z których jeden indeksuje nadzbiór kolumn indeksujących inne indeksy. Może to być pożądane lub nie — nadzbiór może skutkować skanowaniem samego indeksu, co jest dobre, ale może zajmować zbyt dużo miejsca lub zapytanie, które nadzbiór miał zoptymalizować, nie jest już używane.
Jeśli potrzebujesz zautomatyzować definiowanie takich indeksów, możesz zacząć od ze stołu pg_catalog.
Nieużywane indeksy
Wraz z ewolucją aplikacji korzystających z baz danych zmieniają się używane przez nie zapytania. Indeksy dodane wcześniej nie mogą już być używane przez żadne zapytanie. Każdorazowe skanowanie indeksu jest oznaczane przez menedżera statystyk oraz w widoku katalogu systemowego pg_stat_user_indexes możesz zobaczyć wartość idx_scan, który jest licznikiem kumulatywnym. Śledzenie tej wartości przez pewien okres czasu (powiedzmy miesiąc) da dobre wyobrażenie o tym, które indeksy nie są używane i mogą zostać porzucone.
Oto zapytanie, aby uzyskać bieżące liczby skanów wszystkich indeksów w schemacie ‘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)Odbudowywanie indeksów z mniejszą liczbą blokad
Indeksy często wymagają przebudowy, na przykład gdy stają się nadęte, a odbudowa może przyspieszyć skanowanie. Również indeksy mogą ulec uszkodzeniu. Zmiana parametrów indeksu może również wymagać jego przebudowania.
Włącz równoległe tworzenie indeksu
W PostgreSQL 11 tworzenie indeksu B-drzewa odbywa się równolegle. Aby przyspieszyć proces tworzenia, można użyć kilku równoległych procesów roboczych. Upewnij się jednak, że te opcje konfiguracji są ustawione poprawnie:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Wartości domyślne są za małe. W idealnym przypadku liczby te powinny rosnąć wraz z liczbą rdzeni procesora. Czytaj więcej w .
Tworzenie indeksu w tle
Możesz utworzyć indeks w tle za pomocą opcji CONCURRENTLY polecenia 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 blokady tabeli, a zatem nie blokuje operacji zapisu. Z drugiej strony zajmuje to więcej czasu i pochłania więcej zasobów.
Postgres zapewnia dużą elastyczność tworzenia indeksów i sposobów rozwiązywania wszelkich specjalnych przypadków, a także sposoby zarządzania bazą danych w przypadku gwałtownego wzrostu aplikacji. Mamy nadzieję, że te wskazówki pomogą Ci szybko uzyskać zapytania i przygotować bazę danych do skalowania.
Źródło: www.habr.com