sql - Optimice la consulta con OFFSET en una tabla grande
postgresql pagination (2)
¿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
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.
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
:
Tenga en cuenta dos cosas en particular:
-
Los valores de
ROW
en la cláusulaWHERE
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_xEso 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.
-
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 enORDER 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: