잘못된 SQL 쿼리에 대한 레시피

몇달 전 우리는 발표했다 explain.tensor.ru - 공공의 쿼리 계획 구문 분석 및 시각화 서비스 PostgreSQL에.

그 이후로 이미 6000회 이상 사용했지만 간과할 수 있는 편리한 기능 중 하나는 다음과 같습니다. 구조적 단서, 다음과 같이 표시됩니다.

그들에게 귀를 기울이면 귀하의 요청이 "매끄러워질 것"입니다. 🙂

그러나 진지하게 요청을 느리고 리소스 측면에서 "게걸스럽게" 만드는 많은 상황, 일반적이며 계획의 구조와 데이터로 인식할 수 있습니다..

이 경우 개별 개발자는 자신의 경험에만 의존하여 스스로 최적화 옵션을 찾을 필요가 없습니다. 여기에서 무슨 일이 일어나고 있는지, 이유가 무엇인지, 해결책을 내놓는 방법. 우리가 한 일입니다.

이러한 경우가 어떻게 정의되고 어떤 권장 사항으로 이어지는지 자세히 살펴보겠습니다.

주제에 더 잘 몰입하려면 먼저 다음에서 해당 블록을 들을 수 있습니다. PGConf.Russia 2020에서의 보고서, 그런 다음 각 예에 대한 자세한 분석으로 이동합니다.

#1: 인덱스 "undersorting"

언제

클라이언트 "LLC Kolokolchik"에 대한 마지막 송장을 표시합니다.

식별 방법

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

추천

사용된 인덱스 정렬 필드로 확장.

예 :

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 -- отбор по конкретной связи
ORDER BY
  pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;

[explain.tensor.ru 참조]

인덱스에서 100개 이상의 레코드를 뺀 다음 모두 정렬한 다음 하나만 남은 레코드를 즉시 알 수 있습니다.

수정:

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки

[explain.tensor.ru 참조]

그러한 원시적 샘플에서도 - 8.5배 더 빠르고 33배 더 적은 읽기. 각 값에 대한 "사실"이 많을수록 효과가 더 명확해집니다. fk.

이러한 인덱스는 다른 쿼리에 대해 이전 인덱스보다 나쁘지 않은 "접두사" 인덱스로 작동합니다. fk, 여기서 정렬 기준 pk 아니었다 그리고 아니다 (당신은 이것에 대해 더 읽을 수 있습니다 비효율적인 인덱스를 찾는 방법에 대한 내 기사에서). 특히, 정상적인 명시적 외래 키 지원 이 필드로.

#2: 인덱스 교차(BitmapAnd)

언제

"NJSC Lyutik"을 대신하여 체결된 클라이언트 "LLC Kolokolchik"에 대한 모든 계약을 표시합니다.

식별 방법

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

추천

생성 복합 지수 두 소스의 필드를 기준으로 하거나 두 번째에서 기존 필드 중 하나를 확장합니다.

예 :

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре

[explain.tensor.ru 참조]

수정:

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

[explain.tensor.ru 참조]

여기서 비트맵 힙 스캔은 그 자체로 매우 효과적이기 때문에 이득이 더 작습니다. 하지만 어쨌든 7배 더 빠르고 2.5배 더 적은 읽기.

#3: 인덱스 결합(BitmapOr)

언제

가장 오래된 "소유" 또는 할당되지 않은 처리 요청 20개를 우선 순위로 표시합니다.

식별 방법

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

추천

사용 유니온 [전체] 각 조건 OR 블록에 대한 하위 쿼리를 결합합니다.

예 :

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- свои
  fk_own IS NULL -- ... или "ничьи"
ORDER BY
  pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;

[explain.tensor.ru 참조]

수정:

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 -- сначала "свои" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL -- потом "ничьи" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо

[explain.tensor.ru 참조]

우리는 20개의 필요한 레코드가 모두 첫 번째 블록에서 즉시 얻어졌다는 사실을 이용했습니다. 따라서 더 "비싼" Bitmap Heap Scan이 포함된 두 번째 블록은 실행조차 되지 않았습니다. 22배 더 빠르고 44배 더 적은 읽기!

이 최적화 방법에 대한 자세한 이야기 구체적인 사례에 대해 기사에서 읽을 수 있습니다 PostgreSQL 안티패턴: 유해한 JOIN 및 OR и PostgreSQL 안티패턴: 이름별 검색의 반복적 개선 또는 "앞뒤로 최적화"에 대한 이야기.

일반화 버전 여러 키로 정렬된 선택 (const / NULL 쌍뿐만 아니라) 기사에서 논의됩니다. SQL HowTo: 쿼리에서 직접 while 루프 작성 또는 "기본 XNUMX방향".

#4: 우리는 너무 많이 읽습니다.

언제

일반적으로 기존 요청에 "다른 필터를 추가"하려는 경우에 발생합니다.

"그리고 당신은 같은 것을 가지고 있지 않지만 진주 버튼의 어머니와 함께?» 영화 '다이아몬드 핸드'

예를 들어 위의 작업을 수정하면 목적에 관계없이 처리를 위해 처음 20개의 가장 오래된 "중요" 요청이 표시됩니다.

식별 방법

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF -- отфильтровано >80% прочитанного
   && loops × RRbF > 100 -- и при этом больше 100 записей суммарно

추천

[더] 전문화 만들기 WHERE 절이 있는 인덱스 또는 색인에 추가 필드를 포함합니다.

작업에 대한 필터링 조건이 "정적"인 경우 확장을 포함하지 않음 미래의 값 목록 - WHERE 인덱스를 사용하는 것이 좋습니다. 다양한 부울/열거형 상태가 이 범주에 적합합니다.

여과조건이라면 다른 값을 가질 수 있습니다, 위의 BitmapAnd 상황에서와 같이 이러한 필드로 인덱스를 확장하는 것이 좋습니다.

예 :

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K "фактов"
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100 разных внешних ключей
  END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

[explain.tensor.ru 참조]

수정:

CREATE INDEX ON tbl(pk)
  WHERE critical; -- добавили "статичное" условие фильтрации

[explain.tensor.ru 참조]

보시다시피 계획의 필터링이 완전히 사라지고 요청이 5배 더 빠름.

#5: 스파스 테이블

언제

자신의 작업 처리 대기열을 만들기 위한 다양한 시도, 테이블에서 많은 수의 레코드 업데이트/삭제가 많은 수의 "죽은" 레코드 상황으로 이어질 때.

식별 방법

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

추천

수동으로 정기적으로 수행 진공[전체] 또는 적절하게 빈번한 처리를 달성 자동 진공화 다음을 포함하여 매개변수를 미세 조정하여 특정 테이블에 대해.

대부분의 경우 이러한 문제는 비즈니스 로직에서 호출될 때 잘못된 쿼리 레이아웃으로 인해 발생합니다. PostgreSQL 안티패턴: "죽은" 무리와 싸우기.

그러나 VACUUM FULL도 항상 도움이 될 수는 없다는 점을 이해해야 합니다. 이러한 경우 기사의 알고리즘을 숙지해야 합니다. DBA: VACUUM이 통과하면 테이블을 수동으로 정리합니다..

#6: 색인의 "중간"에서 읽기

언제

그들은 조금 읽은 것 같고 모든 것이 색인화되었으며 추가로 필터링하지 않았지만 여전히 우리가 원하는 것보다 훨씬 더 많은 페이지를 읽었습니다.

식별 방법

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      -- прочитано больше 1KB на каждую запись
   && shared hit + shared read > 64

추천

사용된 인덱스의 구조와 쿼리에 지정된 키 필드를 자세히 살펴보십시오. 색인 부분이 설정되지 않음. 비슷한 색인을 만들어야 할 가능성이 높지만 접두사 필드가 없거나 그들의 가치를 반복하는 법을 배우십시오.

예 :

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K "фактов"
, (random() *  100)::integer fk_org  -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей

CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;

[explain.tensor.ru 참조]

색인 측면에서도 모든 것이 괜찮은 것 같지만 왠지 의심 스럽습니다. 20 개의 레코드를 읽을 때마다 4 페이지의 데이터를 빼야했고 레코드 당 32KB가 굵게 표시되지 않습니까? 예 및 색인 이름 tbl_fk_org_fk_cli_idx 생각으로 이어집니다.

수정:

CREATE INDEX ON tbl(fk_cli);

[explain.tensor.ru 참조]

갑자기 - 10배 더 빠르고 4배 더 적은 읽기!

인덱스의 비효율적인 사용에 대한 더 많은 예는 기사를 참조하십시오. DBA: 쓸모없는 인덱스 찾기.

#7: CTE × CTE

언제

요청 중 "뚱뚱한" CTE 점수 다른 테이블에서, 그리고 그들 사이에서하기로 결정 JOIN.

사례는 v12 이하 버전 또는 다음과 같은 요청과 관련이 있습니다. WITH MATERIALIZED.

식별 방법

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      -- слишком большое декартово произведение CTE

추천

요청을 신중하게 분석 여기에 CTE가 전혀 필요합니까?? 그렇다면 hstore/json에 "사전" 적용 에 설명된 모델에 따라 PostgreSQL 안티패턴: 사전 히트 헤비 조인.

#8: 디스크로 스왑(임시 쓰기)

언제

많은 레코드의 일회성 처리(정렬 또는 고유화)는 이를 위해 할당된 메모리에 맞지 않습니다.

식별 방법

-> *
   && temp written > 0

추천

연산에 사용되는 메모리의 양이 파라미터의 설정값을 크게 초과하지 않는 경우 work_mem, 수정해야 합니다. 모든 사람을 위해 구성에서 즉시 할 수 있거나 다음을 통해 할 수 있습니다. SET [LOCAL] 특정 요청/거래에 대해.

예 :

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

[explain.tensor.ru 참조]

수정:

SET work_mem = '128MB'; -- перед выполнением запроса

[explain.tensor.ru 참조]

분명한 이유로 디스크가 아닌 메모리만 사용하면 쿼리가 훨씬 빨라집니다. 동시에 부하의 일부도 HDD에서 제거됩니다.

그러나 많은 메모리를 할당하는 것이 항상 작동하지 않는다는 것을 이해해야 합니다. 단순히 모든 사람에게 충분하지 않을 것입니다.

#9: 관련 없는 통계

언제

한 번에 기지에 많은 양을 쏟아 부었지만 몰아 낼 시간이 없었습니다 ANALYZE.

식별 방법

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

추천

같은 지출 ANALYZE.

이 상황은 에 자세히 설명되어 있습니다. PostgreSQL 안티패턴: 통계는 모든 것의 핵심입니다..

#10: "문제가 발생했습니다."

언제

경쟁 요청을 기다리는 잠금이 있거나 CPU/하이퍼바이저 하드웨어 리소스가 충분하지 않습니다.

식별 방법

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms -- читали мало, но слишком долго

추천

외부 사용 감시 체계 차단 또는 비정상적인 리소스 소비를 위한 서버. 수백 대의 서버에 대해 이 프로세스를 구성하는 버전에 대해 이미 이야기했습니다. 여기에 и 여기에.

출처 : habr.com