Índices BRIN - PolarDB: Central de Documentação do Alibaba Cloud

O BRIN (Block Range Index) armazena estatísticas resumidas para intervalos de blocos de dados, em vez de indexar IDs de linhas individuais como uma B-tree. Isso torna os índices BRIN extremamente compactos e impõe sobrecarga mínima em operações de escrita, atualização e exclusão, embora a localização das linhas seja menos precisa.

Funcionamento

Um índice BRIN divide a tabela em intervalos contíguos de blocos e registra estatísticas resumidas para cada intervalo. Durante a consulta, o planejador ignora os intervalos cujas estatísticas não atendem à condição da consulta e varre apenas os intervalos candidatos por meio de uma varredura de heap com bitmap.

Como o índice armazena resumos em vez de localizações exatas das linhas, ele é com perdas: o executor precisa reverificar cada linha dentro de um intervalo de blocos qualificado em relação à condição da consulta. Por esse motivo, os planos de execução para consultas BRIN exibem Recheck Cond e Rows Removed by Index Recheck. Ambos representam o comportamento esperado e não indicam erros.

O parâmetro de armazenamento pages_per_range controla o equilíbrio entre o tamanho do índice e a precisão:

Valor de `pages_per_range`	Tamanho do índice	Precisão	Blocos ignorados
Pequeno (ex.: 1)	Maior	Maior	Mais
Grande	Menor	Menor	Menos

Operadores suportados

Os índices BRIN oferecem suporte aos seguintes operadores de comparação: <, <=, =, >=, >.

Quando usar índices BRIN

O BRIN apresenta melhor desempenho quando os valores da coluna têm correlação natural com a ordem física de armazenamento. Ou seja, as linhas inseridas em sequência tendem a ter valores de coluna crescentes de forma sequencial. Exemplos comuns incluem:

Dados de séries temporais somente de anexação: entradas de log, leituras de sensores ou fluxos de eventos inseridos em ordem de timestamp.
IDs com incremento automático: linhas inseridas sequencialmente, de modo que a ordem física corresponda à ordem dos IDs.

O BRIN não substitui a B-tree. Para buscas pontuais ou consultas em dados distribuídos aleatoriamente, utilize um índice B-tree.

Exemplo: tamanho do índice e desempenho da consulta

O exemplo a seguir cria uma tabela com 1.000.000 de linhas e compara índices BRIN e B-tree quanto ao tamanho de armazenamento e à execução de consultas.

Configuração

Crie a tabela e insira 1.000.000 de linhas:

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();

Verifique a ordem física das linhas (a coluna ctid reflete a localização no heap):

SELECT ctid,* FROM t_brin limit 3;

Saída:

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

As colunas id e crt_time aumentam monotonicamente conforme a ordem física das linhas, o que torna esta tabela uma boa candidata para índices BRIN.

Crie dois índices BRIN e dois índices B-tree para comparação. Ambos os índices BRIN usam pages_per_range=1 para obter precisão máxima:

-- BRIN indexes
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);

-- B-tree indexes
CREATE INDEX idx_t_brin_3 ON t_brin(id);
CREATE INDEX idx_t_brin_4 ON t_brin(crt_time);

Verifique os tamanhos dos índices:

\di+

Saída:

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  |

Ambos os índices BRIN ocupam menos de 400 kB, enquanto cada índice B-tree ocupa 21 MB — uma diferença de armazenamento de aproximadamente 80 vezes.

Consulta em ID

Com o índice B-tree idx_t_brin_3 presente, o planejador escolhe uma varredura de índice precisa:

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

Saída:

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

Exclua o índice B-tree e execute a mesma consulta. O planejador passa a usar o índice BRIN e realiza uma varredura de heap com bitmap:

DROP INDEX idx_t_brin_3;

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

Saída:

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

Os valores Rows Removed by Index Recheck: 93 e Heap Blocks: lossy=2 confirmam o comportamento esperado do BRIN: o índice identificou dois intervalos de blocos candidatos e o executor reverificou todas as linhas nesses intervalos, descartando 93 que não correspondiam à condição.

Consulta em crt_time

Com o índice B-tree idx_t_brin_4 presente:

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';

Saída:

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

Exclua o índice B-tree e execute a mesma consulta usando o índice BRIN:

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';

Saída:

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

O índice BRIN restringe a varredura aos intervalos de blocos qualificados, e o executor realiza uma etapa de reverificação sobre essas linhas.