Implementar la paginación eficientemente en consultas SQL con seek

La paginación habitualmente se implementa con las palabras reservadas limit y offset del lenguaje SQL pero esto es ineficiente ya que para llegar los resultados de las últimas páginas la base de datos ha de recuperar antes todos los resultados anteriores. Cuando hay varios cientos de miles o millones de filas en una tabla esto es ineficiente y hace que las consultas sean lentas y añadan una importante carga al servidor de base de datos que al final afecta al rendimiento de la aplicación o su capacidad para atender a gran número de peticiones.

Además del rendimiento, otro problema de la paginación con limit y offset es que si se insertan filas en una página anterior mientras se están recorriendo los resultados, uno o varios resultados al avanzar por las páginas en la típica tabla de resultados en una aplicación web podría aparecer dos veces, en un proceso automatizado sería aún peor ya que un resultado podría procesarse dos veces.

La paginación con limit y offset permite ir a una página directamente en una búsqueda pero en los casos que hay miles de resultados realmente a un usuario no le interesa ir a una determinada página cuando hay cientos de páginas, en un proceso automatizado ir a una página en concreto tampoco suele ser un requerimiento.

jOOQ con la cláusula seek permite hacer la paginación eficiente de una forma cómoda. La técnica se basa en ordenar los resultados por unas determinadas columnas y filtrar por condición where sobre las mismas columnas los resultados anteriores, los valores por los que se filtra en la condición son los obtenidos de la última página.

Haciendo seek la paginación es más eficiente ya que la base de datos no necesita recuperar los datos de las páginas anteriores ya que los descarta utilizando la cláusula where que al final es en lo que se traduce la clausula seek. Y se elimina el problema de que se inserten datos en páginas anteriores y alguno se pudiese aparecer dos veces en los resultados ya que manteniendo la ordenación de la consulta si se insertan filas en páginas anteriores no afectarán a las páginas siguientes.

1
2
3
4
5
6
7
8
9

import org.jooq.DSLContext;
...
private DSLContext context;
...
context.selectFrom(Tables.PRODUCTO)
    .orderBy(PRODUCTO.CANTIDAD, PRODUCTO.ID)
    .seek(3l, 2l)
    .limit(10)
    .fetchInto(Producto.class);

La SQL generada por jOOQ es la siguiente donde la clausula seek se añade como una condición en la cláusula where. El campo de la clausula seek coincide con el campo del criterio de ordenación, el operador mayor que en la condición coincide también con el orden ascendente del order by.

1
2
3
4

   select "PRODUCTO"."ID", "PRODUCTO"."NOMBRE", "PRODUCTO"."DESCRIPCION", "PRODUCTO"."CANTIDAD", "PRODUCTO"."FECHA"
     from "PRODUCTO"
    where ("PRODUCTO"."CANTIDAD", "PRODUCTO"."ID") > (cast(? as bigint), cast(? as bigint))
 order by "PRODUCTO"."CANTIDAD", "PRODUCTO"."ID" limit ?

Para que el seek sea correcto los valores de las filas para los campos que forman parte del seek han de ser únicos por eso entre los campos de los ejemplos se incluye el identificativo de la fila, que siempre se incluye como último campo si hubiese más criterios de ordenación y valores para el seek.

Si además se tiene un índice para los campos que forman parte del seek el rendimiento será muy alto y la diferencia entre la primera y la última página mínimo, además se puede considerar independiente del número de filas de la tabla. Son varias las ventajas perdiendo solo la capacidad de navegar a cierta página pero en la mayoría de los casos esto es asumible.

En los siguientes interesantes artículos se comenta detalladamente como implementar la paginación eficientemente y se dan más detalles.

• Pagination Done the PostgreSQL Way
• Why Most Programmers Get Pagination Wrong
• Pagination with PostgreSQL 9.3: counting number of pages