복잡한 쿼리에서 행을 계산하는 방식에 어떤 문제가 있습니까?

debugcn 에 게시 Dev

Sanko

몇 개의 테이블이있는 데이터베이스가 있고 각 테이블에는 수백만 개의 행이 있습니다 (테이블에는 인덱스가 있음). 테이블의 행을 계산해야하지만 외래 키 필드가 다른 테이블의 하위 집합을 가리키는 행만 계산해야합니다.
다음은 쿼리입니다.

WITH filtered_title 
     AS (SELECT top.id 
         FROM   title top 
         WHERE  ( top.production_year >= 1982 
                  AND top.production_year <= 1984 
                  AND top.kind_id IN( 1, 2 ) 
                   OR EXISTS(SELECT 1 
                             FROM   title sub 
                             WHERE  sub.episode_of_id = top.id 
                                    AND sub.production_year >= 1982 
                                    AND sub.production_year <= 1984 
                                    AND sub.kind_id IN( 1, 2 )) )) 
SELECT Count(*) 
FROM   cast_info 
WHERE  EXISTS(SELECT 1 
              FROM   filtered_title 
              WHERE  cast_info.movie_id = filtered_title.id) 
       AND cast_info.role_id IN( 3, 8 )

동일한 하위 쿼리를 사용하는 다른 테이블에 대해 더 많은 COUNT 쿼리가 있기 때문에 CTE를 사용합니다. 그러나 나는 CTE를 제거하려고 시도했으며 결과는 동일했습니다. 처음 쿼리를 실행했을 때 실행 ... 실행 ... 10 분 이상 실행됩니다 . 두 번째로 쿼리를 실행하면 4 초로 줄어 듭니다.

결과 EXPLAIN ANALYZE:

Aggregate  (cost=46194894.49..46194894.50 rows=1 width=0) (actual time=127728.452..127728.452 rows=1 loops=1)
  CTE filtered_title
    ->  Seq Scan on title top  (cost=0.00..46123542.41 rows=1430406 width=4) (actual time=732.509..1596.345 rows=16250 loops=1)
          Filter: (((production_year >= 1982) AND (production_year <= 1984) AND (kind_id = ANY ('{1,2}'::integer[]))) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
          Rows Removed by Filter: 2832906
          SubPlan 1
            ->  Index Scan using title_idx_epof on title sub  (cost=0.43..16.16 rows=1 width=0) (never executed)
                  Index Cond: (episode_of_id = top.id)
                  Filter: ((production_year >= 1982) AND (production_year <= 1984) AND (kind_id = ANY ('{1,2}'::integer[])))
          SubPlan 2
            ->  Seq Scan on title sub_1  (cost=0.00..90471.23 rows=11657 width=4) (actual time=0.071..730.311 rows=16250 loops=1)
                  Filter: ((production_year >= 1982) AND (production_year <= 1984) AND (kind_id = ANY ('{1,2}'::integer[])))
                  Rows Removed by Filter: 2832906
  ->  Nested Loop  (cost=32184.70..63158.16 rows=3277568 width=0) (actual time=1620.382..127719.030 rows=29679 loops=1)
        ->  HashAggregate  (cost=32184.13..32186.13 rows=200 width=4) (actual time=1620.058..1631.697 rows=16250 loops=1)
              ->  CTE Scan on filtered_title  (cost=0.00..28608.12 rows=1430406 width=4) (actual time=732.513..1607.093 rows=16250 loops=1)
        ->  Index Scan using cast_info_idx_mid on cast_info  (cost=0.56..154.80 rows=6 width=4) (actual time=5.977..7.758 rows=2 loops=16250)
              Index Cond: (movie_id = filtered_title.id)
              Filter: (role_id = ANY ('{3,8}'::integer[]))
              Rows Removed by Filter: 15
Total runtime: 127729.100 ms

이제 내 질문에. 내가 뭘 잘못하고 있으며 어떻게 고칠 수 있습니까?

동일한 쿼리의 몇 가지 변형 (배타적 조인, 조인 / 존재)을 시도했습니다. 한편으로 이것은 작업을 수행하는 데 가장 적은 시간 (10 배 더 빠름)이 필요한 것처럼 보이지만 여전히 평균 60 초입니다. 반면에 두 번째 실행에서 4-6 초가 필요한 첫 번째 쿼리와 달리 항상 60 초가 필요합니다.

WITH filtered_title 
     AS (SELECT top.id 
         FROM   title top 
         WHERE  top.production_year >= 1982 
                AND top.production_year <= 1984 
                AND top.kind_id IN( 1, 2 ) 
                 OR EXISTS(SELECT 1 
                           FROM   title sub 
                           WHERE  sub.episode_of_id = top.id 
                                  AND sub.production_year >= 1982 
                                  AND sub.production_year <= 1984 
                                  AND sub.kind_id IN( 1, 2 ))) 
SELECT Count(*) 
FROM   cast_info 
       join filtered_title 
         ON cast_info.movie_id = filtered_title.id 
WHERE  cast_info.role_id IN( 3, 8 )

Erwin Brandstetter

^{면책 조항 : 결정적인 답변을하기에는 너무 많은 요소가 있습니다. 정보 with a few tables, each has a few millions rows (tables do have indexes) 는 그것을 자르지 않습니다 . 카디널리티, 테이블 정의, 데이터 유형, 사용 패턴 및 (아마도 가장 중요한) 인덱스에 따라 다릅니다. 물론 db 서버의 적절한 기본 구성. 이 모든 것은 SO에 대한 단일 질문의 범위를 벗어납니다. postgresql-performance 태그 의 링크로 시작합니다 . 또는 전문가를 고용하십시오.}

내가 해결 하겠어 가장 눈에 띄는 세부 쿼리 계획에서 (나를 위해) :

에 순차 스캔 `title`?

-> 제목 sub_1에 대한 시퀀스 스캔 (비용 = 0.00..90471.23 행 = 11657 너비 = 4) (실제 시간 = 0.071..730.311 행 = 16250 루프 = 1)
필터 : ((생산 _ 연도 > = 1982) AND (생산 _ 연도 <= 1984) AND (kind_id = ANY ( '{1,2}':: integer [])))
필터에 의해 제거 된 행 : 2832906

대담하게 강조합니다. 3 백만 행을 순차적으로 스캔하여 16250 개만 검색하는 것은 그다지 효율적이지 않습니다. 순차 스캔은 첫 번째 실행이 훨씬 더 오래 걸리는 이유이기도합니다. 후속 호출은 캐시에서 데이터를 읽을 수 있습니다. 테이블이 크기 때문에 캐시가 너무 많지 않으면 데이터가 캐시에 오래 머물지 않을 것입니다.

인덱스 스캔은 일반적으로 큰 테이블에서 행의 0.5 %를 수집하는 데 훨씬 더 빠릅니다. 가능한 원인들:

내 돈은 색인에 있습니다. Postgres 버전을 제공하지 않았으므로 현재 9.3을 가정합니다. 이 쿼리에 대한 완벽한 색인은 다음과 같습니다.

CREATE INDEX title_foo_idx ON title (kind_id, production_year, id, episode_of_id)

데이터 유형이 중요합니다. 인덱스 의 열 순서가 중요합니다.
kind_id첫째, 경험의 법칙은 다음과 같습니다 .
마지막 두 열 ( id, episode_of_id)은 잠재적 인 인덱스 전용 스캔에만 유용합니다. 해당되지 않는 경우 삭제하십시오. 자세한 내용은 여기 :
PostgreSQL 복합 기본 키

쿼리를 작성하는 방식 에 따라 큰 테이블에서 두 번의 순차 스캔으로 끝납니다 . 그래서 여기에 대한 교육적인 추측이 있습니다 ...

더 나은 쿼리

WITH t_base AS (
   SELECT id, episode_of_id
   FROM   title
   WHERE  kind_id BETWEEN 1 AND 2
   AND    production_year BETWEEN 1982 AND 1984 
   )
, t_all AS (
   SELECT id FROM t_base

   UNION  -- not UNION ALL (!)
   SELECT id
   FROM  (SELECT DISTINCT episode_of_id AS id FROM t_base) x
   JOIN   title t USING (id)
   )
SELECT count(*) AS ct
FROM   cast_info c
JOIN   t_all t ON t.id = c.movie_id 
WHERE  c.role_id IN (3, 8);

이것은 당신에게 제공해야 하나 새로운 인덱스 스캔 title_foo_idx플러스의 PK 인덱스에 다른 인덱스 스캔을 title. 나머지는 상대적으로 저렴해야합니다. 운이 좋으면 이전보다 훨씬 빠릅니다.

kind_id BETWEEN 1 AND 2.. 값의 연속 범위가있는 한, Postgres가 인덱스에서 연속 범위를 가져올 수 있기 때문에 개별 값을 나열하는 것보다 빠릅니다. 두 가지 값에만 그다지 중요하지 않습니다.
의 두 번째 구간에 대해이 대안을 테스트합니다 t_all. 어느 것이 더 빠른지 확실하지 않습니다.
```
   SELECT id
   FROM   title t 
   WHERE  EXISTS (SELECT 1 FROM t_base WHERE t_base.episode_of_id = t.id)
```

CTE 대신 임시 테이블

당신은 쓰기:

동일한 하위 쿼리를 사용하는 다른 테이블에 대해 더 많은 COUNT 쿼리 가 있기 때문에 CTE 를 사용합니다.

CTE는 최적화 장벽 역할을하며 결과 내부 작업 테이블은 색인화되지 않습니다 . 결과 (사소한 행 수 이상)를 여러 번 재사용 할 때 대신 인덱싱 된 임시 테이블을 사용하는 것이 좋습니다. 간단한 int 열에 대한 인덱스 생성이 빠릅니다.

CREATE TEMP TABLE t_tmp AS
WITH t_base AS (
   SELECT id, episode_of_id
   FROM   title
   WHERE  kind_id BETWEEN 1 AND 2
   AND    production_year BETWEEN 1982 AND 1984 
   )
SELECT id FROM t_base
UNION
SELECT id FROM title t 
WHERE  EXISTS (SELECT 1 FROM t_base WHERE t_base.episode_of_id = t.id);

ANALYZE t_tmp;                       -- !
CREATE UNIQUE INDEX ON t_tmp (id);   -- ! (unique is optional)

SELECT count(*) AS ct
FROM   cast_info c
JOIN   t_tmp t ON t.id = c.movie_id 
WHERE  c.role_id IN (3, 8);

-- More queries using t_tmp

임시 테이블 정보 :
Postgres에서 레코드가 변경되었는지 확인하는 방법

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-05-31

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사