全部產品
Search
文件中心

PolarDB:BRIN索引

更新時間:Nov 08, 2024

BRIN索引是塊級索引,有別於B-TREE等索引,BRIN記錄並不是以行號為單位記錄索引明細,而是記錄每個資料區塊或者每段連續的資料區塊的統計資訊。因此BRIN索引空間佔用特別的小,對資料寫入、更新、刪除的影響也很小。

操作符

  • <

  • <=

  • =

  • >=

  • >

應用情境

流式日誌資料,按時間順序不斷的順序插入。索引佔用空間小,效能要求高。

準備工作

  • 建立表及插入資料

    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();
  • 查詢資料

    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
  • 建立索引

    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);
  • 查詢索引

    \di+
    Schema   | Name         | Type  | Owner    | Table  | Size   | Description
    wangjian | idx_t_brin_1 | index | wangjian | t_brin | 272 kB |
    wangjian | idx_t_brin_2 | index | wangjian | t_brin | 352 kB |
    wangjian | idx_t_brin_3 | index | wangjian | t_brin | 21 MB  |
    wangjian | idx_t_brin_4 | index | wangjian | t_brin | 21 MB  |

樣本一

  • 使用索引執行計畫。

    EXPLAIN (analyze, verbose,timing,costs, buffers) SELECT * FROM t_brin WHERE id BETWEEN 100 AND 200;
    Index Scan using idx_t_brin_3 on public.t_brin  (cost=0.42..5.79 rows=107 width=45) (actual time=0.006..0.020 rows=101 loops=1)
      Output: id, info, crt_time
      Index Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
      Buffers: shared hit=5 (main=5 vm=0 fsm=0)
    Query Identifier: -5761088690410512151
    Planning:
      Buffers: shared hit=40 (main=38 vm=2 fsm=0)
    Planning Time: 0.232 ms
    Execution Time: 0.045 ms
  • 刪除索引後,執行計畫。

    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 on public.t_brin  (cost=37.83..241.69 rows=107 width=45) (actual time=1.333..1.351 rows=101 loops=1)
      Output: id, info, crt_time
      Recheck Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
      Rows Removed by Index Recheck: 93
      Heap Blocks: lossy=2
      Buffers: shared hit=51 (main=51 vm=0 fsm=0)
      ->  Bitmap Index Scan on idx_t_brin_1  (cost=0.00..37.80 rows=186 width=0) (actual time=1.327..1.327 rows=20 loops=1)
            Index Cond: ((t_brin.id >= 100) AND (t_brin.id <= 200))
            Buffers: shared hit=49 (main=49 vm=0 fsm=0)
    Query Identifier: -5761088690410512151
    Planning:
      Buffers: shared hit=5 (main=5 vm=0 fsm=0) dirtied=1 (main=0 vm=0 fsm=0)
    Planning Time: 0.054 ms
    Execution Time: 1.381 ms

樣本二

  • 使用索引執行計畫。

    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';
    Index Scan using idx_t_brin_4 on public.t_brin  (cost=0.42..2.64 rows=1 width=45) (actual time=0.003..0.003 rows=0 loops=1)
      Output: id, info, crt_time
      Index 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))
      Buffers: shared hit=3 (main=3 vm=0 fsm=0)
    Query Identifier: 2646955540723493075
    Planning:
      Buffers: shared hit=9 (main=7 vm=2 fsm=0)
    Planning Time: 0.061 ms
    Execution Time: 0.019 ms
  • 刪除索引後,執行計畫。

    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 on public.t_brin  (cost=49.90..152.73 rows=1 width=45) (actual time=1.449..1.449 rows=0 loops=1)
      Output: 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))
      Buffers: shared hit=65 (main=65 vm=0 fsm=0)
      ->  Bitmap Index Scan on idx_t_brin_2  (cost=0.00..49.90 rows=93 width=0) (actual time=1.447..1.447 rows=0 loops=1)
         Index 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))
         Buffers: shared hit=65 (main=65 vm=0 fsm=0)
    Query Identifier: 2646955540723493075
    Planning:
      Buffers: shared hit=5 (main=5 vm=0 fsm=0) dirtied=1 (main=0 vm=0 fsm=0)
    Planning Time: 0.058 ms
    Execution Time: 1.477 ms