Menggunakan semua fitur indeks di PostgreSQL

Di dunia Postgres, indeks sangat penting untuk menavigasi repositori basis data secara efisien (disebut heap, heap). Postgres tidak mendukung pengelompokan untuk itu, dan arsitektur MVCC menyebabkan Anda mengumpulkan banyak versi dari tuple yang sama. Oleh karena itu, sangat penting untuk dapat membuat dan memelihara indeks yang efektif untuk mendukung aplikasi.

Berikut adalah beberapa kiat untuk mengoptimalkan dan meningkatkan penggunaan indeks.

Catatan: kueri yang ditampilkan di bawah ini berfungsi pada database sampel pagila yang tidak dimodifikasi.

Menggunakan Covering Indexes

Mari kita tinjau permintaan untuk mengambil alamat email untuk pengguna yang tidak aktif. Tabel customer memiliki kolom active , dan permintaannya 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) 

Permintaan meminta urutan lengkap pemindaian tabel customer . Mari kita buat indeks untuk 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) 

Ini membantu, pemindaian selanjutnya berubah menjadi " index scan ". Ini berarti Postgres akan memindai indeks idx_cust1 , dan kemudian melanjutkan mencari tumpukan tabel untuk membaca nilai-nilai kolom lain (dalam hal ini, kolom email ) yang dibutuhkan oleh kueri.

PostgreSQL 11 diperkenalkan meliputi indeks. Mereka memungkinkan Anda untuk memasukkan satu atau lebih kolom tambahan dalam indeks itu sendiri - nilainya disimpan dalam penyimpanan data indeks.

Jika kami menggunakan fitur ini dan menambahkan nilai email di dalam indeks, maka Postgres tidak perlu mencari nilai email di tumpukan tabel. Mari kita lihat apakah ini bekerja:

 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 kita bahwa permintaan sekarang hanya membutuhkan satu indeks, yang membantu menghindari semua disk I / O untuk membaca tumpukan tabel.

Hari ini, indeks penutupan hanya tersedia untuk pohon-B. Namun, dalam hal ini, upaya pengawalan akan lebih tinggi.

Menggunakan indeks parsial

Indeks parsial hanya mengindeks sebagian dari baris dalam tabel. Ini menghemat ukuran indeks dan pemindaian lebih cepat.

Misalkan kita perlu mendapatkan daftar alamat email dari pelanggan California kami. Permintaan 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 biasa diberikan indeks kepada kami:

 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) 

Pemindaian address digantikan oleh idx_address1 indeks idx_address1 , dan kemudian tumpukan address dipindai.

Karena ini adalah kueri yang sering dan perlu dioptimalkan, kami dapat menggunakan indeks parsial yang hanya mengindeks baris-baris dengan alamat di mana wilayah '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 permintaan hanya membaca idx_address2 dan tidak menyentuh tabel address .

Menggunakan Indeks Multi-Nilai

Beberapa kolom yang perlu diindeks mungkin tidak mengandung tipe data skalar. Jenis jsonb seperti jsonb , arrays dan tsvector berisi beberapa nilai. Jika Anda perlu mengindeks kolom tersebut, Anda biasanya harus mencari semua nilai individual di kolom ini.

Mari kita coba menemukan nama-nama semua film yang berisi potongan dari pengambilan yang tidak berhasil. Tabel film memiliki kolom teks yang disebut special_features . Jika film memiliki "properti khusus" ini, maka kolom berisi elemen dalam bentuk array teks Behind The Scenes . Untuk mencari semua film semacam itu, kita perlu memilih semua baris dengan "Di Balik Layar" untuk nilai apa pun dari array special_features :

 SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}'; 

Operator kontainmen @> memeriksa untuk melihat apakah sisi kanan adalah subset dari sisi kiri.

Paket 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 reguler 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 menyadari 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) 

GIN-index mendukung perbandingan nilai individu dengan nilai komposit terindeks, sebagai akibatnya, biaya rencana kueri berkurang lebih dari setengah.

Singkirkan indeks rangkap

Indeks terakumulasi dari waktu ke waktu, dan terkadang indeks baru mungkin berisi definisi yang sama dengan yang sebelumnya. Untuk mendapatkan definisi indeks yang dapat dibaca manusia, Anda dapat menggunakan pg_indexes tampilan katalog. Anda juga dapat dengan mudah menemukan definisi yang sama:

  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

Mungkin terjadi bahwa Anda mengumpulkan banyak indeks, salah satunya indeks bagian dari kolom yang indeks indeks lainnya. Ini bisa baik diinginkan atau tidak - superset hanya dapat memindai berdasarkan indeks, yang bagus, tetapi dapat memakan banyak ruang, atau permintaan yang superset ini dimaksudkan untuk dioptimalkan tidak lagi digunakan.

Jika Anda perlu mengotomatiskan definisi indeks tersebut, Anda bisa mulai dengan pg_index dari tabel pg_catalog .

Indeks yang Tidak Digunakan

Saat aplikasi yang menggunakan basis data berkembang, demikian juga kueri yang mereka gunakan. Indeks yang ditambahkan sebelumnya mungkin tidak lagi digunakan oleh permintaan apa pun. Setiap kali indeks dipindai, itu ditandai oleh manajer statistik, dan dalam pg_stat_user_indexes katalog sistem pg_stat_user_indexes Anda dapat melihat nilai idx_scan , yang merupakan penghitung kumulatif. Melacak nilai ini selama periode waktu tertentu (katakanlah, sebulan) akan memberikan ide bagus tentang indeks mana yang tidak digunakan dan dapat dihapus.

Berikut adalah permintaan 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) 

Buat kembali indeks dengan kunci lebih sedikit

Seringkali indeks harus dibuat ulang, misalnya ketika ukurannya digelembungkan, dan pembuatan ulang dapat mempercepat pemindaian. Juga, indeks mungkin rusak. Mengubah parameter indeks mungkin juga perlu membuatnya kembali.

Aktifkan pembuatan indeks paralel

Di PostgreSQL 11, membuat indeks B-Tree kompetitif. Untuk mempercepat proses pembuatan, beberapa pekerja paralel dapat digunakan. Namun, pastikan bahwa parameter konfigurasi ini diatur dengan benar:

 SET max_parallel_workers = 32; SET max_parallel_maintenance_workers = 16; 

Nilai default terlalu kecil. Idealnya, angka-angka ini harus ditambah seiring dengan jumlah inti prosesor. Baca dokumentasi untuk lebih jelasnya.

Pembuatan Indeks Latar Belakang

Anda dapat membuat indeks di latar belakang menggunakan parameter CONCURRENTLY dari perintah CREATE INDEX :

 pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district); CREATE INDEX 

Prosedur pembuatan indeks ini berbeda dari yang biasa karena tidak memerlukan penguncian tabel, dan karenanya tidak memblokir operasi penulisan. Di sisi lain, dibutuhkan lebih banyak waktu dan menghabiskan lebih banyak sumber daya.

Postgres menyediakan banyak opsi fleksibel untuk membuat indeks dan cara untuk menyelesaikan kasus tertentu, serta menyediakan cara untuk mengelola basis data jika terjadi ledakan pertumbuhan aplikasi Anda. Kami harap tips ini akan membantu Anda membuat kueri Anda lebih cepat dan database Anda siap untuk ditingkatkan.