LIMIT/OFFset을 사용하여 쿼리 실행 및 총 행 수 가져오기

LIMIT/OFFset을 사용하여 쿼리 실행 및 총 행 수 가져오기

페이지화를 위해 다음과 같이 쿼리를 실행해야 합니다.LIMIT그리고.OFFSET조항그러나 해당 쿼리에서 반환되는 행 수를 계산해야 합니다.LIMIT그리고.OFFSET조항

실행할 대상:

SELECT * FROM table WHERE /* whatever */ ORDER BY col1 LIMIT ? OFFSET ?

그리고:

SELECT COUNT(*) FROM table WHERE /* whatever */

동시에.Postgres가 이를 최적화하여 둘 다 개별적으로 실행하는 것보다 더 빠르게 실행할 수 있는 방법이 있습니까?

예. 간단한 창 기능으로:

SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
OFFSET ?
LIMIT  ?

비용은 총 숫자가 없는 것보다 훨씬 더 비싸지만 일반적으로 두 개의 개별 쿼리보다 더 저렴합니다.Postgres는 실제로 어느 쪽이든 모든 행을 계산해야 하므로, 적격 행의 총 수에 따라 비용이 부과됩니다.세부사항:

• LIMIT가 적용되기 전에 결과 수를 가져오는 가장 좋은 방법

하지만 Dani가 지적했듯이, 언제OFFSET적어도 기본 쿼리에서 반환된 행 수만큼 큰 경우에는 반환되는 행이 없습니다.그래서 우리는 또한 받지 못합니다.full_count.

허용되지 않을 경우 항상 전체 카운트를 반환할 수 있는 해결 방법은 CTE와OUTER JOIN:

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

다음과 같이 NULL 값의 행 하나를 얻을 수 있습니다.full_count첨부된 경우OFFSET너무 큽니다.그렇지 않으면 첫 번째 쿼리에서와 같이 모든 행에 추가됩니다.

모든 NULL 값을 가진 행이 유효한 결과일 경우 확인해야 합니다.offset >= full_count빈 행의 원점을 명확히 합니다.

이 경우에도 기본 쿼리는 한 번만 실행됩니다.그러나 쿼리에 오버헤드를 더 추가하고 카운트에 대한 기본 쿼리를 반복하는 것보다 적은 경우에만 비용을 지불합니다.

최종 정렬 순서를 지원하는 인덱스를 사용할 수 있는 경우 다음을 포함하는 것이 좋습니다.ORDER BYCTE에서 (중복적으로).

Erwin Brandstetter의 답변은 매력적으로 작동하지만 다음과 같이 모든 행의 총 행 수를 반환합니다.

col1 - col2 - col3 - total
--------------------------
aaaa - aaaa - aaaa - count
bbbb - bbbb - bbbb - count
cccc - cccc - cccc - count

다음과 같이 총 카운트를 한 번만 반환하는 방법을 사용하는 것을 고려할 수 있습니다.

total - rows
------------
count - [{col1: 'aaaa'},{col2: 'aaaa'},{col3: 'aaaa'}
         {col1: 'bbbb'},{col2: 'bbbb'},{col3: 'bbbb'}
         {col1: 'cccc'},{col2: 'cccc'},{col3: 'cccc'}]

SQL 쿼리:

SELECT
    (SELECT COUNT(*) 
     FROM table
     WHERE /* sth */
    ) as count, 
    (SELECT json_agg(t.*) FROM (
        SELECT * FROM table
        WHERE /* sth */
        ORDER BY col1
        OFFSET ?
        LIMIT ?
    ) AS t) AS rows 

edit: 이 답변은 필터링되지 않은 테이블을 검색할 때 유효합니다.누군가에게 도움이 될 수도 있지만 처음 질문에 정확히 답하지 못할 수도 있는 경우를 대비해서 허락하겠습니다.

정확한 값이 필요하다면 어윈 브랜드스터의 대답은 완벽합니다.그러나 큰 표에서는 종종 꽤 좋은 근사치만 필요합니다.Postgres를 사용하면 각 행을 평가할 필요가 없으므로 훨씬 더 빨라집니다.

SELECT *
FROM (
    SELECT *
    FROM tbl
    WHERE /* something */
    ORDER BY /* something */
    OFFSET ?
    LIMIT ?
    ) data
RIGHT JOIN (SELECT reltuples FROM pg_class WHERE relname = 'tbl') pg_count(total_count) ON true;

저는 사실 외부화할 수 있는 이점이 있는지 잘 모르겠습니다.RIGHT JOIN또는 표준 쿼리에 있는 것처럼 사용합니다.시험해 볼 만하겠군요.

SELECT t.*, pgc.reltuples AS total_count
FROM tbl as t
RIGHT JOIN pg_class pgc ON pgc.relname = 'tbl'
WHERE /* something */
ORDER BY /* something */
OFFSET ?
LIMIT ?

아니요.

이론적으로 충분히 복잡한 기계를 사용하여 개별적으로 운영하는 것보다 약간의 이득을 얻을 수 있을 것입니다.그러나 조건과 일치하는 행 수를 알고 싶다면 LIMITED 하위 집합이 아닌 해당 행 수를 계산해야 합니다.

언급URL : https://stackoverflow.com/questions/28888375/run-a-query-with-a-limit-offset-and-also-get-the-total-number-of-rows