El índice de cobertura no es solo otra característica que puede ser útil. Esta cosa es puramente práctica. Sin ellos, Index Only Scan no puede dar una victoria. Aunque el índice de cobertura en diferentes situaciones es efectivo de diferentes maneras.
No se trata realmente de cubrir índices: estrictamente hablando, los llamados índices inclusivos han aparecido en Postgres. Pero, en orden: un índice de cobertura es un índice que contiene todos los valores de columna requeridos por la consulta; sin embargo, ya no se requiere acceso a la tabla en sí. Casi. Puede
leer sobre "casi" y otros matices en un artículo de
Yegor Rogov , incluido en su serie de índice de 10 (!) Partes. Y el
índice inclusivo se crea específicamente para buscar consultas típicas: los valores de los campos que no se pueden buscar se agregan al índice de búsqueda, solo se necesitan para no volver a consultar la tabla. Dichos índices se forman con la palabra clave INCLUDE.
Anastasia Lubennikova (Postgres Professional) finalizó el método btree para que se pudieran incluir columnas adicionales en el índice. Este parche se incluyó en PostgreSQL 11. Pero los parches para los métodos de acceso GiST / SP-GiST no tuvieron tiempo de madurar antes del lanzamiento de esta versión. Para el 12º GiST maduró.
Un deseo constructivo de tener índices inclusivos para GiST surgió hace mucho tiempo: un parche de prueba de Andrey Borodin fue
ofrecido a la comunidad a mediados de abril de 2018. Hizo todo el trabajo básico, muy difícil.
A principios de agosto de 2019, Alexander Korotkov agregó mejoras cosméticas y comprometió el parche.
Para demostraciones y algunas investigaciones, generaremos un conjunto de 3 millones de rectángulos. Al mismo tiempo, algunas palabras sobre el tipo de cuadro, ya que no todas las manipulaciones con él son intuitivas.
El tipo de cuadro, es decir, el rectángulo, ha estado durante mucho tiempo en Postgres, está definido por 2 puntos (el punto de tipo geométrico): los vértices opuestos del rectángulo (es decir, el rectángulo no puede ser oblicuo, lleno a un lado). Leemos en la
documentación : "los valores del cuadro de tipo se escriben en una de las siguientes formas:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
En la práctica, tienes que escribir, por ejemplo, así:
SELECT box('1,2', '3,4'); box
Primero, Postgres nos muestra el vértice superior derecho, luego el inferior izquierdo. Si escribimos así,
SELECT box('5,2', '3,4'); box
entonces nos aseguraremos de que Postgres no haya dado los picos que le dieron. Calculó la esquina superior derecha e inferior izquierda desde nuestra esquina superior izquierda e inferior derecha. Esta es una propiedad conveniente cuando la ubicación de los vértices no se conoce de antemano, por ejemplo, en caso de generación aleatoria. La notación '1,2', '3,4' es equivalente al punto (1,2), punto (3,4). Este formulario es a veces más conveniente.
Para negocios: busque en 3 millones de rectángulos
CREATE TABLE boxes(id serial, thebox box, name text);
Generaremos 3 millones de rectángulos aleatorios. Queremos una distribución normal, pero para no usar la extensión
tablefunc , usamos el enfoque "pobre": usamos random () - random (), que también da una buena imagen (ver fig.) Con rectángulos, cuanto más grande, más cerca del centro. Sus centros de gravedad también son aleatorios. Dichas distribuciones son características de algunos tipos de datos de ciudades reales. Y aquellos que quieran profundizar en las leyes de estadísticas o actualizar recuerdos pueden leer sobre la diferencia de variables aleatorias, por ejemplo,
aquí .
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
El tamaño de la tabla que muestra
\dt+ es de 242 MB. Ahora puedes comenzar la búsqueda.
Estamos buscando sin un índice:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Vemos que hay una exploración paralela secuencial - exploración secuencial (aunque paralelizada).
Cree un índice regular, no inclusivo:
CREATE INDEX ON boxes USING gist(thebox);
El tamaño del índice
boxes_thebox_idx , que muestra
\di+ , 262MB. En respuesta a la misma solicitud, obtenemos:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
El tiempo de búsqueda se redujo en un factor de tres y, en lugar de la exploración paralela paralela, recibieron una exploración de índice de mapa de bits. No se paraleliza, pero funciona más rápido.
Ahora elimine el índice anterior y cree uno inclusivo:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
Índice de
boxes_thebox_name_idx fatter: 356MB. Vamos:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Se utiliza Index Only Scan, pero la imagen es triste: el tiempo es casi 2 veces más largo que sin ella. Leemos el manual del creador de índices, en la
parte I :
‹Los índices Rang PostgreSQL no contienen información que le permita juzgar la visibilidad de las filas. Por lo tanto, el método de acceso devuelve todas las versiones de filas que se encuentran bajo la condición de búsqueda, independientemente de si son visibles para la transacción actual o no. Sin embargo, si el mecanismo de indexación tuviera que mirar en la tabla cada vez para determinar la visibilidad, este método de escaneo no sería diferente del escaneo de índice ordinario. El problema se resuelve por el hecho de que PostgreSQL admite el llamado mapa de visibilidad para tablas, en el que el proceso de vacío marca páginas en las que los datos no han cambiado lo suficiente como para que todas las transacciones lo vean, independientemente de la hora de inicio y el nivel de aislamiento. Si el identificador de la fila devuelta por el índice se refiere a dicha página, entonces no se puede verificar la visibilidad. ››
Hacemos VACÍO. Repetir
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
¡Un asunto completamente diferente! El doble de la ganancia en comparación con el índice no inclusivo.
Selectividad y ganancia
El rendimiento de los índices inclusivos depende en gran medida de la selectividad de las condiciones en las consultas. Para investigar un poco esta dependencia, resolveremos el problema inverso: generaremos una etiqueta con un índice de punto de tipo y buscaremos cuántos puntos caerán en el cuadro dado. Extiende los puntos al cuadrado de manera uniforme.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
El tamaño de la tabla es de 211 MB.
CREATE INDEX on test_covergist USING gist(tochka);
Tamaño 213 MB.
Obviamente tomaremos todos los puntos disponibles en un cuadrado:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Le pedimos a EXPLAIN que mostrara los tampones. Será útil. Ahora el tiempo de ejecución de la solicitud es más de 2 segundos, se puede ver que Buffers: lectura compartida = 54287. En otra situación, podríamos ver una mezcla de lectura compartida y éxito compartido, es decir, algunos búferes se leen desde el disco (o desde el caché del sistema operativo) y otros desde el caché del búfer. Conocemos el tamaño aproximado de la tabla y los índices, por lo que nos protegeremos configurando buffers compartidos para que todo encaje: reinicie Postgres con la opción
-o "-c shared_buffers=1GB"
Ahora:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Es decir, la lectura compartida se convirtió en un éxito compartido, y el tiempo se redujo tres veces.
Otro detalle importante en EXPLICAR: se devuelven 3 millones de puntos, y el pronóstico del número de registros devuelto es de 3 mil. Spoiler: este número no cambiará con ninguna selectividad. El optimizador no sabe cómo evaluar la cardinalidad cuando se trabaja con tipos de cuadro o punto. Y el plan no cambiará: para cualquier tamaño del rectángulo, habrá un Escaneo de índice de mapa de bits en test_covergist_tochka_idx.
Aquí hay dos medidas más con el número de registros emitidos, que difieren en órdenes de magnitud:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Devuelve 10 veces menos registros (real ... filas = 269882), el tiempo ha disminuido en aproximadamente 5 veces.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
El contenido de un cuadrado de 30K × 30K (2780) se cuenta en solo 16 ms. Y cuando hay docenas de registros, ya se extraen en fracciones de ms, y tales mediciones no son muy confiables.
Finalmente, mida lo mismo con el índice inclusivo:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
Tamaño 316 MB.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
El tiempo es casi el mismo que con un índice convencional, aunque Index Only Scan.
Pero:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Y fue de 151 ms. Y, en consecuencia:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Esto ya es una fracción de ms para los mismos registros de 2780 puntos.
Amortiguadores como pistolas
Se puede buscar y encontrar una explicación en una escopeta que aún no ha disparado pero que estaba colgada en la pared: la cantidad de bloques leídos. En el caso de un índice inclusivo, solo se leen los bloques del índice (Heap Fetches: 0). En tres casos, estos fueron los números 40492, 3735 y 52. Pero cuando se usa el índice regular, los bloques leídos consisten en la suma de los bits leídos en el índice de Bitmap Heap Scan (54248 con 3 millones de registros) y los que tuvieron que leerse del montón (27223) , ya que el campo de nombre no se puede extraer de un índice regular. 54248 + 27223 = 81471. La exclusiva fue 40492. Para otros dos casos: 29534 + 2510 = 31044 y 2655 + 31 = 2686. En el caso de un índice regular, se leen más bloques de todos modos, pero con una mejora en la selectividad, el número de bloques leídos comienza a diferir en órdenes de magnitud en lugar de 2 veces debido al hecho de que el número de bloques necesarios de un montón disminuye más lentamente que leer bloques de índice.
Pero tal vez el punto no es la selectividad, sino simplemente el tamaño de la tabla. Por si acaso, repetimos los mismos pasos, generando una tabla con 300 mil, y no 3 millones de registros:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Luego, repita lo mismo para el índice inclusivo. Aquí están los resultados:
En el caso del 100% de cobertura de puntos, la consulta fue incluso un poco más lenta que con el índice habitual. Además, como en el caso de 3 millones, todo encajó. Es decir, la selectividad es importante.
Nuestra compañía probó índices GiST inclusivos en datos reales, un conjunto con varios millones de rectángulos en un mapa de Moscú. La conclusión es la misma: en muchas situaciones, tales índices aceleran notablemente las consultas. Pero el artículo no puede ilustrarse con imágenes y números de pruebas: estos datos no son de dominio público.
En lugar de una conclusión
Regresemos por un momento a rectángulos aleatorios. Tratemos de hacer lo mismo con spgist. Puede recordar o descubrir qué es entender las diferencias entre SP-GiST y GiST leyendo el artículo
Índices en PostgreSQL - 6 . Crea un índice inclusivo:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
Por desgracia, para SP-GiST, los índices inclusivos aún no se implementan.
¡Entonces hay margen de mejora!