Índices BRIN - PolarDB - Centro de documentación de Alibaba Cloud

BRIN (Block Range Index) almacena estadísticas de resumen para rangos de bloques de datos, en lugar de indexar identificadores de filas individuales como lo hace un B-tree. Esto hace que los índices BRIN sean extremadamente compactos y generen una sobrecarga mínima en las operaciones de escritura, actualización y eliminación, a costa de una localización de filas menos precisa.

Cómo funciona

Un índice BRIN divide la tabla en rangos de bloques contiguos y registra estadísticas de resumen para cada rango. Durante la ejecución de consultas, el planificador omite los rangos cuyas estadísticas no pueden satisfacer la condición de la consulta y examina únicamente los rangos candidatos mediante un bitmap heap scan.

Dado que el índice almacena resúmenes en lugar de ubicaciones exactas de filas, es con pérdida: el ejecutor debe volver a comprobar cada fila de un rango de bloques candidato frente a la condición de la consulta. Por esta razón, los planes de ejecución de consultas con BRIN muestran Recheck Cond y Rows Removed by Index Recheck. Ambos elementos corresponden al comportamiento esperado y no representan errores.

El parámetro de almacenamiento pages_per_range controla el equilibrio entre el tamaño del índice y su precisión:

Valor de `pages_per_range`	Tamaño del índice	Precisión	Bloques omitidos
Pequeño (por ejemplo, 1)	Mayor	Mayor	Más
Grande	Menor	Menor	Menos

Operadores compatibles

Los índices BRIN admiten los siguientes operadores de comparación: <, <=, =, >=, >.

Cuándo usar índices BRIN

BRIN funciona mejor cuando los valores de la columna se correlacionan de forma natural con su orden físico de almacenamiento. Es decir, las filas insertadas secuencialmente tienden a tener valores de columna que aumentan de manera consecutiva. Algunos ejemplos comunes son:

Datos de series temporales de solo anexión: entradas de registro, lecturas de sensores o flujos de eventos insertados en orden cronológico.
Identificadores autoincrementables: filas insertadas de forma secuencial, de modo que el orden físico coincide con el orden del identificador.

BRIN no sustituye a B-tree. Para búsquedas puntuales o consultas sobre datos distribuidos aleatoriamente, utilice un índice B-tree.

Ejemplo: tamaño del índice y rendimiento de consultas

A continuación se muestra un ejemplo donde se crea una tabla de 1,000,000 de filas y se comparan los índices BRIN y B-tree en cuanto a tamaño de almacenamiento y ejecución de consultas.

Configuración inicial

Cree la tabla e inserte 1,000,000 de filas:

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

Compruebe el orden físico de las filas (la columna ctid refleja la ubicación en el heap):

SELECT ctid,* FROM t_brin limit 3;

Salida:

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

Las columnas id y crt_time aumentan de forma monótona junto con el orden físico de las filas. Por ello, esta tabla resulta adecuada para los índices BRIN.

Cree dos índices BRIN y dos índices B-tree para la comparación. Ambos índices BRIN usan pages_per_range=1 para obtener la máxima precisión:

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

Consulte el tamaño de los índices:

\di+

Salida:

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 índices BRIN ocupan menos de 400 kB, mientras que cada índice B-tree requiere 21 MB. Esto representa una diferencia de almacenamiento de aproximadamente 80 veces.

Consulta por id

Cuando está presente el índice B-tree idx_t_brin_3, el planificador elige un recorrido de índice preciso:

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

Salida:

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

Elimine el índice B-tree y ejecute la misma consulta. El planificador recurre al índice BRIN y utiliza un bitmap heap scan:

DROP INDEX idx_t_brin_3;

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

Salida:

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

Las líneas Rows Removed by Index Recheck: 93 y Heap Blocks: lossy=2 confirman el comportamiento esperado de BRIN. El índice identificó dos rangos de bloques candidatos y el ejecutor volvió a comprobar todas las filas de dichos rangos, donde descartó 93 que no coincidían.

Consulta por crt_time

Con el í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';

Salida:

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

Elimine el índice B-tree y ejecute la misma consulta con el í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';

Salida:

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

El índice BRIN limita el examen a los rangos de bloques que cumplen la condición. Posteriormente, el ejecutor aplica una segunda pasada de comprobación sobre esas filas.