[Q&A] PostgreSQL 9.6 성능 이야기

PostgreSQL 9.6 성능 이야기 관련 Q&A 페이지입니다.  아래 페이지에 질문을 등록해주시면 최대한 빠르게 답변드리겠습니다. 감사합니다.

Q&A #3

답변 (김시연): 2017-04-12

네. 맞습니다. JPPD 실패 시의 문제점을 부각(?)시키기 위해서 Merge Join을 disable 했는데요. 그 부분이 빠져있습니다. 이 부분은 책에도 추가를 해야겠습니다. 감사합니다 🙂

참고로, Merge Join으로 수행될 경우에는 입력 값에 따라서 성능 편차가 있습니다. 예제의 경우는 빠르게 나오지만 “a.c2=999999″와 같이 뒷 부분의 값을 입력하면 느립니다.

질문: eqon19 : 2017-04-12

전자책 228페이지 아래 JPPD실패하는 경우의 예제 테스트해보니까

QUERY PLAN
————————————————————————–
Merge Join (actual rows=1 loops=1)
 Merge Cond: (t3.c1 = a.c1)
 -> GroupAggregate (actual rows=12 loops=1)
 Group Key: t3.c1
 -> Index Scan using t3_c1_indx on t3 (actual rows=25 loops=1)
 -> Materialize (actual rows=1 loops=1)
 -> Sort (actual rows=1 loops=1)
 Sort Key: a.c1
 Sort Method: quicksort Memory: 25kB
 -> Index Scan using t2_uk on t2 a (actual rows=1 loops=1)
 Index Cond: (c2 = 10)
 Planning time: 0.981 ms
 Execution time: 0.327 ms

위처럼 나오는데

set enable_mergejoin=off;
이렇게 먼저 해줘야 책과 같은 실행계획이 나오네요. 확인 부탁드려요.

 QUERY PLAN
—————————————————————————–
Hash Join (actual rows=1 loops=1)
 Hash Cond: (t3.c1 = a.c1)
 -> GroupAggregate (actual rows=2000000 loops=1)
 Group Key: t3.c1
 -> Index Scan using t3_c1_indx on t3 (actual rows=4000000 loops=1)
 -> Hash (actual rows=1 loops=1)
 Buckets: 1024 Batches: 1 Memory Usage: 9kB
 -> Index Scan using t2_uk on t2 a (actual rows=1 loops=1)
 Index Cond: (c2 = 10)
 Planning time: 0.324 ms
 Execution time: 1654.565 ms

Q&A #2

답변 (김시연): 2017-04-12

제 환경에서도 몇 차례 더 테스트를 수행해봤습니다. 제 환경에서는 책과 동일하게 In-Memory 방식의 속도가 약간 느립니다. eqon19님과는 다른 결과가 나오는건데요.

먼저, 저 처럼 In-Memory 방식이 느린 경우는 In-Memory 방식에서의 해시 충돌에 대한 부하가 Multi-Batch 방식에서의 Temp IO 부하보다 더 클 때입니다.
만일 반대의 경우라면 In-Memory 방식이 더 빠르겠죠.

그런데 eqon19님의 환경은 SSD이고, Explain 결과를 보면 제 환경보다 IO 속도가 더 빠릅니다. 즉, Temp IO의 부하가 더 적습니다. 따라서 Multi-Batch 시의
Temp IO 부하가 더 적으므로 Multi-Batch의 속도가 더 빠를 것 같지만, 결과는 오히려 In-Memory가 빠릅니다.

아마 이런 결과가 나온 이유는, 해시 테이블 검색 속도, 즉 CPU 속도가 제 환경보다 빨라서 인 것으로 보입니다.  참고로 제 PC 환경은 Intel-Core i7-6500U 2.50GHz 입니다.

질문: eqon19 : 2017-04-11

전자책 198페이지 질문 좀 드릴게요. 아래와 같이 테스트해봤는데 책과는 다르게 in-memory방식이 더 빠른건 왜일까요.. 테스트환경은 SSD를 사용하고 있는데 관계가 있을까요

postgres=# set work_mem=’1MB’;
SET
 postgres=#
 postgres=# explain (costs false, analyze, buffers)
 postgres-# select *
 postgres-# from t1 a, t2 b
 postgres-# where a.c1=b.c1;
 QUERY PLAN
———————————————————————————-
Hash Join (actual time=214.777..1924.983 rows=4000000 loops=1)
 Hash Cond: (b.c1 = a.c1)
 Buffers: shared hit=16300 read=10728 written=14, temp read=21217 written=21091
 -> Seq Scan on t2 b (actual time=0.022..312.475 rows=4000000 loops=1)
 Buffers: shared hit=14793 read=6829 written=1
 -> Hash (actual time=214.309..214.309 rows=1000000 loops=1)
 Buckets: 32768 Batches: 64 Memory Usage: 976kB
 Buffers: shared hit=1507 read=3899 written=13, temp written=4176
 -> Seq Scan on t1 a (actual time=0.006..80.531 rows=1000000 loops=1)
 Buffers: shared hit=1507 read=3899 written=13
 Planning time: 0.107 ms
 Execution time: 2082.692 ms
 (12 rows)
postgres=#
 postgres=# set work_mem=’100MB’;
SET
 postgres=#
 postgres=# explain (costs false, analyze, buffers)
 postgres-# select *
 postgres-# from t1 a, t2 b
 postgres-# where a.c1=b.c1;
 QUERY PLAN
——————————————————————————-
Hash Join (actual time=278.591..1791.172 rows=4000000 loops=1)
 Hash Cond: (b.c1 = a.c1)
 Buffers: shared hit=16240 read=10788
 -> Seq Scan on t2 b (actual time=1.078..280.867 rows=4000000 loops=1)
 Buffers: shared hit=14765 read=6857
 -> Hash (actual time=274.582..274.582 rows=1000000 loops=1)
 Buckets: 1048576 Batches: 1 Memory Usage: 54091kB
 Buffers: shared hit=1475 read=3931
 -> Seq Scan on t1 a (actual time=0.015..98.582 rows=1000000 loops=1)
 Buffers: shared hit=1475 read=3931
 Planning time: 0.140 ms
 Execution time: 1941.265 ms

Q&A #1 

답변 (김시연) : 2017-04-04


아래의 예제를 그대로 수행하면 말씀하신 것처럼 Merge Join으로 수행되는 것이 맞습니다. 현재 해당 쿼리를 가장 빠르게 수행하는 방법이 Merge Join이기 때문입니다. 그런데 설명을 위해서 제가 다른 조인 방법을 모두 off 해두었는데, 이 부분이 책에 빠져있습니다. ㅠㅠ

즉, 수행 전에

set enable_mergejoin=off;
set enable_hashjoin=off;

를 수행하고 테스트를 했는데, 이 부분이 책에 빠져있습니다. 4월2주차 출간본에는 해당 부분을 추가하도록 하겠습니다. 감사합니다. 🙂

질문:  eqon19 says:| 4월 4, 2017 (2:37 오후) 편집

질문 좀 드릴게요. 책 124페이지 맨위에 Nested Loop실행계획 부분이요. 똑같이 따라 했는데 아래처럼 Merge join으로 풀리는 이유가 뭘까요. 딱히 파라미터 바꾼게 없는것 같은데… 어떤 파라미터가 영향을 미쳤을까요.. 굳이 따지자면 테스트환경은 디스크 SSD 사용하고 있습니다만…

postgres=# explain
postgres-# select *
postgres-# from t1 a, t2 b
postgres-# where a.c1=b.c1;

QUERY PLAN
———————————————————————————–
Merge Join (cost=2.54..4.53 rows=10 width=2016)
Merge Cond: (b.c1 = a.c1)
-> Index Scan using t2_idx01 on t2 b (cost=0.28..181.28 rows=1000 width=1008)
-> Sort (cost=2.27..2.29 rows=10 width=1008)
Sort Key: a.c1
-> Seq Scan on t1 a (cost=0.00..2.10 rows=10 width=1008)

Advertisements

14 comments

  1. 전자책 198페이지 질문 좀 드릴게요. 아래와 같이 테스트해봤는데 책과는 다르게 in-memory방식이 더 빠른건 왜일까요.. 테스트환경은 SSD를 사용하고 있는데 관계가 있을까요

    postgres=# set work_mem=’1MB’;
    SET
    postgres=#
    postgres=# explain (costs false, analyze, buffers)
    postgres-# select *
    postgres-# from t1 a, t2 b
    postgres-# where a.c1=b.c1;
    QUERY PLAN
    ———————————————————————————-
    Hash Join (actual time=214.777..1924.983 rows=4000000 loops=1)
    Hash Cond: (b.c1 = a.c1)
    Buffers: shared hit=16300 read=10728 written=14, temp read=21217 written=21091
    -> Seq Scan on t2 b (actual time=0.022..312.475 rows=4000000 loops=1)
    Buffers: shared hit=14793 read=6829 written=1
    -> Hash (actual time=214.309..214.309 rows=1000000 loops=1)
    Buckets: 32768 Batches: 64 Memory Usage: 976kB
    Buffers: shared hit=1507 read=3899 written=13, temp written=4176
    -> Seq Scan on t1 a (actual time=0.006..80.531 rows=1000000 loops=1)
    Buffers: shared hit=1507 read=3899 written=13
    Planning time: 0.107 ms
    Execution time: 2082.692 ms
    (12 rows)

    postgres=#
    postgres=# set work_mem=’100MB’;
    SET
    postgres=#
    postgres=# explain (costs false, analyze, buffers)
    postgres-# select *
    postgres-# from t1 a, t2 b
    postgres-# where a.c1=b.c1;
    QUERY PLAN
    ——————————————————————————-
    Hash Join (actual time=278.591..1791.172 rows=4000000 loops=1)
    Hash Cond: (b.c1 = a.c1)
    Buffers: shared hit=16240 read=10788
    -> Seq Scan on t2 b (actual time=1.078..280.867 rows=4000000 loops=1)
    Buffers: shared hit=14765 read=6857
    -> Hash (actual time=274.582..274.582 rows=1000000 loops=1)
    Buckets: 1048576 Batches: 1 Memory Usage: 54091kB
    Buffers: shared hit=1475 read=3931
    -> Seq Scan on t1 a (actual time=0.015..98.582 rows=1000000 loops=1)
    Buffers: shared hit=1475 read=3931
    Planning time: 0.140 ms
    Execution time: 1941.265 ms

    Like

    1. 즉답이 어렵네요. 🙂 제가 어제 오늘 외부에 일이 있어서 저녁에 자세히 보고 답변 드리도록 하겠습니다.

      Like

      1. 답변 등록해두었습니다. 정확한 답변이라기보다는 약간의 추정이 포함되어 있습니다.

        Like

      2. 답변 감사드립니다. 회사에서 비교적 최근에 구입한 고스펙의 서버환경에서 테스트해서 그런가봅니다. PC환경 i5-5200 CPU 2.20GHz HDD 에서 다시테스트해보니 책과 같이 in-memory방식이 약간 느린 결과가 나오네요.

        Like

  2. 전자책 228페이지 아래 JPPD실패하는 경우의 예제 테스트해보니까

    QUERY PLAN
    ————————————————————————–
    Merge Join (actual rows=1 loops=1)
    Merge Cond: (t3.c1 = a.c1)
    -> GroupAggregate (actual rows=12 loops=1)
    Group Key: t3.c1
    -> Index Scan using t3_c1_indx on t3 (actual rows=25 loops=1)
    -> Materialize (actual rows=1 loops=1)
    -> Sort (actual rows=1 loops=1)
    Sort Key: a.c1
    Sort Method: quicksort Memory: 25kB
    -> Index Scan using t2_uk on t2 a (actual rows=1 loops=1)
    Index Cond: (c2 = 10)
    Planning time: 0.981 ms
    Execution time: 0.327 ms

    위처럼 나오는데
    set enable_mergejoin=off;

    이렇게 먼저 해줘야 책과 같은 실행계획이 나오네요. 확인 부탁드려요.
    l
    QUERY PLAN
    —————————————————————————–
    Hash Join (actual rows=1 loops=1)
    Hash Cond: (t3.c1 = a.c1)
    -> GroupAggregate (actual rows=2000000 loops=1)
    Group Key: t3.c1
    -> Index Scan using t3_c1_indx on t3 (actual rows=4000000 loops=1)
    -> Hash (actual rows=1 loops=1)
    Buckets: 1024 Batches: 1 Memory Usage: 9kB
    -> Index Scan using t2_uk on t2 a (actual rows=1 loops=1)
    Index Cond: (c2 = 10)
    Planning time: 0.324 ms
    Execution time: 1654.565 ms

    Like

  3. 전자책 260-261페이지 확인 좀 부탁드릴게요.
    1. 261페이지 예제에서 테스트데이터 입력이후에 아래와 같이 버킷수 10개 설정해주면 되는건가요?

    alter table t1 alter column c1 set statistics 10;

    select attname, attstattarget
    from pg_attribute
    where attrelid= (select oid from pg_class where relname=’t1′)
    and attname=’c1′;

    2. 261페이지 아래 값이 잘못된게 아닌지.. 예제대로 입력하면 1000000이 됩니다.

    reltupples
    —————
    1000036

    3. 위의 2번 때문인지 histogram_bounds도 책과 다르게 나옵니다.

    n_distinct | 20
    most_common_vals |
    most_common_freqs |
    histogram_bounds | {1,3,5,7,8,10,12,14,16,18,20}

    Like

    1. 네. 테스트해보니 postgresql.conf 파라미터를 변경하는 것과 테이블 칼럼을 변경하는 것과 analyze 결과가 다르네요.

      제가 테스트한 순서는 다음과 같습니다.
      1. $PGDATA\postgresql.conf 파일의 default_statistics_target = 10으로 변경후 재기동
      2. T1 테이블 생성 및 데이터 로딩 후 Analyze 수행
      => 부정확한 샘플링으로 인해 Analyze 결과가 1,000,036 으로 저장됨.

      eqon19님은 파라미터를 그대로 두고 칼럼속성을 변경하셨는데, 저도 그렇게 해보니 건수가 1,000,000으로 나오네요.

      따라서 테스트 장비에서 파라미터 변경 후에 다시 테스트해보시면 책과 동일한 결과가 나올 것 같습니다. 🙂

      PS) 제가

      Like

      1. 알려주신 대로 postgresql.conf 파일변경하고 테스트하니까 책과 같은 결과가 나오네요. 확인 감사드려요. ^^

        Like

  4. transaction internal책 많은 도움받았었기에 이번에도 기대를 가지고 스크립트 전부 테스트해보면서 정독했습니다. 업무중에 틈틈이 보기시작해서 오늘 일독 끝냈구요.. 다른 Postgres책과 달리 각장마다 실습스크립트가 있어서 많은 도움되었습니다. Vacuum쪽은 역시나 예상대로 어려웠구요. 좋은 책 내주셔서 감사합니다. ^^ 다음 책 기대하면서 mysql쪽 블로그내용 올려주시는거 공부해야겠네요.

    Like

    1. 앗. Transaction 책부터 독자셨으면 벌써 8년이 넘었네요. 도움이 되셨다니 정말 기쁘고, 정독해주신 점 깊이 감사드립니다. 🙂

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s