Geekly articles weekly

Indeks dalam PostgreSQL - 1

Pendahuluan


Seri artikel ini sebagian besar berkaitan dengan indeks di PostgreSQL.

Subjek apa saja dapat dipertimbangkan dari perspektif yang berbeda. Kami akan membahas hal-hal yang harus menarik minat pengembang aplikasi yang menggunakan DBMS: indeks apa yang tersedia, mengapa ada begitu banyak jenis yang berbeda, dan bagaimana menggunakannya untuk mempercepat permintaan. Topiknya mungkin dapat dicakup dalam lebih sedikit kata, tetapi dalam kerahasiaan kami berharap untuk pengembang yang ingin tahu, yang juga tertarik pada detail internal, terutama karena memahami detail tersebut memungkinkan Anda untuk tidak hanya menunda penilaian orang lain, tetapi juga membuat kesimpulan milikmu sendiri

Pengembangan jenis indeks baru berada di luar ruang lingkup. Ini membutuhkan pengetahuan tentang bahasa pemrograman C dan berkaitan dengan keahlian pemrogram sistem daripada pengembang aplikasi. Untuk alasan yang sama kita hampir tidak akan membahas antarmuka pemrograman, tetapi hanya akan fokus pada hal-hal yang penting untuk bekerja dengan indeks siap pakai.

Pada artikel ini kita akan membahas distribusi tanggung jawab antara mesin pengindeksan umum yang terkait dengan inti DBMS dan metode akses indeks individual, yang memungkinkan PostgreSQL untuk ditambahkan sebagai ekstensi. Pada artikel selanjutnya kita akan membahas antarmuka metode akses dan konsep kritis seperti kelas dan keluarga operator. Setelah pengantar yang panjang tapi perlu, kami akan mempertimbangkan rincian struktur dan penerapan berbagai jenis indeks: Hash , B-tree , GiST , SP-GiST , GIN dan RUM , BRIN , dan Bloom .

Sebelum kita mulai, saya ingin mengucapkan terima kasih kepada Elena Indrupskaya karena telah menerjemahkan artikel-artikel itu ke dalam bahasa Inggris.
Banyak hal telah berubah sedikit sejak publikasi aslinya. Komentar saya tentang keadaan saat ini ditunjukkan seperti ini.

Indeks


Dalam PostgreSQL, indeks adalah objek basis data khusus yang terutama dirancang untuk mempercepat akses data. Mereka adalah struktur tambahan: setiap indeks dapat dihapus dan diciptakan kembali dari informasi dalam tabel. Terkadang Anda mungkin mendengar bahwa DBMS dapat bekerja tanpa indeks meskipun lambat. Namun, ini bukan masalahnya, karena indeks juga berfungsi untuk menegakkan beberapa kendala integritas.

Saat ini, enam jenis indeks dibangun ke dalam PostgreSQL 9.6, dan satu indeks lagi tersedia sebagai ekstensi - berkat perubahan signifikan dalam versi 9.6. Jadi, harapkan jenis indeks baru dalam waktu dekat.

Terlepas dari semua perbedaan antara jenis indeks (juga disebut metode akses), masing-masing dari mereka akhirnya mengaitkan kunci (misalnya, nilai kolom yang diindeks) dengan baris tabel yang berisi kunci ini. Setiap baris diidentifikasi oleh TID (tuple id), yang terdiri dari jumlah blok dalam file dan posisi baris di dalam blok. Yang mengatakan, dengan kunci yang diketahui atau beberapa informasi tentang itu kita dapat dengan cepat membaca baris-baris yang mungkin berisi informasi yang menarik kita tanpa memindai seluruh tabel.

Penting untuk dipahami bahwa indeks mempercepat akses data dengan biaya pemeliharaan tertentu. Untuk setiap operasi pada data yang diindeks, apakah itu penyisipan, penghapusan, atau pembaruan baris tabel, indeks untuk tabel itu perlu diperbarui juga, dan dalam transaksi yang sama. Perhatikan bahwa pembaruan bidang tabel yang indeksnya belum dibangun tidak menghasilkan pembaruan indeks; teknik ini disebut HOT (Heap-Only Tuples).

Perluasan memerlukan beberapa implikasi. Untuk memudahkan penambahan metode akses baru ke sistem, antarmuka mesin pengindeksan umum telah diterapkan. Tugas utamanya adalah untuk mendapatkan TUT dari metode akses dan bekerja dengannya:

  • Baca data dari versi baris tabel yang sesuai.
  • Ambil versi baris TID oleh TID atau dalam batch menggunakan bitmap prebuilt.
  • Periksa visibilitas versi baris untuk transaksi saat ini dengan mempertimbangkan tingkat isolasinya.

Mesin pengindeksan terlibat dalam melakukan kueri. Itu disebut sesuai dengan rencana yang dibuat pada tahap optimisasi. Pengoptimal, memilah dan mengevaluasi berbagai cara untuk melakukan kueri, harus memahami kemampuan semua metode akses yang berpotensi berlaku. Apakah metode ini dapat mengembalikan data dalam urutan yang diperlukan atau haruskah kami mengantisipasi penyortiran? Bisakah kita menggunakan metode ini untuk mencari NULL? Ini adalah masalah yang diselesaikan oleh optimizer secara teratur.

Tidak hanya pengoptimal membutuhkan informasi tentang metode akses. Ketika membangun indeks, sistem harus memutuskan apakah indeks dapat dibangun di atas beberapa kolom dan apakah indeks ini memastikan keunikan.

Jadi, setiap metode akses harus menyediakan semua informasi yang diperlukan tentang dirinya sendiri. Versi yang lebih rendah dari 9,6 menggunakan tabel "pg_am" untuk ini, sementara mulai dengan versi 9.6 data dipindahkan ke tingkat yang lebih dalam, di dalam fungsi-fungsi khusus. Kami akan berkenalan dengan antarmuka ini sedikit lebih jauh.

Semua yang lain adalah tugas dari metode akses:

  • Menerapkan algoritma untuk membangun indeks dan memetakan data ke halaman (untuk buffer cache manager untuk memproses setiap indeks secara seragam).
  • Cari informasi dalam indeks dengan predikat dalam bentuk " ekspresi operator bidang-bidang ".
  • Mengevaluasi biaya penggunaan indeks.
  • Memanipulasi kunci yang diperlukan untuk pemrosesan paralel yang benar.
  • Buat catatan write-ahead log (WAL).

Kami pertama-tama akan mempertimbangkan kemampuan mesin pengindeksan umum dan kemudian melanjutkan untuk mempertimbangkan metode akses yang berbeda.

Mesin pengindeksan


Mesin pengindeksan memungkinkan PostgreSQL untuk bekerja dengan berbagai metode akses secara seragam, tetapi dengan mempertimbangkan fitur-fiturnya.

Teknik pemindaian utama


Pemindaian indeks


Kita dapat bekerja secara berbeda dengan TIDs yang disediakan oleh indeks. Mari kita pertimbangkan sebuah contoh:

postgres=# create table t(a integer, b text, c boolean); postgres=# insert into t(a,b,c) select s.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as s(id) order by random(); postgres=# create index on t(a); postgres=# analyze t;

Kami membuat tabel tiga bidang. Bidang pertama berisi angka dari 1 hingga 100.000, dan indeks (apa pun jenisnya) dibuat di bidang ini. Kolom kedua berisi berbagai karakter ASCII kecuali yang tidak dapat dicetak. Akhirnya, bidang ketiga berisi nilai logis yang benar untuk sekitar 1% dari baris dan salah untuk sisanya. Baris dimasukkan ke dalam tabel dalam urutan acak.

Mari kita coba memilih nilai dengan syarat "a = 1". Perhatikan bahwa kondisinya seperti " ekspresi operator bidang berindeks ", di mana operator "sama" dan ekspresi (kunci pencarian) adalah "1". Dalam kebanyakan kasus, kondisi harus terlihat seperti ini agar indeks dapat digunakan.

 postgres=# explain (costs off) select * from t where a = 1; 
  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t Index Cond: (a = 1) (2 rows)

Dalam hal ini, pengoptimal memutuskan untuk menggunakan pemindaian indeks . Dengan pemindaian indeks, metode akses mengembalikan nilai TID satu per satu hingga baris pencocokan terakhir tercapai. Mesin pengindeksan mengakses baris tabel yang ditunjukkan oleh TIDs pada gilirannya, mendapatkan versi baris, memeriksa visibilitasnya terhadap aturan konkurensi multiversion, dan mengembalikan data yang diperoleh.

Pemindaian bitmap


Pemindaian indeks berfungsi dengan baik ketika kita hanya berurusan dengan beberapa nilai. Namun, karena jumlah baris yang diambil meningkat, lebih mungkin untuk kembali ke halaman tabel yang sama beberapa kali. Oleh karena itu, pengoptimal beralih ke pemindaian bitmap .

 postgres=# explain (costs off) select * from t where a <= 100; 
  QUERY PLAN ------------------------------------ Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) (4 rows)

Metode akses pertama mengembalikan semua TIDs yang cocok dengan kondisi (Bitmap Index Scan node), dan bitmap versi baris dibangun dari TIDs ini. Versi baris kemudian dibaca dari tabel (Bitmap Heap Scan), setiap halaman hanya dibaca sekali.

Perhatikan bahwa pada langkah kedua, kondisi dapat diperiksa ulang (Periksa ulang Cond). Jumlah baris yang diambil bisa terlalu besar untuk bitmap versi baris agar sepenuhnya sesuai dengan RAM (dibatasi oleh parameter "work_mem"). Dalam kasus ini, bitmap hanya dibuat untuk halaman yang berisi setidaknya satu versi baris yang cocok. Bitmap "lossy" ini membutuhkan lebih sedikit ruang, tetapi ketika membaca halaman, kita perlu memeriksa kembali kondisi untuk setiap baris yang ada di sana. Perhatikan bahwa bahkan untuk sejumlah kecil baris yang diambil dan oleh karena itu bitmap "tepat" (seperti dalam contoh kami), langkah "Periksa Ulang Cond" diwakili dalam rencana, walaupun sebenarnya tidak dilakukan.

Jika ketentuan diberlakukan pada beberapa bidang tabel dan bidang ini diindeks, pemindaian bitmap memungkinkan penggunaan beberapa indeks secara bersamaan (jika pengoptimal menganggap ini efisien). Untuk setiap indeks, bitmap versi baris dibangun, yang mana penggandaan boolean bitwise (jika ekspresi digabungkan dengan AND) atau penambahan boolean (jika ekspresi digabungkan oleh OR) kemudian dilakukan. Sebagai contoh:

 postgres=# create index on t(b); postgres=# analyze t; postgres=# explain (costs off) select * from t where a <= 100 and b = 'a'; 
  QUERY PLAN -------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) (7 rows)

Di sini simpul BitmapAnd menggabungkan dua bitmap dengan operasi bitwise "dan".

Pemindaian bitmap memungkinkan kita untuk menghindari akses berulang ke halaman data yang sama. Tetapi bagaimana jika data dalam halaman tabel dipesan secara fisik persis dengan cara yang sama seperti catatan indeks? Tidak diragukan lagi bahwa kita tidak dapat sepenuhnya bergantung pada urutan fisik data di halaman. Jika data yang diurutkan diperlukan, kita harus secara eksplisit menentukan klausa ORDER BY dalam kueri. Tetapi situasi mungkin di mana sebenarnya "hampir semua" data dipesan: misalnya, jika baris ditambahkan dalam urutan yang diperlukan dan tidak berubah setelah itu atau setelah melakukan perintah CLUSTER. Dalam kasus seperti ini, membangun bitmap adalah langkah yang berlebihan, dan pemindaian indeks biasa akan sama baiknya (kecuali jika kita memperhitungkan kemungkinan untuk bergabung dengan beberapa indeks). Oleh karena itu, ketika memilih metode akses, perencana melihat statistik khusus yang menunjukkan korelasi antara urutan baris fisik dan urutan logis dari nilai kolom:

 postgres=# select attname, correlation from pg_stats where tablename = 't'; 
  attname | correlation ---------+------------- b | 0.533512 c | 0.942365 a | -0.00768816 (3 rows)

Nilai absolut mendekati satu menunjukkan korelasi tinggi (seperti untuk kolom "c"), sedangkan nilai mendekati nol, sebaliknya, menunjukkan distribusi kacau (kolom "a").

Pemindaian berurutan


Untuk melengkapi gambar, kita harus perhatikan bahwa dengan kondisi non-selektif, pengoptimal akan lebih memilih pemindaian berurutan dari seluruh tabel daripada penggunaan indeks:

 postgres=# explain (costs off) select * from t where a <= 40000; 
  QUERY PLAN ------------------------ Seq Scan on t Filter: (a <= 40000) (2 rows)

Masalahnya adalah bahwa indeks berfungsi semakin baik semakin tinggi selektivitas kondisi, yaitu, semakin sedikit baris yang cocok. Pertumbuhan jumlah baris yang diambil meningkatkan biaya overhead untuk membaca halaman indeks.

Pemindaian berurutan menjadi lebih cepat sehingga pemindaian acak menambah situasi. Ini terutama berlaku untuk hard disk, di mana operasi mekanis membawa kepala magnetik ke trek membutuhkan waktu lebih banyak daripada membaca data itu sendiri. Efek ini kurang terlihat untuk SSD. Dua parameter tersedia untuk memperhitungkan perbedaan dalam biaya akses, "seq_page_cost" dan "random_page_cost", yang dapat kita atur tidak hanya secara global, tetapi pada tingkat tablespace, dengan cara ini menyesuaikan dengan karakteristik berbagai subsistem disk.

Meliputi indeks


Sebagai aturan, tugas utama metode akses adalah mengembalikan pengidentifikasi baris tabel yang cocok untuk mesin pengindeksan untuk membaca data yang diperlukan dari baris ini. Tetapi bagaimana jika indeks sudah berisi semua data yang dibutuhkan untuk kueri? Indeks semacam itu disebut penutup , dan dalam hal ini, pengoptimal dapat menerapkan pemindaian hanya indeks :

 postgres=# vacuum t; postgres=# explain (costs off) select a from t where a < 100; 
  QUERY PLAN ------------------------------------ Index Only Scan using t_a_idx on t Index Cond: (a < 100) (2 rows)

Nama ini dapat memberikan gagasan bahwa mesin pengindeksan tidak mengakses tabel sama sekali dan mendapatkan semua informasi yang diperlukan dari metode akses saja. Tetapi ini tidak persis seperti itu karena indeks di PostgreSQL tidak menyimpan informasi yang memungkinkan kita menilai visibilitas baris. Oleh karena itu, metode akses mengembalikan versi baris yang cocok dengan kondisi pencarian terlepas dari visibilitasnya dalam transaksi saat ini.

Namun, jika mesin pengindeksan perlu melihat ke dalam tabel untuk visibilitas setiap kali, metode pemindaian ini tidak akan berbeda dari pemindaian indeks biasa.

Untuk mengatasi masalah tersebut, untuk tabel PostgreSQL mempertahankan apa yang disebut peta visibilitas di mana menyedot debu menandai halaman di mana data tidak berubah cukup lama agar data ini dapat dilihat oleh semua transaksi terlepas dari waktu mulai dan tingkat isolasi. Jika pengidentifikasi baris yang dikembalikan oleh indeks terkait dengan halaman tersebut, pemeriksaan visibilitas dapat dihindari.

Oleh karena itu, penyedotan debu secara teratur meningkatkan efisiensi penutupan indeks. Selain itu, pengoptimal memperhitungkan jumlah tupel mati dan dapat memutuskan untuk tidak menggunakan pemindaian hanya-indeks jika ia memperkirakan biaya overhead yang tinggi untuk pemeriksaan visibilitas.

Kita bisa mempelajari jumlah akses paksa ke tabel menggunakan perintah EXPLAIN ANALYZE:

 postgres=# explain (analyze, costs off) select a from t where a < 100; 
  QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1) Index Cond: (a < 100) Heap Fetches: 0 Planning time: 0.092 ms Execution time: 0.059 ms (5 rows)

Dalam hal ini, tidak perlu mengakses tabel (Heap Fetches: 0), karena menyedot debu baru saja dilakukan. Secara umum, semakin dekat angka ini ke nol semakin baik.

Tidak semua indeks menyimpan nilai yang diindeks bersama dengan pengidentifikasi baris. Jika metode akses tidak dapat mengembalikan data, itu tidak dapat digunakan untuk hanya pindaian indeks.

PostgreSQL 11 telah memperkenalkan fitur baru: INCLUDE-indexes. Bagaimana jika ada indeks unik yang tidak memiliki beberapa kolom untuk digunakan sebagai indeks penutup untuk beberapa permintaan? Anda tidak bisa begitu saja menambahkan kolom ke indeks karena akan merusak keunikannya. Fitur ini memungkinkan untuk memasukkan kolom non-kunci yang tidak memengaruhi keunikan dan tidak dapat digunakan dalam predikat pencarian, tetapi masih dapat melayani pemindaian hanya indeks. Tambalan dikembangkan oleh rekan saya Anastasia Lubennikova.

Tidak


NULLs memainkan peran penting dalam database relasional sebagai cara mudah untuk mewakili nilai yang tidak ada atau tidak diketahui.

Tetapi nilai khusus itu istimewa untuk dihadapi. Aljabar boolean reguler menjadi ternary; tidak jelas apakah NULL harus lebih kecil atau lebih besar dari nilai reguler (ini memerlukan konstruksi khusus untuk menyortir, NULLS FIRST dan NULLS LAST); tidak jelas apakah fungsi agregat harus mempertimbangkan NULL atau tidak; diperlukan statistik khusus untuk perencana ...

Dari perspektif dukungan indeks, juga tidak jelas apakah kita perlu mengindeks nilai-nilai ini atau tidak. Jika NULLs tidak diindeks, indeks mungkin lebih kompak. Tetapi jika NULL diindeks, kita akan dapat menggunakan indeks untuk kondisi seperti " bidang yang diindeks [TIDAK] NULL" dan juga sebagai indeks penutup ketika tidak ada kondisi sama sekali yang ditentukan untuk tabel (karena dalam kasus ini, index harus mengembalikan data semua baris tabel, termasuk yang dengan NULLs).

Untuk setiap metode akses, pengembang membuat keputusan sendiri apakah akan mengindeks NULL atau tidak. Tapi sebagai aturan, mereka diindeks.

Indeks pada beberapa bidang


Untuk mendukung kondisi untuk beberapa bidang, indeks multikolom dapat digunakan. Misalnya, kami dapat membuat indeks di dua bidang tabel kami:

 postgres=# create index on t(a,b); postgres=# analyze t;

Pengoptimal kemungkinan besar lebih suka indeks ini untuk bergabung dengan bitmap karena di sini kita siap mendapatkan TIDs yang diperlukan tanpa operasi tambahan:

 postgres=# explain (costs off) select * from t where a <= 100 and b = 'a'; 
  QUERY PLAN ------------------------------------------------ Index Scan using t_a_b_idx on t Index Cond: ((a <= 100) AND (b = 'a'::text)) (2 rows)

Indeks multikolom juga dapat digunakan untuk mempercepat pengambilan data dengan suatu kondisi untuk beberapa bidang, dimulai dengan yang pertama:

 postgres=# explain (costs off) select * from t where a <= 100; 
  QUERY PLAN -------------------------------------- Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_b_idx Index Cond: (a <= 100) (4 rows)

Secara umum, jika kondisi tidak dikenakan pada bidang pertama, indeks tidak akan digunakan. Tetapi terkadang optimizer menganggap penggunaan indeks lebih efisien daripada pemindaian berurutan. Kami akan memperluas topik ini ketika mempertimbangkan indeks "btree".

Tidak semua metode akses mendukung indeks pembangunan pada beberapa kolom.

Indeks pada ekspresi


Kami telah menyebutkan bahwa kondisi pencarian harus seperti " ekspresi operator bidang berindeks ". Dalam contoh di bawah ini, indeks tidak akan digunakan karena ekspresi yang mengandung nama bidang digunakan alih-alih nama bidang itu sendiri:

 postgres=# explain (costs off) select * from t where lower(b) = 'a'; 
  QUERY PLAN ------------------------------------------ Seq Scan on t Filter: (lower((b)::text) = 'a'::text) (2 rows)

Tidak perlu banyak menulis ulang kueri khusus ini sehingga hanya nama bidang yang ditulis di sebelah kiri operator. Tetapi jika ini tidak memungkinkan, indeks pada ekspresi (indeks fungsional) akan membantu:

 postgres=# create index on t(lower(b)); postgres=# analyze t; postgres=# explain (costs off) select * from t where lower(b) = 'a'; 
  QUERY PLAN ---------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: (lower((b)::text) = 'a'::text) -> Bitmap Index Scan on t_lower_idx Index Cond: (lower((b)::text) = 'a'::text) (4 rows)

Indeks fungsional dibangun bukan pada bidang tabel, tetapi pada ekspresi sewenang-wenang. Pengoptimal akan mempertimbangkan indeks ini untuk kondisi seperti " ekspresi operator ekspresi-ekspresi ". Jika perhitungan ekspresi yang akan diindeks adalah operasi yang mahal, pembaruan indeks juga akan memerlukan sumber daya perhitungan yang signifikan.

Harap juga diingat bahwa statistik individu dikumpulkan untuk ekspresi yang diindeks. Kita bisa mengetahui statistik ini dalam tampilan "pg_stats" dengan nama indeks:

 postgres=# \dt 
  Table "public.t" Column | Type | Modifiers --------+---------+----------- a | integer | b | text | c | boolean | Indexes: "t_a_b_idx" btree (a, b) "t_a_idx" btree (a) "t_b_idx" btree (b) "t_lower_idx" btree (lower(b)) 
 postgres=# select * from pg_stats where tablename = 't_lower_idx';

Adalah mungkin, jika perlu, untuk mengontrol jumlah keranjang histogram dengan cara yang sama seperti untuk bidang data biasa (mencatat bahwa nama kolom dapat berbeda tergantung pada ekspresi yang diindeks):

 postgres=# \d t_lower_idx 
  Index "public.t_lower_idx" Column | Type | Definition --------+------+------------ lower | text | lower(b) btree, for table "public.t" 
 postgres=# alter index t_lower_idx alter column "lower" set statistics 69;

PostgreSQL 11 telah memperkenalkan cara yang lebih bersih untuk mengontrol target statistik untuk indeks dengan menentukan nomor kolom dalam ALTER INDEX ... SET perintah STATISTIK. Tambalan dikembangkan oleh rekan saya Alexander Korotkov, dan Adrien Nayrat.

Indeks sebagian


Terkadang muncul kebutuhan untuk mengindeks hanya sebagian dari baris tabel. Ini biasanya terkait dengan distribusi yang sangat tidak seragam: masuk akal untuk mencari nilai yang jarang dengan indeks, tetapi lebih mudah untuk menemukan nilai yang sering dengan pemindaian penuh tabel.

Kita tentu dapat membangun indeks reguler pada kolom "c", yang akan bekerja seperti yang kita harapkan:

 postgres=# create index on t(c); postgres=# analyze t; postgres=# explain (costs off) select * from t where c; 
  QUERY PLAN ------------------------------- Index Scan using t_c_idx on t Index Cond: (c = true) Filter: c (3 rows) 
 postgres=# explain (costs off) select * from t where not c; 
  QUERY PLAN ------------------- Seq Scan on t Filter: (NOT c) (2 rows)

Dan ukuran indeksnya adalah 276 halaman:

 postgres=# select relpages from pg_class where relname='t_c_idx'; 
  relpages ---------- 276 (1 row)

Tetapi karena kolom "c" memiliki nilai true hanya untuk 1% dari baris, 99% dari indeks sebenarnya tidak pernah digunakan. Dalam hal ini, kita dapat membuat indeks parsial:

 postgres=# create index on t(c) where c; postgres=# analyze t;

Ukuran indeks dikurangi menjadi 5 halaman:

 postgres=# select relpages from pg_class where relname='t_c_idx1'; 
  relpages ---------- 5 (1 row)

Terkadang perbedaan dalam ukuran dan kinerja mungkin cukup signifikan.

Menyortir


Jika metode akses mengembalikan pengidentifikasi baris dalam beberapa urutan tertentu, ini memberikan pengoptimal dengan opsi tambahan untuk melakukan kueri.

Kita bisa memindai tabel dan kemudian mengurutkan data:

 postgres=# set enable_indexscan=off; postgres=# explain (costs off) select * from t order by a; 
  QUERY PLAN --------------------- Sort Sort Key: a -> Seq Scan on t (3 rows)

Tetapi kita dapat membaca data menggunakan indeks dengan mudah dalam urutan yang diinginkan:

 postgres=# set enable_indexscan=on; postgres=# explain (costs off) select * from t order by a; 
  QUERY PLAN ------------------------------- Index Scan using t_a_idx on t (1 row)

Hanya "btree" dari semua metode akses yang dapat mengembalikan data yang diurutkan, jadi mari kita menunda diskusi yang lebih terperinci sampai mempertimbangkan jenis indeks ini.

Bangunan serentak


Biasanya membangun indeks mendapatkan kunci SHARE untuk tabel. Kunci ini memungkinkan membaca data dari tabel, tetapi melarang perubahan apa pun saat indeks sedang dibuat.

Kami dapat memastikan hal ini jika, katakanlah, saat membuat indeks pada tabel "t", kami melakukan kueri di bawah ini di sesi lain:

 postgres=# select mode, granted from pg_locks where relation = 't'::regclass; 
  mode | granted -----------+--------- ShareLock | t (1 row)

Jika tabel cukup besar dan banyak digunakan untuk penyisipan, pembaruan, atau penghapusan, ini mungkin tampaknya tidak dapat diterima karena proses modifikasi akan menunggu rilis kunci untuk waktu yang lama.

Dalam hal ini, kita bisa menggunakan bangunan bersamaan dari indeks.

 postgres=# create index concurrently on t(a);

Perintah ini mengunci tabel dalam mode EXCLUSIVE SHARE UPDATE, yang memungkinkan membaca dan memperbarui (hanya mengubah struktur tabel yang dilarang, serta menyedot debu, analisis, atau membuat indeks lain pada tabel ini bersamaan).

Namun, ada juga sisi lain. Pertama, indeks akan dibangun lebih lambat dari biasanya karena dua lintasan melintasi tabel dilakukan alih-alih satu, dan juga perlu menunggu penyelesaian transaksi paralel yang memodifikasi data.

Kedua, dengan pembangunan bersamaan indeks, kebuntuan dapat terjadi atau kendala unik dapat dilanggar. Namun, indeks akan dibangun, meskipun tidak beroperasi. Indeks seperti itu harus dihapus dan dibangun kembali. Indeks yang tidak beroperasi ditandai dengan kata INVALID di output dari perintah psql \ d, dan kueri di bawah ini mengembalikan daftar lengkapnya:

 postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name from pg_index where not indisvalid; 
  index_name | table_name ------------+------------ t_a_idx | t (1 row)

Baca terus .

Source: https://habr.com/ru/post/id441962/

More articles:


All Articles