PolarDB：Indeks BRIN

Operator

• <

• <=

• =

• >=

• >

Skenario

Data log streaming dimasukkan ke dalam database secara berurutan berdasarkan waktu. Indeks harus menggunakan memori dalam jumlah kecil, sementara database harus tetap menawarkan performa tinggi.

Persiapan

• Buat tabel dan masukkan data.

  CREATE TABLE t_brin (id int, info text, crt_time timestamp);
  INSERT INTO t_brin SELECT generate_series(1,1000000), md5(random()::text), clock_timestamp();

• Kueri data.

  SELECT ctid,* FROM t_brin limit 3;

   ctid  | id | info                             | crt_time
   (0,1) |  1 | 81c3f4f603c0c17e45778b2dd2d72f4d | 2024-11-06 09:26:57.549121
   (0,2) |  2 | b4b77e95a1580480107b038776b3cc9c | 2024-11-06 09:26:57.551548
   (0,3) |  3 | 6ebf5ebdd3df3428c279de2d5c7aab9f | 2024-11-06 09:26:57.551558

• Buat indeks.

  CREATE INDEX idx_t_brin_1 ON t_brin USING brin (id) WITH (pages_per_range=1);
  CREATE INDEX idx_t_brin_2 ON t_brin USING brin (crt_time) WITH (pages_per_range=1);
  CREATE INDEX idx_t_brin_3 ON t_brin(id);
  CREATE INDEX idx_t_brin_4 ON t_brin(crt_time);

• Kueri indeks.

  \di+

  Skema   | Nama         | Tipe  | Pemilik    | Tabel  | Ukuran   | Deskripsi
  wangjian | idx_t_brin_1 | indeks | wangjian | t_brin | 272 kB |
  wangjian | idx_t_brin_2 | indeks | wangjian | t_brin | 352 kB |
  wangjian | idx_t_brin_3 | indeks | wangjian | t_brin | 21 MB  |
  wangjian | idx_t_brin_4 | indeks | wangjian | t_brin | 21 MB  |

Contoh 1

• Lihat rencana eksekusi setelah indeks dibuat.

  EXPLAIN (analyze, verbose,timing,costs, buffers) SELECT * FROM t_brin WHERE id BETWEEN 100 AND 200;

  Pemindaian Indeks menggunakan idx_t_brin_3 pada public.t_brin  (biaya=0.42..5.79 baris=107 lebar=45) (aktual waktu=0.006..0.020 baris=101 loop=1)
    Keluaran: id, info, crt_time
    Kondisi Indeks: ((t_brin.id >= 100) AND (t_brin.id <= 200))
    Buffer: shared hit=5 (main=5 vm=0 fsm=0)
  Pengenal Kueri: -5761088690410512151
  Perencanaan:
    Buffer: shared hit=40 (main=38 vm=2 fsm=0)
  Waktu Perencanaan: 0.232 ms
  Waktu Eksekusi: 0.045 ms

• Hapus indeks dan lihat rencana eksekusi.

  DROP INDEX idx_t_brin_3;

  EXPLAIN (analyze,verbose,timing,costs,buffers) SELECT * FROM t_brin WHERE id BETWEEN 100 AND 200;

  Bitmap Heap Scan pada public.t_brin  (biaya=37.83..241.69 baris=107 lebar=45) (aktual waktu=1.333..1.351 baris=101 loop=1)
    Keluaran: id, info, crt_time
    Recheck Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
    Baris Dihapus oleh Recheck Indeks: 93
    Blok Heap: lossy=2
    Buffer: shared hit=51 (main=51 vm=0 fsm=0)
    ->  Bitmap Index Scan pada idx_t_brin_1  (biaya=0.00..37.80 baris=186 lebar=0) (aktual waktu=1.327..1.327 baris=20 loop=1)
          Kondisi Indeks: ((t_brin.id >= 100) AND (t_brin.id <= 200))
          Buffer: shared hit=49 (main=49 vm=0 fsm=0)
  Pengenal Kueri: -5761088690410512151
  Perencanaan:
    Buffer: shared hit=5 (main=5 vm=0 fsm=0) dirtied=1 (main=0 vm=0 fsm=0)
  Waktu Perencanaan: 0.054 ms
  Waktu Eksekusi: 1.381 ms

Contoh 2

• Lihat rencana eksekusi setelah indeks dibuat.

  EXPLAIN (analyze, verbose, timing, costs, buffers) SELECT * FROM t_brin WHERE crt_time BETWEEN '2017-06-27 22:50:19.172224' AND '2017-06-27 22:50:19.182224';

  Pemindaian Indeks menggunakan idx_t_brin_4 pada public.t_brin  (biaya=0.42..2.64 baris=1 lebar=45) (aktual waktu=0.003..0.003 baris=0 loop=1)
    Keluaran: id, info, crt_time
    Kondisi Indeks: ((t_brin.crt_time >= '2017-06-27 22:50:19.172224'::timestamp without time zone) AND (t_brin.crt_time <= '2017-06-27 22:50:19.182224'::timestamp without time zone))
    Buffer: shared hit=3 (main=3 vm=0 fsm=0)
  Pengenal Kueri: 2646955540723493075
  Perencanaan:
    Buffer: shared hit=9 (main=7 vm=2 fsm=0)
  Waktu Perencanaan: 0.061 ms
  Waktu Eksekusi: 0.019 ms

• Hapus indeks dan lihat rencana eksekusi.

  DROP INDEX idx_t_brin_4;

  EXPLAIN (analyze, verbose, timing, costs, buffers) SELECT * FROM t_brin WHERE crt_time BETWEEN '2017-06-27 22:50:19.172224' AND '2017-06-27 22:50:19.182224';

  Bitmap Heap Scan pada public.t_brin  (biaya=49.90..152.73 baris=1 lebar=45) (aktual waktu=1.449..1.449 baris=0 loop=1)
    Keluaran: id, info, crt_time
    Recheck Cond: ((t_brin.crt_time >= '2017-06-27 22:50:19.172224'::timestamp without time zone) AND (t_brin.crt_time <= '2017-06-27 22:50:19.182224'::timestamp without time zone))
    Buffer: shared hit=65 (main=65 vm=0 fsm=0)
    ->  Bitmap Index Scan pada idx_t_brin_2  (biaya=0.00..49.90 baris=93 lebar=0) (aktual waktu=1.447..1.447 baris=0 loop=1)
       Kondisi Indeks: ((t_brin.crt_time >= '2017-06-27 22:50:19.172224'::timestamp without time zone) AND (t_brin.crt_time <= '2017-06-27 22:50:19.182224'::timestamp without time zone))
       Buffer: shared hit=65 (main=65 vm=0 fsm=0)
  Pengenal Kueri: 2646955540723493075
  Perencanaan:
    Buffer: shared hit=5 (main=5 vm=0 fsm=0) dirtied=1 (main=0 vm=0 fsm=0)
  Waktu Perencanaan: 0.058 ms
  Waktu Eksekusi: 1.477 ms