Optimizar consulta con OFFSET en una tabla grande

Tengo table

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á, cuando x es un número grande, las consultas como esta son muy lentas.

Para la optimization del performance 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 desplazamiento grande.

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

Un OFFSET grande siempre va a ser lento. Postgres tiene que orderar todas las filas y contar las visibles hasta su compensación. Para omitir todas las filas previas directamente , puede agregar un row_number indexado a la tabla (o crear una MATERIALIZED VIEW incluya dicho row_number ) y trabajar con WHERE row_number > x lugar de OFFSET x .

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

Sugiero un enfoque diferente para la pagination :

 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 navegas hacia atrás .

La comparación de valores de filas es compatible con el índice que ya tiene, una function que cumple con ANSI SQL, pero no todos los RDBMS lo admiten.

 CREATE INDEX vote_order_asc ON big_table (vote, id); 

O por order 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.
Te sugiero que declares tus columnas NOT NULL o que te familiarices con la NULLS FIRST|LAST :

  • PostgreSQL orderar por datetime asc, null primero?

Tenga en count dos cosas en particular:

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

     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 less no puedo pensar en una forma genérica para implementar esto de manera eficiente. Si al less una de las dos columnas es de tipo numérico, puede usar un índice funcional con un valor invertido en (vote, (id * -1)) – y use la misma expresión en ORDER BY :

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

Relacionado:

  • Término de syntax SQL para 'WHERE (col1, col2) <(val1, val2)'
  • Mejore el performance para orderar con columnas de muchas tablas

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

  • "Paginación hecha de la manera PostgreSQL"

¿Has intentado repartir la table?

La facilidad de administración, la escalabilidad y la disponibilidad mejoradas, y la networkingucción del locking son razones comunes para dividir las tablas. Mejorar el performance de las consultas no es una razón para emplear la partición, aunque puede ser un efecto secundario beneficioso en algunos casos. En términos de performance, es importante asegurarse de que su plan de implementación incluya una revisión del performance de las consultas. Confirme que sus índices continúen respaldando adecuadamente sus consultas después de que la tabla esté particionada, y verifique que las consultas que usan 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