Di dunia Postgres, indeks sangat penting untuk navigasi penyimpanan database yang efisien (disebut "tumpukan"). Postgres tidak mendukung pengelompokan untuk itu, dan arsitektur MVCC menyebabkan Anda berakhir dengan banyak versi dari tuple yang sama. Oleh karena itu, sangat penting untuk dapat membuat dan memelihara indeks yang efisien untuk mendukung aplikasi.
Berikut adalah beberapa tips untuk mengoptimalkan dan meningkatkan penggunaan indeks.
Catatan: kueri yang ditampilkan di bawah berfungsi pada yang tidak dimodifikasi .
Menggunakan Indeks Penutup
Mari kita lihat permintaan untuk mengekstrak alamat email bagi pengguna yang tidak aktif. Meja customer ada kolom active, dan kuerinya sederhana:
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)
Kueri memanggil urutan pemindaian tabel lengkap customer. Mari buat indeks pada kolom 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)
Itu membantu, pemindaian selanjutnya berubah menjadi "index scan". Ini berarti Postgres akan memindai indeks "idx_cust1", lalu lanjutkan mencari tumpukan tabel untuk membaca nilai kolom lain (dalam hal ini, kolom email) yang dibutuhkan kueri.
Meliputi indeks diperkenalkan di PostgreSQL 11. Mereka memungkinkan Anda untuk memasukkan satu atau lebih kolom tambahan dalam indeks itu sendiri - nilainya disimpan di penyimpanan data indeks.
Jika kita memanfaatkan fitur ini dan menambahkan nilai email di dalam indeks, maka Postgres tidak perlu mencari nilainya di tumpukan tabel. email. Mari kita lihat apakah ini akan berhasil:
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' memberi tahu kami bahwa kueri sekarang hanya memerlukan indeks, yang membantu menghindari semua I/O disk untuk membaca tumpukan tabel.
Covering index saat ini hanya tersedia untuk B-tree. Namun, dalam hal ini, upaya pemeliharaan akan lebih tinggi.
Menggunakan Indeks Parsial
Indeks parsial mengindeks hanya subset dari baris dalam tabel. Ini menghemat ukuran indeks dan mempercepat pemindaian.
Katakanlah kita ingin mendapatkan daftar alamat email pelanggan kita di California. Permintaannya akan 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)Apa yang akan diberikan indeks biasa kepada 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)
Pindai address telah digantikan oleh pemindaian indeks idx_address1dan kemudian memindai tumpukan address.
Karena ini adalah kueri yang sering dan perlu dioptimalkan, kita dapat menggunakan indeks parsial, yang hanya mengindeks baris-baris dengan alamat di mana distrik β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 kueri hanya membaca idx_address2 dan tidak menyentuh meja address.
Menggunakan Indeks Multi-Nilai
Beberapa kolom yang akan diindeks mungkin tidak berisi tipe data skalar. Jenis kolom seperti jsonb, arrays ΠΈ tsvector mengandung komposit atau beberapa nilai. Jika Anda perlu mengindeks kolom seperti itu, Anda biasanya harus mencari semua nilai individual di kolom tersebut.
Mari kita coba temukan judul semua film yang berisi potongan dari pengambilan yang gagal. Meja film ada kolom teks yang disebut special_features. Jika film memiliki "properti khusus" ini, maka kolom berisi elemen sebagai larik teks Behind The Scenes. Untuk mencari semua film seperti itu, kita perlu memilih semua baris dengan "Behind The Scenes" kapan apapun nilai larik special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
Operator bersarang @> memeriksa apakah sisi kanan adalah bagian dari sisi kiri.
Rencana permintaan:
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 pemindaian tumpukan penuh dengan biaya 67.
Mari kita lihat apakah 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 bahkan tidak dipertimbangkan. Indeks B-tree tidak mengetahui keberadaan elemen individu dalam nilai yang diindeks.
Kami membutuhkan 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 mendukung pemetaan nilai tunggal terhadap nilai komposit yang diindeks, menghasilkan biaya paket kueri yang lebih dari setengahnya.
Menyingkirkan indeks duplikat
Indeks terakumulasi dari waktu ke waktu, dan terkadang indeks baru mungkin berisi definisi yang sama dengan yang sebelumnya. Anda dapat menggunakan tampilan katalog untuk mendapatkan definisi indeks SQL yang dapat dibaca manusia. pg_indexes. Anda juga dapat dengan mudah menemukan definisi yang identik:
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
Bisa jadi Anda berakhir dengan banyak indeks, salah satunya mengindeks superset kolom yang mengindeks indeks lainnya. Ini mungkin atau mungkin tidak diinginkanβsuperset dapat menghasilkan pemindaian hanya indeks, yang bagus, tetapi mungkin memakan terlalu banyak ruang, atau kueri yang dimaksudkan untuk dioptimalkan oleh superset tidak lagi digunakan.
Jika Anda perlu mengotomatiskan definisi indeks tersebut, Anda dapat memulainya dari meja pg_catalog.
Indeks yang tidak digunakan
Saat aplikasi yang menggunakan database berkembang, kueri yang mereka gunakan juga berkembang. Indeks yang ditambahkan sebelumnya mungkin tidak lagi digunakan oleh kueri apa pun. Setiap kali indeks dipindai, itu ditandai oleh manajer statistik, dan dalam tampilan katalog sistem pg_stat_user_indexes Anda bisa melihat nilainya idx_scan, yang merupakan penghitung kumulatif. Melacak nilai ini selama periode waktu tertentu (katakanlah sebulan) akan memberikan gambaran yang baik tentang indeks mana yang tidak digunakan dan dapat dihapus.
Berikut adalah kueri untuk mendapatkan jumlah pemindaian saat ini dari 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)Membangun kembali indeks dengan lebih sedikit kunci
Indeks seringkali perlu dibangun kembali, misalnya ketika menjadi kembung, dan pembangunan kembali dapat mempercepat pemindaian. Juga indeks bisa rusak. Mengubah parameter indeks mungkin juga memerlukan pembangunan kembali.
Aktifkan pembuatan indeks paralel
Di PostgreSQL 11, membuat indeks B-Tree dilakukan bersamaan. Untuk mempercepat proses pembuatan, beberapa pekerja paralel dapat digunakan. Namun, pastikan opsi konfigurasi ini disetel dengan benar:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Nilai default terlalu kecil. Idealnya, angka-angka ini harus meningkat seiring dengan jumlah inti prosesor. Baca selengkapnya di .
Pembuatan indeks latar belakang
Anda dapat membuat indeks di latar belakang menggunakan opsi CONCURRENTLY tim CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXProsedur pembuatan indeks ini berbeda dari yang biasa karena tidak memerlukan kunci pada tabel, dan oleh karena itu tidak memblokir operasi tulis. Di sisi lain, dibutuhkan lebih banyak waktu dan menghabiskan lebih banyak sumber daya.
Postgres memberikan banyak fleksibilitas untuk membuat indeks dan cara untuk menyelesaikan kasus khusus apa pun, serta cara untuk mengelola database jika aplikasi Anda berkembang pesat. Kami harap tips ini akan membantu Anda mendapatkan kueri dengan cepat dan basis data Anda siap untuk diskalakan.
Sumber: www.habr.com