최적화 GroupWise에 최대 쿼리

select * 
from records 
where id in ( select max(id) from records group by option_id )

이 쿼리는 심지어 수백만 개의 행에 잘 작동합니다. 그러나 당신은 문을 설명의 결과에서 볼 수있다 :

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=30218.84..31781.62 rows=620158 width=44) (actual time=1439.251..1443.458 rows=1057 loops=1)
->  HashAggregate  (cost=30218.41..30220.41 rows=200 width=4) (actual time=1439.203..1439.503 rows=1057 loops=1)
     ->  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=1438.523..1438.807 rows=1057 loops=1)
           ->  Seq Scan on records records_1  (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.103..527.914 rows=1240315 loops=1)
->  Index Scan using records_pkey on records  (cost=0.43..7.80 rows=1 width=44) (actual time=0.002..0.003 rows=1 loops=1057)
     Index Cond: (id = (max(records_1.id)))
Total runtime: 1443.752 ms

(비용 = 0.00..23995.15 행 = 1240315 폭 = 8) <- 여기 그것이 모든 행을 스캔하고 있다고하고 분명 비효율적이다.

또한 쿼리를 재정렬 시도 :

select r.* from records r
inner join (select max(id) id from records group by option_id) r2 on r2.id= r.id;

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------

Nested Loop  (cost=30197.15..37741.04 rows=964 width=44) (actual time=835.519..840.452 rows=1057 loops=1)
->  HashAggregate  (cost=30196.72..30206.36 rows=964 width=8) (actual time=835.471..835.836 rows=1057 loops=1)
     ->  Seq Scan on records  (cost=0.00..23995.15 rows=1240315 width=8) (actual time=0.336..348.495 rows=1240315 loops=1)
->  Index Scan using records_pkey on records r  (cost=0.43..7.80 rows=1 width=44) (actual time=0.003..0.003 rows=1 loops=1057)
     Index Cond: (id = (max(records.id)))
Total runtime: 840.809 ms

비용 (= 0.00..23995.15 행 = 1,240,315 폭 = 8) <- 아직 모든 행을 주사.

내가 함께하고 (option_id), (option_id, ID), (option_id, ID 내림차순) 인덱스없이 시도, 그들 중 누구도 쿼리 계획에 어떤 영향을 미치지 않았다.

모든 행을 스캔하지 않고 포스트 그레스에서 GroupWise에 최대 쿼리를 실행하는 방법이 있나요?

내가 찾고 있어요 프로그래밍 방식, 그들은 기록 테이블에 삽입되는 각 option_id의 최대 ID를 저장하는 인덱스입니다. 내가 option_ids의 최대 값에 대한 쿼리 할 때 다른 option_ids 있기 때문에이 방법은, 난 단지 여러 번 스캔 인덱스 레코드에 필요합니다.

나는 (나에 검색 키워드를주는 @Clodoaldo 네토 덕분에) 높은 순위 사용자로부터 모든 것을 SO 답변에 선택 별개을 보았다. 그것이 작동하지 않는 이유는 다음과 같습니다

create index index_name on records(option_id, id desc)

select distinct on (option_id) *
from records
order by option_id, id desc
                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique  (cost=0.43..76053.10 rows=964 width=44) (actual time=0.049..1668.545 rows=1056 loops=1)
  ->  Index Scan using records_option_id_id_idx on records  (cost=0.43..73337.25 rows=1086342 width=44) (actual time=0.046..1368.300 rows=1086342 loops=1)
Total runtime: 1668.817 ms

즉, 인덱스를 사용하고, 좋아요. 그러나 정말 많은 이해가되지 않습니다 모든 ID를 스캔하는 인덱스를 사용. 내 실행에 따르면, 느린 간단한 순차 검색보다 더 사실이다.

재미있는 충분, MySQL은 5.5 단순히 기록에 인덱스를 사용하여 쿼리를 최적화 할 수 있습니다 (option_id, ID)

mysql> select count(1) from records;

+----------+
| count(1) |
+----------+
|  1086342 |
+----------+

1 row in set (0.00 sec)

mysql> explain extended select * from records
       inner join ( select max(id) max_id from records group by option_id ) mr
                                                      on mr.max_id= records.id;

+------+----------+--------------------------+
| rows | filtered | Extra                    |
+------+----------+--------------------------+
| 1056 |   100.00 |                          |
|    1 |   100.00 |                          |
|  201 |   100.00 | Using index for group-by |
+------+----------+--------------------------+

3 rows in set, 1 warning (0.02 sec)

해결법

==============================
1.기록에 많은 행 옵션에 상대적으로 적은 행을 가정.

기록에 많은 행 옵션에 상대적으로 적은 행을 가정.

일반적으로, 당신은 이상적으로 외래 키 제약 조건, records.option_id에서 참조하는 룩업 테이블 옵션을 가질 것이다. 그렇지 않으면, 나는 참조 무결성을 적용 할 하나를 생성하는 것이 좋습니다 :
```
CREATE TABLE options (
  option_id int  PRIMARY KEY
, option    text UNIQUE NOT NULL
);

INSERT INTO options
SELECT DISTINCT option_id, 'option' || option_id -- dummy option names
FROM   records;
```
그런 다음 우리는 느슨한 인덱스가 더 이상 스캔 에뮬레이트 할 필요가 없으며이 매우 간단하게 빠르게. 상관 하위 쿼리는 (option_id, ID)에 일반 인덱스를 사용할 수 있습니다.
```
SELECT option_id
      ,(SELECT max(id)
        FROM   records
        WHERE  option_id = o.option_id
       ) AS max_id
FROM   options o
ORDER  BY 1;
```
이 테이블 레코드에없는 일치와 옵션이 포함되어 있습니다. 당신은 max_id에 대한 NULL을 얻을하고 필요한 경우 쉽게 외부 SELECT 이러한 행을 제거 할 수 있습니다.

또는 (같은 결과)
```
SELECT option_id
     , (SELECT id
        FROM   records
        WHERE  option_id = o.option_id
        ORDER  BY id DESC NULLS LAST
       ) AS max_id
FROM   options o
ORDER  BY 1;
```
조금 더 빠를 수 있습니다. NULL 값을 무시 집계 기능 맥스 ()와 동일 - 부질 정렬 순서 DESC NULLS LAST를 사용한다. 첫째 NULL을하는 것과 DESC 정렬 :

그래서,이 완벽한 지수 :
```
CREATE INDEX on records (option_id, id DESC NULLS LAST);
```
열이 NULL NOT 정의하는 동안별로 중요하지 않습니다.

여전히 모든 행을 가져 오기 위해 단지 가장 빠른 방법 작은 테이블 옵션에 순차 검색,있을 수 있습니다. 순서에은 (전용) 사전 정렬 된 행을 가져 오기 위해 검색 인덱스에 가져올 수 있습니다. 인덱스 만 스캔 가능한 경우, 또는 - 큰 테이블 레코드은 (비트 맵) 인덱스 스캔을 통해 액세스 할 수 있습니다.

SQL 바이올린은 간단한 경우 두 개의 인덱스 만 스캔을 보여주는.

또는 사용 측면은 9.3+ 포스트 그레스에서 비슷한 효과를 조인
==============================
2.각 option_id에 대한 인덱스 만 인덱스 최대 (ID)를 원하는 말할 것도. 이 기능은 현재 PostgreSQL을 지원하지 않습니다. 이러한 기능은 향후에 추가되면, 아마 집계 쿼리에 구체화 된 뷰를 만들고, 다음 뷰를 구체화 색인의 메커니즘을 통해 수행 할 수있다. 그래도, 적어도 몇 년 동안 기대하지 않을 것이다.

각 option_id에 대한 인덱스 만 인덱스 최대 (ID)를 원하는 말할 것도. 이 기능은 현재 PostgreSQL을 지원하지 않습니다. 이러한 기능은 향후에 추가되면, 아마 집계 쿼리에 구체화 된 뷰를 만들고, 다음 뷰를 구체화 색인의 메커니즘을 통해 수행 할 수있다. 그래도, 적어도 몇 년 동안 기대하지 않을 것이다.

당신이 지금 할 수있는 일,하지만, 그것은 option_id의 각각의 고유 한 값으로 색인을 통해 건너 재귀 쿼리 메이크업을 사용합니다. 기술에 대한 전반적인 설명을 PostgreSQL의 위키 페이지를 참조하십시오.

당신이 당신의 사건에 대한이 사용할 수있는 방법은 그 부속 최대 (ID)의 각각에 대해 다음 option_id의 고유 한 값을 반환하는 재귀 쿼리를 작성하고 :
```
with recursive dist as (
  select min(option_id) as option_id from records
union all
  select (select min(option_id) from records where option_id > dist.option_id) 
     from dist where dist.option_id is not null
) 

select option_id, 
  (select max(id) from records where records.option_id=dist.option_id)
from dist where option_id is not null;
```
그것은 추한,하지만 당신은 볼 뒤에 숨길 수 있습니다.

내 손에서 별개의 다양한에이 43ms에서 실행보다는 513ms.

당신은 재귀 쿼리에 최대 (ID)를 통합 할 수있는 방법을 찾을 수 있다면 그것은 아마 배 빠른에 대해 만들 수있는,하지만 난 그렇게 할 수있는 방법을 찾을 수 없습니다. 문제는 이러한 쿼리가 다소 제한적인 문법을 가지고, 당신은 UNION ALL과 함께 "에 의해 순서", "제한"또는 사용할 수있다.

이 쿼리의 접촉 페이지 널리 인덱스 곳곳에, 그리고 그 페이지가 캐시에 맞지 않는 경우에, 당신은 비효율적 인 IO를 많이하고있을 것입니다. 이러한 유형의 쿼리가 인기 경우, 다음 1057 잎 인덱스 페이지는 약간의 문제가 캐시에 머물고있을 것이다.

이것은 내 테스트 케이스를 설정하는 방법입니다 :
```
create table records  as select floor(random()*1057)::integer as option_id, floor(random()*50000000)::integer as id from generate_series(1,1240315);
create index on records (option_id ,id);
explain analyze;
```
==============================
3.PostgreSQL은 MySQL은 같은 쿼리에 사용할 수있는 느슨한 스캔을 지원하지 않습니다. 그것은을 위해 사용 지수는 MySQL의 계획을보고있는 그룹에 의해.

PostgreSQL은 MySQL은 같은 쿼리에 사용할 수있는 느슨한 스캔을 지원하지 않습니다. 그것은을 위해 사용 지수는 MySQL의 계획을보고있는 그룹에 의해.

기본적으로, 그 후이 서브 세트의 다음 또는 이전 값을 검색 복합 키의 일부와 일치하는 범위에서 첫 번째 또는 마지막 항목을 리턴있다.

귀하의 경우에는 먼저 (option_id, ID) (정의에 의해 가장 큰 option_id의 MAX (ID)를 개최 일어나는)와 마지막 값, 다음 검색에 전체 인덱스의 마지막 값을 반환 가장 큰 option_id 그래서 옆에 의 위에.

PostgreSQL을의 최적화는 이러한 계획을 구축 할 수 없습니다, 그러나, PostgreSQL는 당신이 SQL에서 에뮬레이트 할 수 있습니다. 당신이 기록하지만 몇 가지 뚜렷한 option_id 많이있는 경우, 그것의 가치는 일.

이렇게하려면 먼저 인덱스를 만들 :
```
CREATE INDEX ix_records_option_id ON records (option_id, id);
```
다음이 쿼리를 실행합니다 :
```
WITH RECURSIVE q (option_id) AS
        (
        SELECT  MIN(option_id)
        FROM    records
        UNION ALL
        SELECT  (
                SELECT  MIN(option_id)
                FROM    records
                WHERE   option_id > q.option_id
                )
        FROM    q
        WHERE   option_id IS NOT NULL
        )
SELECT  option_id,
        (
        SELECT  MAX(id)
        FROM    records r
        WHERE   r.option_id = q.option_id
        )
FROM    q
WHERE   option_id IS NOT NULL
```
http://sqlfiddle.com/#!15/4d77d/4 : sqlfiddle.com에보기
==============================
4.
```
select distinct on (option_id) *
from records
order by option_id, id desc
```
카디널리티가 유리한 경우 인덱스에만 사용됩니다. 즉, 복합 인덱스를 시도 할 수 있습니다 말했다
```
create index index_name on records(option_id, id desc)
```

from https://stackoverflow.com/questions/24244026/optimize-groupwise-maximum-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 결과는 SQL 전치하는 하나의 열은 복수 열로 이동되도록 (0)	2020.05.16
[SQL] 두 테이블 구조의 차이점 (0)	2020.05.16
[SQL] 열 이름에 예약어를 사용하여 (0)	2020.05.16
[SQL] ColdFusion에서 - 변수 필드 이름은 데이터베이스 쿼리 결과를 통해 반복 할 때 (0)	2020.05.16
[SQL] PostgreSQL는 WHERE 절에 열 별칭을 허용하지 않습니다 (0)	2020.05.16

복붙노트

[SQL] 최적화 GroupWise에 최대 쿼리

최적화 GroupWise에 최대 쿼리

해결법

1.기록에 많은 행 옵션에 상대적으로 적은 행을 가정.

3.PostgreSQL은 MySQL은 같은 쿼리에 사용할 수있는 느슨한 스캔을 지원하지 않습니다. 그것은을 위해 사용 지수는 MySQL의 계획을보고있는 그룹에 의해.

4.

'SQL' 카테고리의 다른 글

티스토리툴바