BY 쿼리 최적화 그룹은 사용자 당 최신 행을 검색하기

나는 포스트 그레스 9.2에서 사용자 메시지 (단순화 된 형태)에 대해 다음 로그 테이블이 있습니다

CREATE TABLE log (
    log_date DATE,
    user_id  INTEGER,
    payload  INTEGER
);

그것은 사용자 당 하루에 한 기록까지 포함되어 있습니다. 3백일을 위해 하루에 약 50 만 기록이있을 것이다. (이 중요한 경우) 페이로드는 지금까지 각 사용자에 대해 증가하고있다.

나는 효율적으로 특정 날짜 이전에 각 사용자에 대한 최신 기록을 검색 할 수 있습니다. 내 질문은 :

SELECT user_id, max(log_date), max(payload) 
FROM log 
WHERE log_date <= :mydate 
GROUP BY user_id

이는 매우 느립니다. 나는 또한 시도했다 :

SELECT DISTINCT ON(user_id), log_date, payload
FROM log
WHERE log_date <= :mydate
ORDER BY user_id, log_date DESC;

의 같은 계획을 가지고 있으며, 동일하게 느립니다.

지금까지 나는 로그 (log_date)에 하나의 인덱스를 가지고 있지만, 많은 도움이되지 않습니다.

그리고 나는 모든 사용자와 사용자 테이블이 포함되어 있습니다. 또한 일부 일부 사용자에 대한 결과 (: 값 페이로드에 그들을>) 검색 할 수 있습니다.

나는이 위로, 또는 내가 원하는 것을 달성하기 위해 다른 방법으로 속도를 사용해야하는 다른 인덱스가 있습니까?

해결법

==============================
1.최적의 읽기 성능을 당신은 멀티 컬럼 인덱스가 필요합니다 :

최적의 읽기 성능을 당신은 멀티 컬럼 인덱스가 필요합니다 :
```
CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST);
```
메이크업 지수는 가능한 스캔하려면 INCLUDE 절 (나중에 포스트 그레스 11)와 커버 인덱스에 달리 필요하지 않은 열 페이로드를 추가 :
```
CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);
```
보다:

이전 버전에 대한 폴백 (fallback) :
```
CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload);
```
왜 DESC NULLS LAST?

USER_ID 당 몇 행이나 작은 테이블의 경우 DISTINCT ON는 일반적으로 가장 빠르고 간단합니다 :

인덱스 스캔 (또는 느슨한 인덱스 스캔)을 건너 USER_ID 당 행 수를 들어 (훨씬) 더 효율적입니다. 즉하지 포스트 그레스 (12)까지 구현 것 - 작업은 포스트 그레스 (13)에 대해 진행되고 그러나 효율적으로 에뮬레이션 할 수있는 방법이 있습니다.

공통 테이블 표현식은 포스트 그레스 8.4+이 필요합니다. 측면은 포스트 그레스 9.3+가 필요합니다. 다음 솔루션은 포스트 그레스 위키에 덮여 것 이상으로 이동합니다.

별도의 사용자 테이블로, 2에서 솔루션은 아래에 일반적으로 간단하고 빠릅니다. 앞서 건너 뜁니다.
```
WITH RECURSIVE cte AS (
   (                                -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;
```
이는 현재 포스트 그레스에서 아마 가장 좋은 임의의 열과를 검색 할 간단합니다. 장 2A에 더 설명. 이하.
```
WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= :mydate        -- repeat condition
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;
```
단일 열 또는 전체 행을 검색하기 편리합니다. 이 예에서는 테이블의 전체 행의 형태를 사용한다. 다른 변형이 가능하다.

이전 반복에서 발견 된 행을 주장하려면 (기본 키와 같은) 하나의 NOT NULL 열을 테스트합니다.

장 2B에서이 쿼리에 대한 자세한 설명. 이하.

관련 :

테이블 레이아웃은 거의만큼 정확히 관련 USER_ID 당 하나 개의 행이 보장으로 중요하지 않습니다. 예:
```
CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);
```
이상적으로, 테이블은 물리적으로 로그 테이블과 동기화 정렬됩니다. 보다:

또는 그것이 거의 문제 없다고 정도로 작은 (낮은 카디널리티)입니다. 다른 쿼리의 행을 정렬하는 것은 더 성능 최적화에 도움이 될 수 있습니다. 갱 리앙의 추가를 참조하십시오. 사용자 테이블의 물리적 정렬 순서가 로그에 인덱스를 일치 발생하면이 관련이있을 수 있습니다.
```
SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;
```
가입 측면은 동일한 쿼리 수준에서 항목 FROM 이전 참조 할 수 있습니다. 보다:

사용자 당 하나의 인덱스의 결과 (오닐) 룩업.

사용자 테이블에없는 사용자에 대한 행을 반환합니다. 일반적으로, 참조 무결성을 시행 외래 키 제약 조건은 배제한다.

또한, 로그 항목이 일치하지 않고 사용자를위한 어떤 행 - 원래의 질문에 부합. 결과를 사용하여 왼쪽에서 해당 사용자를 유지하려면 ... 대신 십자가의 진정한 ON 측면 가입 측면 가입 :

N 대신 LIMIT 1의 사용 제한은 사용자 당 하나 이상의 행 (전부는 아니지만)을 검색 할 수 있습니다.

효과적으로, 이들 모두는 동일한 작업을 수행 :
```
JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...
```
마지막 하나는하지만, 우선 순위가 낮습니다. 명시 쉼표 전에 바인딩 가입하세요. 그 미묘한 차이는 더 많은 것을 가진 문제는 테이블에 가입하실 수 있습니다. 보다:

좋은 선택은 단일 행에서 하나의 열을 검색 할 수 있습니다. 코드 예제 :

같은 여러 열에 가능하지만, 당신은 더 많은 현명함이 필요합니다 :
```
CREATE TEMP TABLE combo (log_date date, payload int);

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;
```
관련 :

100,000 로그 항목 및 1K 사용자와 4 개 쿼리를 입증 : DB <> 바이올린 여기 - 11 페이지 올드 sqlfiddle - 페이지 9.6
==============================
2.이것은 독립 답변이 아니라 어윈의 대답 @에 대한 의견이 아닙니다. 2A의 경우, 측면, 예를 조인 쿼리 로그에 인덱스의 지방을 이용하기 위해 사용자 테이블을 정렬하여 개선 될 수있다.

이것은 독립 답변이 아니라 어윈의 대답 @에 대한 의견이 아닙니다. 2A의 경우, 측면, 예를 조인 쿼리 로그에 인덱스의 지방을 이용하기 위해 사용자 테이블을 정렬하여 개선 될 수있다.
```
SELECT u.user_id, l.log_date, l.payload
  FROM (SELECT user_id FROM users ORDER BY user_id) u,
       LATERAL (SELECT log_date, payload
                  FROM log
                 WHERE user_id = u.user_id -- lateral reference
                   AND log_date <= :mydate
              ORDER BY log_date DESC NULLS LAST
                 LIMIT 1) l;
```
이론적 근거 USER_ID 값이 임의 인 경우 고가 조회하는 인덱스이다. 제 USER_ID를 정렬하여, 후속하는 측의 로그 지수 간단한 주사 같을 것이다 조인. 두 쿼리 계획이 닮았에도 불구하고, 실행 시간은 큰 테이블 훨씬 특히 다를 것이다.

USER_ID 필드에 대한 인덱스가있는 경우 정렬 비용은 특히 최소이다.
==============================
3.아마도 테이블에 다른 인덱스는 도움이 될 것이다. 로그 (USER_ID, log_date) :이 일을보십시오. 나는 포스트 그레스는 별개의 최적 사용을 만들 것입니다 긍정적 아닙니다.

아마도 테이블에 다른 인덱스는 도움이 될 것이다. 로그 (USER_ID, log_date) :이 일을보십시오. 나는 포스트 그레스는 별개의 최적 사용을 만들 것입니다 긍정적 아닙니다.

그래서, 그 인덱스와 스틱이 버전을 시도 할 것입니다 :
```
select *
from log l
where not exists (select 1
                  from log l2
                  where l2.user_id = l.user_id and
                        l2.log_date <= :mydate and
                        l2.log_date > l.log_date
                 );
```
이 정렬을 교체해야 / 인덱스보기 업으로 그룹화. 그것은 더 빠를 수 있습니다.

from https://stackoverflow.com/questions/25536422/optimize-group-by-query-to-retrieve-latest-row-per-user by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 사람이 읽을 수있는 설명에서 SQL 쿼리를 구성하는 엄지 손가락의 규칙이 있나요? (0)	2020.03.07
[SQL] 관리 및 MS Access에서 SQL 쿼리를 디버깅 (0)	2020.03.07
[SQL] NOT IN 절 내부에 NULL 값 (0)	2020.03.07
[SQL] 어떻게 MySQL의에서 AUTO_INCREMENT를 재설정? (0)	2020.03.07
[SQL] SQL 테이블에서 중복 값 찾기 (0)	2020.03.07

복붙노트

[SQL] BY 쿼리 최적화 그룹은 사용자 당 최신 행을 검색하기

BY 쿼리 최적화 그룹은 사용자 당 최신 행을 검색하기

해결법

1.최적의 읽기 성능을 당신은 멀티 컬럼 인덱스가 필요합니다 :

2.이것은 독립 답변이 아니라 어윈의 대답 @에 대한 의견이 아닙니다. 2A의 경우, 측면, 예를 조인 쿼리 로그에 인덱스의 지방을 이용하기 위해 사용자 테이블을 정렬하여 개선 될 수있다.

3.아마도 테이블에 다른 인덱스는 도움이 될 것이다. 로그 (USER_ID, log_date) :이 일을보십시오. 나는 포스트 그레스는 별개의 최적 사용을 만들 것입니다 긍정적 아닙니다.

'SQL' 카테고리의 다른 글

티스토리툴바