La mejor manera de obtener el recuento de resultados antes de que se aplicara LIMIT

Solución:

SQL puro

Las cosas han cambiado desde 2008. Puede usar una función de ventana para obtener el recuento completo y el resultado limitado en una consulta. Introducido con PostgreSQL 8.4 en 2009.

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

Tenga en cuenta que esto puede ser considerablemente más caro que sin el recuento total. Todas las filas deben contarse, y un posible atajo que tome solo las filas superiores de un índice coincidente puede que ya no sea útil.
No importa mucho con mesas pequeñas o full_count <= OFFSET + LIMIT. Importa para un full_count.

Caso de la esquina: cuando OFFSET es al menos tan grande como el número de filas de la consulta base, sin fila es regresado. Entonces también obtienes no full_count. Posible alternativa:

• Ejecute una consulta con LIMIT / OFFSET y también obtenga el número total de filas

Secuencia de eventos en un SELECT consulta

(0. Las CTE se evalúan y se materializan por separado. En Postgres 12 o posterior, el planificador puede incluir esas subconsultas antes de comenzar a trabajar). Aquí no.

1. WHERE cláusula (y JOIN condiciones, aunque ninguna en su ejemplo) filtran filas calificadas de la (s) tabla (s) base. El resto se basa en el subconjunto filtrado.

(2. GROUP BY y las funciones agregadas irían aquí.) No aquí.

(3. Otro SELECT las expresiones de lista se evalúan, basándose en columnas agrupadas / agregadas.) No aquí.

4. Las funciones de ventana se aplican dependiendo de la OVER cláusula y la especificación del marco de la función. Lo simple count(*) OVER() se basa en todas las filas calificadas.

5. ORDER BY

(6. DISTINCT o DISTINCT ON iría aquí.) No aquí.

7. LIMIT / OFFSET se aplican en función del orden establecido para seleccionar filas para devolver.

LIMIT / OFFSET se vuelve cada vez más ineficaz con un número creciente de filas en la tabla. Considere enfoques alternativos si necesita un mejor rendimiento:

• Optimice la consulta con OFFSET en una tabla grande

Alternativas para obtener el recuento final

Existen enfoques completamente diferentes para obtener el recuento de filas afectadas (no el recuento completo antes OFFSET Y LIMIT se aplicaron). Postgres tiene una contabilidad interna de cuántas filas se vieron afectadas por el último comando SQL. Algunos clientes pueden acceder a esa información o contar filas ellos mismos (como psql).

Por ejemplo, puede recuperar el número de filas afectadas en plpgsql inmediatamente después de ejecutar un comando SQL con:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Detalles en el manual.

O puedes usar pg_num_rows en PHP. O funciones similares en otros clientes.

Relacionado:

• Calcular el número de filas afectadas por la consulta por lotes en PostgreSQL

Como describo en mi blog, MySQL tiene una función llamada SQL_CALC_FOUND_ROWS. Esto elimina la necesidad de realizar la consulta dos veces, pero aún necesita realizar la consulta en su totalidad, incluso si la cláusula de límite hubiera permitido que se detuviera antes.

Hasta donde yo sé, no existe una característica similar para PostgreSQL. Una cosa a tener en cuenta cuando se realiza la paginación (la cosa más común para la que se usa LIMIT en mi humilde opinión): hacer un “OFFSET 1000 LIMIT 10” significa que la base de datos tiene que buscar por lo menos 1010 filas, incluso si solo le da 10. Una forma más eficaz de hacerlo es recordar el valor de la fila por la que está ordenando para la fila anterior (la 1000 en este caso) y reescribir la consulta de esta manera: “.. . DONDE order_row> value_of_1000_th LIMIT 10 “. La ventaja es que lo más probable es que “order_row” esté indexado (si no, tienes un problema). La desventaja es que si se agregan nuevos elementos entre las visitas a la página, esto puede desincronizarse un poco (pero, de nuevo, es posible que los visitantes no lo puedan observar y puede suponer una gran ganancia de rendimiento).

Puede mitigar la penalización del rendimiento si no ejecuta la consulta COUNT () cada vez. Almacene en caché el número de páginas durante, digamos, 5 minutos antes de que se vuelva a ejecutar la consulta. A menos que vea una gran cantidad de INSERT, eso debería funcionar bien.

---

---

Utiliza Nuestro Buscador