Cómo funcionan LIMIT y OFFSET de fondo en PostgreSQL

Cuando hablamos de paginación, ¿cuál es el primer enfoque que te viene a la mente? En mi caso, inmediatamente pienso en ‘LIMIT’ y ‘OFFSET’, así que los utilizo. ¿Es bueno, verdad? Bueno, en mi caso, me di cuenta de que era ineficiente para paginaciones grandes.

Profundicemos un poco más en esto.

1. Configuración

He creado una demostración sencilla con una tabla de ‘usuarios’ poblada con 10 millones de filas.

-- Create the user table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    gender VARCHAR(10)
);

-- Generate and insert 10 million rows using generate_series
INSERT INTO users (name, gender)
SELECT
    'User ' || gs.num AS name,
    CASE WHEN gs.num % 2 = 0 THEN 'Male' ELSE 'Female' END AS gender
FROM generate_series(1, 10000000) AS gs(num);

2. SQL LIMIT y OFFSET

LIMIT y OFFSET te permiten recuperar un subconjunto de filas de la consulta.

Escribamos la consulta SQL para la paginación.

SELECT * FROM users ORDER BY id LIMIT 500 OFFSET 1000;

Para mí, esto parece magia. ¿Cómo sabe Postgres saltar esos registros? Bueno, en realidad no sabe cómo saltar esos registros.

Analicemos la consulta SQL con el comando ‘EXPLAIN ANALYZE’.

EXPLAIN ANALYZE SELECT * FROM users ORDER BY id LIMIT 500 OFFSET 1000;

Después de ejecutar la consulta SQL, obtuvimos el plan de la consulta y las estadísticas de ejecución.

Adentrémonos en detalle en el plan de la consulta.

El plan de la consulta consta de dos partes:

En primer lugar, observamos que Postgres elige un Index Scan en la primary key ya que estamos ordenando los resultados según el ‘id’ utilizando la cláusula ORDER BY. En segundo lugar, Postgres limita los resultados para devolver las filas requeridas.

Si examinamos el tiempo real y las filas recuperadas por Postgres, vemos que en realidad son 1500 en lugar de 500.

Esto significa que al utilizar LIMIT y OFFSET no se omiten los registros, se recuperan todos los registros en memoria y recae en que Postgres los filtre.

Si nos referimos a la documentación de PostgreSQL, encontramos que esto es realmente así. ‘Las filas omitidas por una cláusula OFFSET aún deben calcularse dentro del servidor; por lo tanto, un OFFSET grande podría ser ineficiente’.

Podemos decir que LIMIT y OFFSET funcionan bien con offsets pequeños, pero ¿existe un enfoque mejor para offsets más grandes?

3. ¿Existe un enfoque mejor para un OFFSET grande?

Podemos aprovechar el ID secuencial para obtener el mismo resultado con mayor eficiencia.

SELECT * FROM users WHERE id between 1001 and 1500;

Analicemos la consulta.

EXPLAIN ANALYZE SELECT * FROM users WHERE id between 1001 and 1500;

Después de ejecutar la consulta, podemos ver que recupera solo las 500 filas requeridas.

4. Conclusión

Podemos concluir que aunque el uso de LIMIT y OFFSET para la paginación puede funcionar bien para offsets pequeños, para offsets más grandes, podría ser ineficiente ya que debe cargar todo en memoria y filtrar las filas. Hay otras formas de lograr el mismo resultado. Aprovechar columnas ordenadas e índices podría ser un enfoque mejor para desplazamientos más grandes.