explain (analyze, buffers) 할때와 안할때 실행계획이 변경될 수 있나요?

글쓴이 : 모델광 날짜 : 2021-03-18 (목) 11:34 조회 : 5636

안녕하세요?
테이블 대사작업 속도가 나지 않아서 대사 쿼리를 수정했는데...
explain (analyze, buffers) 하고 SQL 수행하면, 13 sec 걸리는데
explain (analyze, buffers) 제거하고 SQL 수행하면, 22 sec 걸립니다.

PostgreSQL 13.1 / EDB 12.4 에서 동일 현상 발생합니다.

아래는 테스트 쿼리 입니다.
parameter 값은 모두 default 설정입니다.

DROP TABLE T1;

CREATE TABLE T1 AS

SELECT ROW_NUMBER() OVER () as C1

, oid, relname, relnamespace, relpages, reltuples, relhasindex, relisshared,relhasrules

FROM pg_class, (SELECT i FROM generate_series(1, 10000) a(i)) A;

drop table t2;
CREATE TABLE T2 AS

SELECT C1, OID

,CASE WHEN C1 != 11 THEN RELNAME ELSE 'DIFFERENT' END RELNAME

, RELNAMESPACE

, CASE WHEN C1 !=8 THEN RELPAGES ELSE 777 END RELPAGES

, RELTUPLES, relhasindex, relisshared,relhasrules

FROM T1

WHERE MOD(C1,5139999) != 0;

--아래와 같이 NOT EXISTS를 이용하면 PARALLEL PROCESSING을 이용할 수 있다.
(full outer join은 parallel processing 미지원)

explain (analyze,buffers)--explain 을 넣고 수행하면 약 13초 소요되나, explain 빼고 수행하면 약 22초
SELECT *

FROM T1 A

WHERE NOT EXISTS (SELECT 1

FROM T2 B

WHERE A.C1 = B.C1

AND A.OID = B.OID

AND A.RELNAME = B.RELNAME

AND A.RELNAMESPACE = B.RELNAMESPACE

AND A.RELPAGES = B.RELPAGES

AND A.RELTUPLES = B.RELTUPLES

AND A.RELHASINDEX = B.RELHASINDEX

AND A.RELISSHARED = B.RELISSHARED

AND A.RELHASRULES = B.RELHASRULES)

UNION ALL

SELECT *

FROM T2 B

WHERE NOT EXISTS (SELECT 1

FROM T1 A

WHERE A.C1 = B.C1

AND A.OID = B.OID

AND A.RELNAME = B.RELNAME

AND A.RELNAMESPACE = B.RELNAMESPACE

AND A.RELPAGES = B.RELPAGES

AND A.RELTUPLES = B.RELTUPLES

AND A.RELHASINDEX = B.RELHASINDEX

AND A.RELISSHARED = B.RELISSHARED

AND A.RELHASRULES = B.RELHASRULES);

댓글목록

PostgresDBA 2021-03-26 (금) 06:09

특이하네요. 실행계획 떠 보셨나요?

모델광 2021-04-01 (목) 11:25

아래는 explain(analyze, buffers) 부여 했을 때의 실행계획 입니다.
OS 단에서도 worker process가 병렬로 기동함을 확인할 수 있었습니다.
그러나 explain(analyze, buffers) 제거하고 실행하면, 병렬로 worker process가 기동되지 않더군요.

Gather (cost=205606.84..1001246.80 rows=2 width=91) (actual time=10680.032..14748.655 rows=5 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=1225 read=320342, temp read=283603 written=286288
-> Parallel Append (cost=204606.84..1000246.60 rows=2 width=91) (actual time=11838.119..14475.475 rows=2 loops=3)
Buffers: shared hit=1224 read=320342, temp read=283603 written=286288
-> Parallel Hash Anti Join (cost=204606.84..500123.29 rows=1 width=91) (actual time=5809.887..6413.544 rows=1 loops=3)
Hash Cond: ((b.c1 = a.c1) AND (b.oid = a.oid) AND (b.relname = a.relname) AND (b.relnamespace = a.relnamespace) AND (b.relpages = a.relpages) AND (b.reltuples = a.reltuples) AND (b.relhasindex = a.relhasindex) AND (b.relisshared = a.relisshared) AND (b.relhasrules = a.relhasrules))
Buffers: shared hit=724 read=160059, temp read=141797 written=142972
-> Parallel Seq Scan on t2 b (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.254..583.725 rows=1740000 loops=3)
Buffers: shared hit=224 read=80084
-> Parallel Hash (cost=102058.08..102058.08 rows=2175008 width=91) (actual time=2587.510..2587.511 rows=1740000 loops=3)
Buckets: 32768 Batches: 256 Memory Usage: 2880kB
Buffers: shared hit=333 read=79975, temp written=71016
-> Parallel Seq Scan on t1 a (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.253..1634.099 rows=5220000 loops=1)
Buffers: shared hit=333 read=79975
-> Parallel Hash Anti Join (cost=204606.84..500123.29 rows=1 width=91) (actual time=10520.821..12092.884 rows=2 loops=2)
Hash Cond: ((a_1.c1 = b_1.c1) AND (a_1.oid = b_1.oid) AND (a_1.relname = b_1.relname) AND (a_1.relnamespace = b_1.relnamespace) AND (a_1.relpages = b_1.relpages) AND (a_1.reltuples = b_1.reltuples) AND (a_1.relhasindex = b_1.relhasindex) AND (a_1.relisshared = b_1.relisshared) AND (a_1.relhasrules = b_1.relhasrules))
Buffers: shared hit=500 read=160283, temp read=141806 written=143316
-> Parallel Seq Scan on t1 a_1 (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.013..1915.568 rows=2610000 loops=2)
Buffers: shared hit=173 read=80135
-> Parallel Hash (cost=102058.08..102058.08 rows=2175008 width=91) (actual time=3430.400..3430.401 rows=2610000 loops=2)
Buckets: 32768 Batches: 256 Memory Usage: 2880kB
Buffers: shared hit=160 read=80148, temp written=71472
-> Parallel Seq Scan on t2 b_1 (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.485..1058.952 rows=2610000 loops=2)
Buffers: shared hit=160 read=80148
Planning Time: 0.778 ms
Execution Time: 14748.824 ms

아래는 explain(analyze, buffers) 부여 했을 때의 실행계획 입니다.
OS 단에서도 worker process가 병렬로 기동함을 확인할 수 있었습니다.
그러나 explain(analyze, buffers) 제거하고 실행하면, 병렬로 worker process가 기동되지 않더군요.

Gather  (cost=205606.84..1001246.80 rows=2 width=91) (actual time=10680.032..14748.655 rows=5 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=1225 read=320342, temp read=283603 written=286288
  ->  Parallel Append  (cost=204606.84..1000246.60 rows=2 width=91) (actual time=11838.119..14475.475 rows=2 loops=3)
        Buffers: shared hit=1224 read=320342, temp read=283603 written=286288
        ->  Parallel Hash Anti Join  (cost=204606.84..500123.29 rows=1 width=91) (actual time=5809.887..6413.544 rows=1 loops=3)
              Hash Cond: ((b.c1 = a.c1) AND (b.oid = a.oid) AND (b.relname = a.relname) AND (b.relnamespace = a.relnamespace) AND (b.relpages = a.relpages) AND (b.reltuples = a.reltuples) AND (b.relhasindex = a.relhasindex) AND (b.relisshared = a.relisshared) AND (b.relhasrules = a.relhasrules))
              Buffers: shared hit=724 read=160059, temp read=141797 written=142972
              ->  Parallel Seq Scan on t2 b  (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.254..583.725 rows=1740000 loops=3)
                    Buffers: shared hit=224 read=80084
              ->  Parallel Hash  (cost=102058.08..102058.08 rows=2175008 width=91) (actual time=2587.510..2587.511 rows=1740000 loops=3)
                    Buckets: 32768  Batches: 256  Memory Usage: 2880kB
                    Buffers: shared hit=333 read=79975, temp written=71016
                    ->  Parallel Seq Scan on t1 a  (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.253..1634.099 rows=5220000 loops=1)
                          Buffers: shared hit=333 read=79975
        ->  Parallel Hash Anti Join  (cost=204606.84..500123.29 rows=1 width=91) (actual time=10520.821..12092.884 rows=2 loops=2)
              Hash Cond: ((a_1.c1 = b_1.c1) AND (a_1.oid = b_1.oid) AND (a_1.relname = b_1.relname) AND (a_1.relnamespace = b_1.relnamespace) AND (a_1.relpages = b_1.relpages) AND (a_1.reltuples = b_1.reltuples) AND (a_1.relhasindex = b_1.relhasindex) AND (a_1.relisshared = b_1.relisshared) AND (a_1.relhasrules = b_1.relhasrules))
              Buffers: shared hit=500 read=160283, temp read=141806 written=143316
              ->  Parallel Seq Scan on t1 a_1  (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.013..1915.568 rows=2610000 loops=2)
                    Buffers: shared hit=173 read=80135
              ->  Parallel Hash  (cost=102058.08..102058.08 rows=2175008 width=91) (actual time=3430.400..3430.401 rows=2610000 loops=2)
                    Buckets: 32768  Batches: 256  Memory Usage: 2880kB
                    Buffers: shared hit=160 read=80148, temp written=71472
                    ->  Parallel Seq Scan on t2 b_1  (cost=0.00..102058.08 rows=2175008 width=91) (actual time=0.485..1058.952 rows=2610000 loops=2)
                          Buffers: shared hit=160 read=80148
Planning Time: 0.778 ms
Execution Time: 14748.824 ms


		검색어 PostgreSQL 은 특별합니다. 단순한 데이터베이스가 아니라, 하나의 어플리케이션 플랫폼이기 때문입니다.

explain (analyze, buffers) 할때와 안할때 실행계획이 변경될 수 있나요? > Q & A 게시판

explain (analyze, buffers) 할때와 안할때 실행계획이 변경될 수 있나요?

댓글목록