sql - Optimice la consulta con OFFSET en una tabla grande

postgresql pagination

Question

Tengo mesa

create table big_table (
id serial primary key,
-- other columns here
vote int
); 

Esta tabla es muy grande, aproximadamente 70 millones de filas, necesito consultar:

SELECT * FROM big_table
ORDER BY vote [ASC|DESC], id [ASC|DESC]
OFFSET x LIMIT n -- I need this for pagination

Como sabrán, cuando x es un número grande, las consultas como esta son muy lentas.

Para la optimización del rendimiento agregué índices:

create index vote_order_asc on big_table (vote asc, id asc);

y

create index vote_order_desc on big_table (vote desc, id desc);

EXPLAIN muestra que la consulta SELECT anterior usa estos índices, pero de todos modos es muy lenta con un gran desplazamiento.

¿Qué puedo hacer para optimizar las consultas con OFFSET en tablas grandes? ¿Quizás PostgreSQL 9.5 o incluso versiones más nuevas tienen algunas características? He buscado pero no he encontrado nada.

Answer 1

¿Has intentado separar la mesa?

La facilidad de administración, la escalabilidad y disponibilidad mejoradas, y una reducción en el bloqueo son razones comunes para particionar tablas. Mejorar el rendimiento de la consulta no es una razón para emplear la partición, aunque puede ser un efecto secundario beneficioso en algunos casos. En términos de rendimiento, es importante asegurarse de que su plan de implementación incluya una revisión del rendimiento de la consulta. Confirme que sus índices continúen admitiendo adecuadamente sus consultas después de que la tabla esté particionada, y verifique que las consultas que utilizan los índices agrupados y no agrupados se beneficien de la eliminación de la partición cuando corresponda.

http://sqlperformance.com/2013/09/sql-indexes/partitioning-benefits

Answer 2

Un gran OFFSET siempre será lento. Postgres tiene que ordenar todas las filas y contar las visibles hasta su desplazamiento. Para omitir todas las filas anteriores directamente , puede agregar una row_number indexada_número a la tabla (o crear una MATERIALIZED VIEW incluya dicha row_number ) y trabajar con WHERE row_number > x lugar de OFFSET x .

Sin embargo, este enfoque solo es sensible para datos de solo lectura (o principalmente). Implementar lo mismo para los datos de la tabla que pueden cambiar simultáneamente es más difícil. Debe comenzar definiendo exactamente el comportamiento deseado.

Sugiero un enfoque diferente para la paginación :

SELECT *
FROM big_table
WHERE (vote, id) > (vote_x, id_x) -- ROW values
ORDER BY vote, id -- needs to be deterministic
LIMIT n;

Donde vote_x e id_x son de la última fila de la página anterior (tanto para DESC como para ASC ). O desde el primero si navega hacia atrás .

El índice que ya tiene es compatible con la comparación de valores de fila, una característica que cumple con el estándar ISO SQL, pero no todos los RDBMS lo admiten.

CREATE INDEX vote_order_asc ON big_table (vote, id);

O para orden descendente:

SELECT *
FROM big_table
WHERE (vote, id) < (vote_x, id_x) -- ROW values
ORDER BY vote DESC, id DESC
LIMIT n;

Puede usar el mismo índice.
Le sugiero que declare que sus columnas NOT NULL NULLS FIRST|LAST o que se familiarice con la NULLS FIRST|LAST :

• PostgreSQL ordenar por fecha y hora asc, nulo primero?

Tenga en cuenta dos cosas en particular:

1. Los valores de ROW en la cláusula WHERE no se pueden reemplazar con campos de miembros separados. WHERE (vote, id) > (vote_x, id_x) no se puede reemplazar con:

   WHERE vote >= vote_x
   AND id > id_x

   Eso descartaría todas las filas con id <= id_x , mientras que solo queremos hacer eso para el mismo voto y no para el siguiente. La traducción correcta sería:

   WHERE (vote = vote_x AND id > id_x) OR vote > vote_x

   ... que no funciona tan bien con los índices y se vuelve cada vez más complicado para más columnas.

   Sería simple para una sola columna, obviamente. Ese es el caso especial que mencioné al principio.

2. La técnica no funciona para direcciones mixtas en ORDER BY como:

   ORDER BY vote ASC, id DESC

   Al menos no puedo pensar en una forma genérica de implementar esto de manera eficiente. Si al menos una de las dos columnas es de tipo numérico, puede usar un índice funcional con un valor invertido en (vote, (id * -1)) y usar la misma expresión en ORDER BY :

   ORDER BY vote ASC, (id * -1) ASC

Relacionado:

• Término de sintaxis SQL para ''WHERE (col1, col2) <(val1, val2)''
• Mejore el rendimiento para ordenar con columnas de muchas tablas

Observe en particular la presentación de Markus Winand I vinculada a:

• "Paginación realizada a la manera PostgreSQL"