Indeks penutup bukan hanya fitur lain yang mungkin berguna. Hal ini murni praktis. Tanpa mereka, Index Only Scan tidak akan memberikan kemenangan. Meskipun indeks penutup dalam situasi yang berbeda efektif dengan cara yang berbeda.
Ini sebenarnya bukan tentang membahas indeks: sebenarnya, indeks inklusif telah muncul di Postgres. Namun, secara berurutan: indeks penutup adalah indeks yang berisi semua nilai kolom yang diperlukan oleh kueri; Namun, akses ke tabel itu sendiri tidak lagi diperlukan. Hampir. Anda dapat
membaca tentang "hampir" dan nuansa lain dalam sebuah artikel oleh
Yegor Rogov , termasuk dalam seri indeksnya 10 (!) Bagian. Dan
indeks inklusif dibuat khusus untuk pencarian pada permintaan tipikal: nilai bidang yang tidak dapat dicari ditambahkan ke indeks pencarian, mereka diperlukan hanya agar tidak merujuk ke tabel lagi. Indeks tersebut dibentuk dengan kata kunci TERMASUK.
Anastasia Lubennikova (Postgres Professional) menyelesaikan metode btree sehingga kolom tambahan dapat dimasukkan dalam indeks. Patch ini dimasukkan dalam PostgreSQL 11. Tetapi patch untuk metode akses GiST / SP-GiST tidak punya waktu untuk matang sebelum rilis versi ini. Pada 12 GiST matang.
Keinginan konstruktif untuk memiliki indeks inklusif untuk GiST muncul sejak lama: tes tempel oleh Andrey Borodin
ditawarkan kepada masyarakat pada pertengahan April 2018. Dia melakukan semua pekerjaan dasar, sangat sulit.
Pada awal Agustus 2019, Alexander Korotkov menambahkan perbaikan kosmetik dan melakukan perbaikan.
Untuk demonstrasi dan penelitian, kami akan menghasilkan satu set 3 juta persegi panjang. Pada saat yang sama beberapa kata tentang tipe kotak, karena tidak semua manipulasi dengan itu adalah intuitif.
Jenis kotak - yaitu, persegi panjang - telah lama berada di Postgres, didefinisikan oleh 2 titik (titik tipe geometris) - simpul yang berlawanan dari persegi panjang (yaitu, persegi panjang tidak dapat miring, berserakan ke samping). Kita membaca dalam
dokumentasi : “nilai-nilai kotak jenis ditulis dalam salah satu bentuk berikut:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
Dalam praktiknya, Anda harus menulis, katakanlah, seperti ini:
SELECT box('1,2', '3,4'); box
Pertama, Postgres menunjukkan verteks kanan atas, lalu kiri bawah. Jika kita menulis seperti ini,
SELECT box('5,2', '3,4'); box
maka kami akan memastikan bahwa Postgres tidak memberikan puncak yang mereka berikan kepadanya. Dia menghitung kanan atas dan kiri bawah dari kiri atas dan kanan bawah kami. Ini adalah properti yang nyaman ketika lokasi simpul tidak diketahui sebelumnya - dalam kasus generasi acak, misalnya. Notasi '1,2', '3,4' setara dengan poin (1,2), poin (3,4). Bentuk ini terkadang lebih nyaman.
Untuk bisnis: cari dalam 3 juta persegi panjang
CREATE TABLE boxes(id serial, thebox box, name text);
Kami akan menghasilkan 3 juta persegi panjang acak. Kami ingin distribusi normal, tetapi agar tidak menggunakan ekstensi
tablefunc , kami menggunakan pendekatan "miskin": kami menggunakan acak () - acak (), yang juga memberikan gambar yang bagus (lihat gambar). Dengan persegi panjang, semakin besar semakin besar semakin dekat ke pusat. Pusat gravitasi mereka juga acak. Distribusi semacam itu adalah karakteristik dari beberapa jenis data kota nyata. Dan mereka yang ingin mempelajari hukum statistik atau ingatan yang menyegarkan dapat membaca tentang perbedaan variabel acak, misalnya di
sini .
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
Ukuran tabel yang menunjukkan
\dt+ adalah 242MB. Sekarang Anda dapat memulai pencarian.
Kami mencari tanpa indeks:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Kami melihat bahwa ada Pemindaian Seq Paralel - pemindaian sekuensial (meskipun diparalelkan).
Buat indeks reguler dan non-inklusif:
CREATE INDEX ON boxes USING gist(thebox);
Ukuran indeks
boxes_thebox_idx , yang menunjukkan
\di+ , 262MB. Menanggapi permintaan yang sama, kami mendapatkan:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Waktu pencarian dikurangi dengan faktor tiga dan bukannya Pemindaian Seq Paralel, mereka menerima Pemindaian Indeks Bitmap. Itu tidak sejajar, tetapi bekerja lebih cepat.
Sekarang bunuh indeks lama dan buat yang inklusif:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
Indeks
boxes_thebox_name_idx fatter: 356MB. Ayo pergi:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Index Only Scan digunakan, tetapi gambarnya menyedihkan: waktunya hampir 2 kali lebih lama daripada tanpa itu. Kita membaca buku pegangan pembuat indeks, di
bagian I :
‹Rang PostgreSQL indeks tidak mengandung informasi yang memungkinkan Anda menilai visibilitas baris. Oleh karena itu, metode akses mengembalikan semua versi baris yang termasuk dalam kondisi pencarian, terlepas dari apakah mereka terlihat oleh transaksi saat ini atau tidak. Namun, jika mekanisme pengindeksan harus melihat ke tabel setiap kali untuk menentukan visibilitas, metode pemindaian ini tidak akan berbeda dari pemindaian indeks biasa. Masalahnya dipecahkan oleh fakta bahwa PostgreSQL mendukung apa yang disebut peta visibilitas untuk tabel, di mana proses vakum menandai halaman di mana data tidak berubah cukup lama untuk semua transaksi untuk melihatnya, terlepas dari waktu mulai dan tingkat isolasi. Jika pengidentifikasi baris yang dikembalikan oleh indeks merujuk ke halaman seperti itu, maka visibilitas tidak dapat diperiksa. ››
Kami melakukan VACUUM. Ulangi:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Masalah yang sama sekali berbeda! Dua kali lipat dibandingkan dengan indeks non-inklusif.
Selektivitas dan perolehan
Kinerja indeks inklusif sangat tergantung pada selektivitas kondisi dalam kueri. Untuk menyelidiki sedikit ketergantungan ini, kami akan memecahkan masalah terbalik: kami akan menghasilkan label dengan indeks titik ketik dan kami akan mencari berapa banyak poin yang akan jatuh dalam kotak yang diberikan. Sebarkan titik-titik kuadrat secara merata.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
Ukuran tabel adalah 211 MB.
CREATE INDEX on test_covergist USING gist(tochka);
Ukuran 213 MB.
Kami jelas akan mengambil semua poin yang tersedia menjadi kotak:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Kami meminta EXPLAIN untuk menunjukkan buffer. Ini akan berguna. Sekarang waktu eksekusi permintaan lebih dari 2 detik, dapat dilihat bahwa Buffer: shared read = 54287. Dalam situasi lain, kita bisa melihat campuran dari read read dan shared hit - yaitu, beberapa buffer dibaca dari disk (atau dari cache OS), dan beberapa dari cache buffer. Kami tahu perkiraan ukuran tabel dan indeks, jadi kami akan melindungi diri sendiri dengan mengatur buffer bersama sehingga semuanya cocok - restart Postgres dengan opsi
-o "-c shared_buffers=1GB"
Sekarang:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Artinya, baca bersama menjadi hit bersama, dan waktu dikurangi tiga kali.
Detail penting lainnya dalam MENJELASKAN: 3 juta poin dikembalikan, dan perkiraan jumlah rekaman yang dikembalikan adalah 3.000 Spoiler: angka ini tidak akan berubah dengan selektivitas apa pun. Pengoptimal tidak tahu bagaimana mengevaluasi kardinalitas ketika bekerja dengan tipe kotak atau titik. Dan rencananya tidak akan berubah: untuk ukuran persegi panjang apa pun, akan ada Pemindaian Indeks Bitmap di test_covergist_tochka_idx.
Berikut adalah dua pengukuran lagi dengan jumlah catatan yang dikeluarkan, berbeda dengan urutan besarnya:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Ini mengembalikan 10-aneh kali lebih sedikit catatan (aktual ... baris = 26.988), waktu telah berkurang sekitar 5 kali.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
Isi persegi 30K × 30K (2780) dihitung hanya dalam 16 ms. Dan ketika ada lusinan catatan, mereka sudah diekstraksi dalam fraksi ms, dan pengukuran seperti itu tidak terlalu bisa diandalkan.
Terakhir, ukur hal yang sama dengan indeks inklusif:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
Ukuran 316 MB.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Waktunya hampir sama dengan indeks konvensional, meskipun Index Only Scan.
Tapi:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Dan itu 151 ms. Dan, sesuai:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Ini sudah sebagian kecil dari ms untuk catatan 2780 titik yang sama.
Buffer menyukai senjata
Penjelasan dapat dicari dan ditemukan di senapan yang belum menembak tetapi yang tergantung di dinding: jumlah blok dibaca. Dalam kasus indeks inklusif, hanya blok indeks itu sendiri yang dibaca (Heap Fetches: 0). Dalam tiga kasus, ini adalah angka 40492, 3735, dan 52. Tetapi ketika menggunakan indeks reguler, blok yang dibaca terdiri dari jumlah bit yang dibaca dalam indeks Bitmap Heap Scan (54248 dengan 3 juta catatan) dan yang harus dibaca dari heap (27223) , karena bidang nama tidak dapat diekstraksi dari indeks reguler. 54248 + 27223 = 81471. Eksklusif adalah 40492. Untuk dua kasus lain: 29534 + 2510 = 31044 dan 2655 + 31 = 2686. Dalam kasus indeks reguler, bagaimanapun, lebih banyak blok dibaca, tetapi dengan peningkatan dalam selektivitas, jumlah blok yang dibaca mulai berbeda dengan urutan besarnya daripada 2 kali karena fakta bahwa jumlah blok yang diperlukan dari tumpukan menurun lebih lambat daripada membaca blok indeks.
Tapi mungkin intinya bukan selektivitas sama sekali, tetapi hanya ukuran tabel? Untuk berjaga-jaga, kami mengulangi langkah yang sama, menghasilkan tabel dengan 300 ribu, dan bukan 3 juta catatan:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Selanjutnya, ulangi yang sama untuk indeks inklusif. Inilah hasilnya:
Dalam hal cakupan 100% poin, kueri itu bahkan sedikit lebih lambat daripada dengan indeks biasa. Selanjutnya, seperti dalam kasus 3 juta, semuanya jatuh ke tempatnya. Artinya, selektivitas itu penting.
Perusahaan kami menguji indeks GiST inklusif pada data riil - satu set dengan beberapa juta persegi panjang pada peta Moskow. Kesimpulannya sama: dalam banyak situasi, indeks seperti itu secara nyata mempercepat permintaan. Tetapi artikel tersebut tidak dapat diilustrasikan dengan gambar dan jumlah tes: data ini tidak ada dalam domain publik.
Alih-alih sebuah kesimpulan
Mari kita kembali sejenak ke persegi panjang acak. Mari kita coba melakukan hal yang sama dengan spgist. Anda dapat mengingat atau mencari tahu apa artinya memahami perbedaan antara SP-GiST dan GiST dengan membaca artikel
Indeks di PostgreSQL - 6 . Buat indeks inklusif:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
Sayangnya, untuk SP-GiST, indeks inklusif belum diterapkan.
Jadi ada ruang untuk perbaikan!