Postgres dünyasında indekslər verilənlər bazası yaddaşının ("yığın" adlanır) səmərəli naviqasiyası üçün vacibdir. Postgres bunun üçün klasterləşdirməni dəstəkləmir və MVCC arxitekturası eyni çamadanın bir çox versiyası ilə nəticələnməyinizə səbəb olur. Buna görə də, tətbiqləri dəstəkləmək üçün səmərəli indekslər yarada və saxlaya bilmək çox vacibdir.
Burada indekslərin istifadəsini optimallaşdırmaq və təkmilləşdirmək üçün bəzi məsləhətlər verilmişdir.
Qeyd: aşağıda göstərilən sorğular dəyişdirilməmiş sorğu üzərində işləyir .
Örtmə İndekslərindən istifadə
Gəlin qeyri-aktiv istifadəçilər üçün e-poçt ünvanlarının çıxarılması sorğusuna baxaq. Cədvəl customer sütun var active, və sorğu sadədir:
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)
Sorğu cədvəlin tam skan ardıcıllığını çağırır customer. Sütun üzərində indeks yaradaq 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)
Bu kömək etdi, sonrakı tarama "index scan". Bu o deməkdir ki, Postgres indeksi skan edəcək "idx_cust1", və sonra digər sütunların dəyərlərini oxumaq üçün cədvəl yığınını axtarmağa davam edin (bu halda sütun email) sorğunun ehtiyac duyduğu.
Örtük indeksləri PostgreSQL 11-də təqdim olunur. Onlar indeksin özünə bir və ya daha çox əlavə sütun daxil etməyə imkan verir - onların dəyərləri indeks məlumat anbarında saxlanılır.
Əgər biz bu xüsusiyyətdən istifadə etsək və indeksin içinə e-poçt dəyərini əlavə etsək, Postgres-in dəyər üçün cədvəl yığınını axtarmasına ehtiyac qalmazdı. email. Bunun işləyəcəyini görək:
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' bizə deyir ki, sorğuya indi yalnız indeks lazımdır, bu, cədvəl yığınını oxumaq üçün bütün disklərin giriş/çıxışlarının qarşısını almağa kömək edir.
Örtük indeksləri hazırda yalnız B-ağacları üçün mövcuddur. Lakin, bu halda, təmir səyləri daha yüksək olacaq.
Qismən indekslərdən istifadə
Qismən indekslər cədvəldəki sətirlərin yalnız bir hissəsini indeksləşdirir. Bu, indekslərin ölçüsünü saxlayır və taramaları daha sürətli edir.
Tutaq ki, Kaliforniyadakı müştərilərimizin e-poçt ünvanlarının siyahısını əldə etmək istəyirik. Müraciət belə olacaq:
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)Adi indekslər bizə nə verəcək:
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)
Scan address indeks taraması ilə əvəz edilmişdir idx_address1və sonra yığını skan etdi address.
Bu, tez-tez edilən sorğu olduğundan və optimallaşdırılmalı olduğundan, biz yalnız rayonun ünvanı olan sətirləri indeksləşdirən qismən indeksdən istifadə edə bilərik. ‘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)
İndi sorğu yalnız oxunur idx_address2 və masaya toxunmur address.
Çox dəyərli indekslərdən istifadə
İndekslənəcək bəzi sütunlarda skalyar məlumat növü olmaya bilər. Sütun növləri kimi jsonb, arrays и tsvector kompozit və ya çoxlu dəyərləri ehtiva edir. Bu cür sütunları indeksləşdirməlisinizsə, adətən həmin sütunlardakı bütün fərdi dəyərləri axtarmalısınız.
Uğursuz çəkilişlərdən kəsiklər ehtiva edən bütün filmlərin adlarını tapmağa çalışaq. Cədvəl film adlı mətn sütunu var special_features. Əgər filmdə bu "xüsusi xüsusiyyət" varsa, o zaman sütun mətn massivi kimi elementi ehtiva edir Behind The Scenes. Bütün bu cür filmləri axtarmaq üçün "Pərdə arxasında" olan bütün sətirləri nə vaxt seçməliyik hər hansı bir massiv dəyərləri special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Yerləşdirmə operatoru @> sağ tərəfin sol tərəfin alt çoxluğu olub olmadığını yoxlayır.
Tələb 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)Hansı dəyəri 67 olan tam yığın skan tələb edir.
Adi bir B-ağac indeksinin bizə kömək edib-etmədiyini görək:
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)İndeks hətta nəzərə alınmadı. B-ağac indeksi indeksləşdirilmiş dəyərlərdə fərdi elementlərin mövcudluğundan xəbərdar deyil.
Bizə GIN indeksi lazımdır.
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 indeksi indeksləşdirilmiş kompozit dəyərlərə qarşı vahid dəyərlərin xəritələşdirilməsini dəstəkləyir, nəticədə sorğu planının dəyəri yarıdan çox azalır.
Dublikat indekslərdən xilas olmaq
İndekslər zamanla toplanır və bəzən yeni indeks əvvəlkilərdən biri ilə eyni tərifi ehtiva edə bilər. Siz indekslərin insan tərəfindən oxuna bilən SQL təriflərini əldə etmək üçün kataloq görünüşündən istifadə edə bilərsiniz. pg_indexes. Eyni tərifləri də asanlıqla tapa bilərsiniz:
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 indeksləri
Elə ola bilər ki, siz bir çox indekslə nəticələnə bilərsiniz, bunlardan biri digər indeksləri indeksləşdirən sütunların üst dəstini indeksləşdirir. Bu arzuolunan və ya olmaya da bilər - superset yalnız indeksli skanlarla nəticələnə bilər, bu yaxşıdır, lakin o, çox yer tuta bilər və ya supersetin optimallaşdırılması üçün nəzərdə tutulmuş sorğu artıq istifadə olunmur.
Bu cür indekslərin tərifini avtomatlaşdırmaq lazımdırsa, ondan başlaya bilərsiniz masadan pg_catalog.
İstifadə edilməmiş indekslər
Verilənlər bazasından istifadə edən proqramlar inkişaf etdikcə onların istifadə etdiyi sorğular da inkişaf edir. Əvvəllər əlavə edilmiş indekslər artıq heç bir sorğu tərəfindən istifadə edilə bilməz. İndeks hər dəfə skan edildikdə, o, statistika meneceri tərəfindən və sistem kataloqu görünüşündə qeyd olunur. pg_stat_user_indexes dəyərini görə bilərsiniz idx_scan, kumulyativ sayğacdır. Bu dəyərin müəyyən bir müddət ərzində izlənilməsi (məsələn, bir ay) hansı indekslərin istifadə olunmadığı və silinə biləcəyi haqqında yaxşı bir fikir verəcəkdir.
Bu, sxemdəki bütün indekslərin cari skan saylarını əldə etmək üçün bir sorğudur ‘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)Daha az kilidlə indekslərin yenidən qurulması
İndekslərin tez-tez yenidən qurulması lazımdır, məsələn, şişdikdə və yenidən qurulması taramanı sürətləndirə bilər. Həmçinin indekslər pozula bilər. İndeks parametrlərinin dəyişdirilməsi də onun yenidən qurulmasını tələb edə bilər.
Paralel indeks yaratmağı aktivləşdirin
PostgreSQL 11-də B-Tree indeksinin yaradılması paraleldir. Yaratma prosesini sürətləndirmək üçün bir neçə paralel işçi istifadə edilə bilər. Bununla belə, bu konfiqurasiya seçimlərinin düzgün qurulduğundan əmin olun:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Varsayılan dəyərlər çox kiçikdir. İdeal olaraq, bu rəqəmlər prosessor nüvələrinin sayı ilə birlikdə artmalıdır. Ətraflı oxu .
Fon indeksinin yaradılması
Seçimdən istifadə edərək fonda indeks yarada bilərsiniz CONCURRENTLY komandalar CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXBu indeks yaratma proseduru adi prosedurdan onunla fərqlənir ki, o, masada kilid tələb etmir və buna görə də yazma əməliyyatlarını bloklamır. Digər tərəfdən, daha çox vaxt tələb edir və daha çox resurs sərf edir.
Postgres indekslərin yaradılması və hər hansı xüsusi halların həlli yolları, həmçinin tətbiqinizin sürətlə böyüdüyü halda verilənlər bazasını idarə etməyin yolları üçün çoxlu çeviklik təmin edir. Ümid edirik ki, bu məsləhətlər sorğularınızı tez əldə etməyə və verilənlər bazanızı genişlənməyə hazır olmağa kömək edəcək.
Mənbə: www.habr.com