Dalam dunia Postgres, indeks adalah penting untuk navigasi yang cekap bagi storan pangkalan data (dipanggil "timbunan"). Postgres tidak menyokong pengelompokan untuknya, dan seni bina MVCC menyebabkan anda mendapat banyak versi tuple yang sama. Oleh itu, adalah sangat penting untuk dapat mencipta dan mengekalkan indeks yang cekap untuk menyokong aplikasi.
Berikut ialah beberapa petua untuk mengoptimumkan dan meningkatkan penggunaan indeks.
Nota: pertanyaan yang ditunjukkan di bawah berfungsi pada yang tidak diubah suai .
Menggunakan Indeks Meliputi
Mari lihat permintaan untuk mengekstrak alamat e-mel untuk pengguna yang tidak aktif. Jadual customer terdapat lajur active, dan pertanyaannya mudah:
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)
Pertanyaan menggunakan urutan imbasan jadual penuh customer. Mari buat indeks pada lajur 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)
Ia membantu, imbasan seterusnya bertukar menjadi "index scan". Ini bermakna Postgres akan mengimbas indeks "idx_cust1", dan kemudian teruskan mencari timbunan jadual untuk membaca nilai lajur lain (dalam kes ini, lajur email) yang diperlukan oleh pertanyaan.
Meliputi indeks diperkenalkan dalam PostgreSQL 11. Mereka membenarkan anda memasukkan satu atau lebih lajur tambahan dalam indeks itu sendiri - nilainya disimpan dalam stor data indeks.
Jika kami memanfaatkan ciri ini dan menambah nilai e-mel di dalam indeks, maka Postgres tidak perlu mencari timbunan jadual untuk nilai tersebut. email. Mari lihat jika ini akan berfungsi:
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' memberitahu kami bahawa pertanyaan kini hanya memerlukan indeks, yang membantu mengelakkan semua cakera I/O untuk membaca timbunan jadual.
Indeks penutup pada masa ini hanya tersedia untuk pokok B. Walau bagaimanapun, dalam kes ini, usaha penyelenggaraan akan lebih tinggi.
Menggunakan Indeks Separa
Indeks separa mengindeks hanya subset baris dalam jadual. Ini menjimatkan saiz indeks dan menjadikan imbasan lebih cepat.
Katakan kami ingin mendapatkan senarai alamat e-mel pelanggan kami di California. Permintaan akan menjadi seperti ini:
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)Apakah indeks biasa akan memberi kita:
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)
Imbas address telah digantikan dengan imbasan indeks idx_address1dan kemudian mengimbas timbunan address.
Memandangkan ini adalah pertanyaan yang kerap dan perlu dioptimumkan, kami boleh menggunakan indeks separa, yang mengindeks hanya baris tersebut dengan alamat di mana daerah β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)
Sekarang pertanyaan hanya dibaca idx_address2 dan tidak menyentuh meja address.
Menggunakan Indeks Berbilang Nilai
Sesetengah lajur untuk diindeks mungkin tidak mengandungi jenis data skalar. Jenis lajur seperti jsonb, arrays ΠΈ tsvector mengandungi nilai komposit atau berbilang. Jika anda perlu mengindeks lajur sedemikian, anda biasanya perlu mencari semua nilai individu dalam lajur tersebut.
Mari cuba cari tajuk semua filem yang mengandungi potongan daripada pengambilan yang tidak berjaya. Jadual film terdapat lajur teks dipanggil special_features. Jika filem mempunyai "harta khas" ini, maka lajur mengandungi elemen sebagai tatasusunan teks Behind The Scenes. Untuk mencari semua filem sedemikian, kita perlu memilih semua baris dengan "Behind The Scenes" bila mana-mana nilai tatasusunan special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator bersarang @> menyemak sama ada bahagian kanan ialah subset dari bahagian kiri.
Permintaan pelan:
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)Yang meminta imbasan timbunan penuh dengan kos 67.
Mari lihat sama ada indeks B-tree biasa membantu kita:
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 itu tidak diambil kira. Indeks B-tree tidak menyedari kewujudan elemen individu dalam nilai yang diindeks.
Kami memerlukan indeks 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 menyokong pemetaan nilai tunggal terhadap nilai komposit yang diindeks, menghasilkan kos pelan pertanyaan yang lebih daripada separuh.
Menghapuskan indeks pendua
Indeks terkumpul dari semasa ke semasa, dan kadangkala indeks baharu mungkin mengandungi takrifan yang sama seperti salah satu daripada yang sebelumnya. Anda boleh menggunakan paparan katalog untuk mendapatkan definisi indeks SQL yang boleh dibaca manusia. pg_indexes. Anda juga boleh mencari definisi yang sama dengan mudah:
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)
Indeks Superset
Ia boleh berlaku bahawa anda mempunyai banyak indeks, salah satunya mengindeks superset lajur yang mengindeks indeks lain. Ini mungkin atau mungkin tidak diinginiβsuperset mungkin menghasilkan imbasan indeks sahaja, yang bagus, tetapi ia mungkin mengambil terlalu banyak ruang, atau pertanyaan yang ingin dioptimumkan oleh superset tidak lagi digunakan.
Jika anda perlu mengautomasikan definisi indeks tersebut, anda boleh mulakan dengan daripada meja pg_catalog.
Indeks yang tidak digunakan
Apabila aplikasi yang menggunakan pangkalan data berkembang, begitu juga pertanyaan yang mereka gunakan. Indeks yang ditambahkan sebelum ini tidak lagi boleh digunakan oleh sebarang pertanyaan. Setiap kali indeks diimbas, ia ditandakan oleh pengurus statistik dan dalam paparan katalog sistem pg_stat_user_indexes anda boleh melihat nilainya idx_scan, yang merupakan kaunter terkumpul. Penjejakan nilai ini dalam tempoh masa (katakan sebulan) akan memberikan gambaran yang baik tentang indeks yang tidak digunakan dan boleh digugurkan.
Berikut ialah pertanyaan untuk mendapatkan kiraan imbasan semasa semua indeks dalam skema β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)Membina semula indeks dengan lebih sedikit kunci
Indeks selalunya perlu dibina semula, contohnya apabila ia menjadi kembung, dan pembinaan semula boleh mempercepatkan imbasan. Juga indeks boleh rosak. Menukar parameter indeks juga mungkin memerlukan pembinaan semula.
Dayakan penciptaan indeks selari
Dalam PostgreSQL 11, mencipta indeks B-Tree adalah serentak. Untuk mempercepatkan proses penciptaan, beberapa pekerja selari boleh digunakan. Walau bagaimanapun, pastikan pilihan konfigurasi ini ditetapkan dengan betul:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Nilai lalai terlalu kecil. Sebaik-baiknya, angka ini harus meningkat bersama-sama dengan bilangan teras pemproses. Baca lebih lanjut dalam .
Penciptaan indeks latar belakang
Anda boleh membuat indeks di latar belakang menggunakan pilihan CONCURRENTLY pasukan CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXProsedur penciptaan indeks ini berbeza daripada yang biasa kerana ia tidak memerlukan kunci di atas meja, dan oleh itu tidak menyekat operasi tulis. Sebaliknya, ia mengambil lebih banyak masa dan menggunakan lebih banyak sumber.
Postgres menyediakan banyak fleksibiliti untuk mencipta indeks dan cara untuk menyelesaikan sebarang kes khas, serta cara untuk mengurus pangkalan data sekiranya aplikasi anda berkembang dengan pesat. Kami berharap petua ini akan membantu anda mendapatkan pertanyaan anda dengan cepat dan pangkalan data anda bersedia untuk skala.
Sumber: www.habr.com