[sql] PostgreSQL-열에 대한 Max 값이있는 행 가져 오기

158k 의사 랜덤 행이있는 테이블 (usr_id는 0에서 10k trans_id사이에 균일하게 분포 , 0에서 30 사이에 균일하게 분포),

아래에서 쿼리 비용 xxx_cost은 필요한 I / O 및 CPU 리소스에 대한 가중치 함수 추정 인 Postgres의 비용 기반 최적화 프로그램의 비용 추정 (Postgres의 기본값 포함 )을 참조합니다. PgAdminIII를 시작하고 “Query / Explain options”를 “Analyze”로 설정 한 쿼리에서 “Query / Explain (F7)”을 실행하여이를 얻을 수 있습니다.

• Quassnoy의 쿼리는 1.3 초에서 비용 745k의 추정 (!), 그리고 완료가 (에 화합물 지수 부여를 ( usr_id, trans_id, time_stamp))
• Bill의 쿼리의 예상 비용은 93k이며 2.9 초 만에 완료됩니다 (( usr_id, trans_id) 에 대한 복합 인덱스를 제공함 ).
• 쿼리 # 1 아래 16K의 비용 추정치를 가지며, 800ms의 완료가 (화합물에 주어진 인덱스 ( usr_id, trans_id, time_stamp))
• 쿼리 # 2 아래 14K의 비용 추정치를 가지며, 800ms의 완료는 (ON 화합물 함수 인덱스를 부여 ( usr_id, EXTRACT(EPOCH FROM time_stamp), trans_id))
  • 이것은 Postgres 전용입니다.
• 아래 쿼리 # 3 (포스트 그레스가 8.4+) 쿼리 2 비교 (또는 더 이상) 비용 추정치 및 종료 시간을 갖는다 (복합 지표 (에 기재를 usr_id, time_stamp, trans_id)); lives테이블을 한 번만 스캔하는 이점이 있으며 메모리에서 정렬을 수용하기 위해 임시로 (필요한 경우) work_mem 을 늘리면 모든 쿼리 중에서 훨씬 빠릅니다.

위의 모든 시간에는 전체 10k 행 결과 집합 검색이 포함됩니다.

목표는 예상 비용에 중점을두고 최소 비용 예상 과 최소 쿼리 실행 시간입니다. 쿼리 실행은 런타임 조건 (예 : 관련 행이 이미 메모리에 완전히 캐시되었는지 여부)에 크게 좌우 될 수 있지만 비용 추정은 그렇지 않습니다. 다른 한편으로, 비용 견적은 정확히 견적이라는 것을 명심하십시오.

최적의 쿼리 실행 시간은로드없이 전용 데이터베이스에서 실행될 때 얻을 수 있습니다 (예 : 개발 PC에서 pgAdminIII로 플레이). 쿼리 시간은 실제 머신로드 / 데이터 액세스 확산에 따라 프로덕션에 따라 다릅니다. 한 쿼리가 다른 쿼리보다 약간 빠르지 만 (<20 %) 비용 이 훨씬 높은 경우 일반적으로 실행 시간 은 더 높지만 비용 은 더 낮은 쿼리 를 선택하는 것이 더 현명합니다.

쿼리가 실행될 때 프로덕션 시스템의 메모리에 대한 경쟁이 없을 것으로 예상되는 경우 (예 : RDBMS 캐시 및 파일 시스템 캐시는 동시 쿼리 및 / 또는 파일 시스템 활동에 의해 스 래싱되지 않음) 얻은 쿼리 시간 독립형 (예 : 개발 PC의 pgAdminIII) ​​모드가 대표적입니다. 프로덕션 시스템에 경합이있는 경우 비용이 낮은 쿼리는 캐시에 많이 의존하지 않는 반면 비용이 높은 쿼리는 동일한 데이터를 반복해서 다시 방문 하므로 쿼리 시간이 예상 비용 비율에 비례하여 저하 됩니다 (트리거링 안정적인 캐시가없는 경우 추가 I / O), 예 :

              cost | time (dedicated machine) |     time (under load) |
-------------------+--------------------------+-----------------------+
some query A:   5k | (all data cached)  900ms | (less i/o)     1000ms |
some query B:  50k | (all data cached)  900ms | (lots of i/o) 10000ms |

ANALYZE lives필요한 인덱스를 만든 후 한 번 실행하는 것을 잊지 마십시오 .

쿼리 # 1

-- incrementally narrow down the result set via inner joins
--  the CBO may elect to perform one full index scan combined
--  with cascading index lookups, or as hash aggregates terminated
--  by one nested index lookup into lives - on my machine
--  the latter query plan was selected given my memory settings and
--  histogram
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
    SELECT
      usr_id,
      MAX(time_stamp) AS time_stamp_max
     FROM
      lives
     GROUP BY
      usr_id
  ) AS l2
 ON
  l1.usr_id     = l2.usr_id AND
  l1.time_stamp = l2.time_stamp_max
 INNER JOIN (
    SELECT
      usr_id,
      time_stamp,
      MAX(trans_id) AS trans_max
     FROM
      lives
     GROUP BY
      usr_id, time_stamp
  ) AS l3
 ON
  l1.usr_id     = l3.usr_id AND
  l1.time_stamp = l3.time_stamp AND
  l1.trans_id   = l3.trans_max

쿼리 # 2

-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
--  by far the least I/O intensive operation even in case of great scarcity
--  of memory (least reliant on cache for the best performance)
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
   SELECT
     usr_id,
     MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
       AS compound_time_stamp
    FROM
     lives
    GROUP BY
     usr_id
  ) AS l2
ON
  l1.usr_id = l2.usr_id AND
  EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
  l1.trans_id = l2.compound_time_stamp[2]

2013/01/29 갱신

마지막으로 버전 8.4부터 Postgres는 Window 함수를 지원하므로 다음과 같이 간단하고 효율적으로 작성할 수 있습니다.

쿼리 # 3

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

답변

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;

SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
  ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp
   OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;

SELECT *
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
    SELECT usr_id, time_stamp, trans_id
    FROM lives sq
    WHERE sq.usr_id = outer.usr_id
    ORDER BY trans_id, time_stamp
    LIMIT 1
)

SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

SELECT  l.*
FROM    (
        SELECT DISTINCT usr_id
        FROM   lives
        ) lo, lives l
WHERE   l.ctid = (
        SELECT ctid
        FROM   lives li
        WHERE  li.usr_id = lo.usr_id
        ORDER BY
          time_stamp DESC, trans_id DESC
        LIMIT 1
        )