Postgres dünyasında dizinler, bir veritabanının deposunda ("yığın" olarak adlandırılır) verimli gezinme için gereklidir. Postgres bunun için kümelemeyi desteklemez ve MVCC mimarisi, aynı demetin birçok sürümüyle sonuçlanmanıza neden olur. Bu nedenle, uygulamaları desteklemek için verimli dizinler oluşturabilmek ve sürdürebilmek çok önemlidir.
Dizinlerin kullanımını optimize etmek ve iyileştirmek için bazı ipuçlarını burada bulabilirsiniz.
Not: Aşağıda gösterilen sorgular, değiştirilmemiş bir .
Kaplama İndekslerini Kullanma
Etkin olmayan kullanıcılar için e-posta adreslerini ayıklama isteğine bakalım. Masa customer bir sütun var activeve sorgu basittir:
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)
Sorgu, tam tablo tarama sırasını çağırır customer. Bir sütunda bir dizin oluşturalım 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)
Yardımcı oldu, sonraki tarama "index scan". Bu, Postgres'in dizini tarayacağı anlamına gelir "idx_cust1" ve ardından diğer sütunların değerlerini okumak için tablo yığınını aramaya devam edin (bu durumda, sütun email) sorgunun ihtiyaç duyduğu.
Kaplama dizinleri PostgreSQL 11'de tanıtılmıştır. Dizinin kendisine bir veya daha fazla ek sütun eklemenize izin verirler - değerleri dizin veri deposunda saklanır.
Bu özelliği kullanırsak ve e-posta değerini dizinin içine eklersek, Postgres'in değer için tablonun yığınını aramasına gerek kalmaz. email. Bunun işe yarayıp yaramadığını görelim:
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' bize sorgunun artık yalnızca bir dizine ihtiyacı olduğunu söyler, bu da tüm disk G/Ç'lerinin tablo yığınını okumasını önlemeye yardımcı olur.
Kaplama indeksleri şu anda yalnızca B-ağaçları için mevcuttur. Ancak bu durumda bakım eforu daha yüksek olacaktır.
Kısmi İndeksleri Kullanma
Kısmi indeksler, tablodaki satırların yalnızca bir alt kümesini indeksler. Bu, dizinlerin boyutundan tasarruf sağlar ve taramaları daha hızlı hale getirir.
Diyelim ki Kaliforniya'daki müşterilerimizin e-posta adreslerinin bir listesini almak istiyoruz. İstek şu şekilde olacaktır:
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)Hangi sıradan indeksler bize verecek:
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)
taramak address indeks taraması ile değiştirildi idx_address1ve sonra yığını taradı address.
Bu sık yapılan bir sorgu olduğundan ve optimize edilmesi gerektiğinden, yalnızca ilçenin bulunduğu adresleri içeren satırları indeksleyen kısmi bir indeks kullanabiliriz. ‘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)
Şimdi sorgu sadece okur idx_address2 ve masaya dokunmaz address.
Çok Değerli Dizinleri Kullanma
İndekslenecek bazı sütunlar, skaler veri tipi içermeyebilir. gibi sütun türleri jsonb, arrays и tsvector bileşik veya çoklu değerler içerir. Bu tür sütunları dizine eklemeniz gerekirse, genellikle bu sütunlardaki tüm değerleri tek tek aramanız gerekir.
Başarısız çekimlerden kesitler içeren tüm filmlerin adlarını bulmaya çalışalım. Masa film adlı bir metin sütunu vardır. special_features. Filmde bu "özel özellik" varsa sütun, öğeyi bir metin dizisi olarak içerir Behind The Scenes. Tüm bu tür filmleri aramak için, "Kamera Arkası" ile tüm satırları seçmemiz gerekir. herhangi dizi değerleri special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Yerleştirme operatörü @> sağ tarafın sol tarafın bir alt kümesi olup olmadığını kontrol eder.
Talep 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)Bu da 67 maliyetle tam bir yığın taraması talep ediyor.
Sıradan bir B-ağacı indeksinin bize yardımcı olup olmadığına bakalım:
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)Endeks dikkate bile alınmadı. B-tree indeksi, indekslenmiş değerlerde bireysel öğelerin varlığından haberdar değildir.
Bir GIN indeksine ihtiyacımız var.
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 dizini, tek değerlerin dizine alınmış bileşik değerlere göre eşlenmesini destekler, bu da yarıdan fazla olan bir sorgu planı maliyetiyle sonuçlanır.
Yinelenen dizinlerden kurtulma
Dizinler zamanla birikir ve bazen yeni bir dizin öncekilerle aynı tanımı içerebilir. Dizinlerin okunabilir SQL tanımlarını almak için katalog görünümünü kullanabilirsiniz. pg_indexes. Aynı tanımları da kolayca bulabilirsiniz:
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)
Süper Küme İndeksleri
Sonunda, biri diğer indeksleri indeksleyen sütunların bir üst kümesini indeksleyen birçok indeksle karşılaşabilirsiniz. Bu istenebilir veya istenmeyebilir; üst küme yalnızca dizin taramalarına neden olabilir, bu iyidir, ancak çok fazla yer kaplayabilir veya üst kümenin optimize etmesi amaçlanan sorgu artık kullanılmaz.
Bu tür dizinlerin tanımını otomatikleştirmeniz gerekiyorsa, şununla başlayabilirsiniz: masadan pg_catalog.
Kullanılmayan indeksler
Veritabanlarını kullanan uygulamalar geliştikçe, kullandıkları sorgular da gelişir. Daha önce eklenen dizinler artık herhangi bir sorgu tarafından kullanılamayabilir. Bir dizin her tarandığında, istatistik yöneticisi tarafından ve sistem kataloğu görünümünde işaretlenir. pg_stat_user_indexes değeri görebilirsin idx_scan, kümülatif bir sayaçtır. Bu değeri belirli bir süre boyunca (diyelim ki bir ay) takip etmek, hangi indekslerin kullanılmadığı ve düşürülebileceği konusunda iyi bir fikir verecektir.
İşte şemadaki tüm dizinlerin geçerli tarama sayılarını almak için bir sorgu ‘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)Dizinleri daha az kilitle yeniden oluşturma
Dizinler genellikle, örneğin şiştiklerinde yeniden oluşturulmalıdır ve yeniden oluşturma, taramayı hızlandırabilir. Ayrıca dizinler bozulabilir. Dizin parametrelerinin değiştirilmesi, yeniden oluşturulmasını da gerektirebilir.
Paralel dizin oluşturmayı etkinleştir
PostgreSQL 11'de bir B-Tree dizini oluşturmak eşzamanlıdır. Oluşturma sürecini hızlandırmak için birkaç paralel çalışan kullanılabilir. Ancak, bu yapılandırma seçeneklerinin doğru ayarlandığından emin olun:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Varsayılan değerler çok küçük. İdeal olarak, bu sayıların işlemci çekirdeği sayısıyla birlikte artması gerekir. Daha fazla oku .
Arka planda dizin oluşturma
Seçeneğini kullanarak arka planda bir dizin oluşturabilirsiniz. CONCURRENTLY komutlar CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXBu dizin oluşturma prosedürü, tabloda bir kilit gerektirmemesi ve bu nedenle yazma işlemlerini engellememesi bakımından normalden farklıdır. Öte yandan, daha fazla zaman alır ve daha fazla kaynak tüketir.
Postgres, dizin oluşturmak için çok fazla esneklik ve herhangi bir özel durumu çözmenin yanı sıra, uygulamanızın hızla büyümesi durumunda veritabanını yönetmenin yollarını sunar. Bu ipuçlarının, sorgularınızı hızlı bir şekilde yapmanıza ve veritabanınızı ölçeklendirmeye hazır hale getirmenize yardımcı olacağını umuyoruz.
Kaynak: habr.com