[PostgreSQL] pg_stat_statement 보는 법 (ft. analyze table)

데이터 bulk inset 

 

오라클에서 계층쿼리로 넣었다면 pg에선 dual테이블도 없고 connect by 구문도 없다 

insert into t1 
select level, '0715test'
from dual
connect by level < 10000;

 

generate_series 사용

insert into t1 
select generate_series(1,99999), '0715test';

 

 

 

실행시간 보는 법

psql  - \timing

suadb=> \timing
작업수행시간 보임



suadb=> insert into t1
suadb-> select generate_series(1,99999), '0715test';
INSERT 0 99999
작업시간: 116.898 ms



suadb=> select count(*) from t1;
 count
-------
 99999
(1개 행)

작업시간: 13.201 ms

 

 

 

explain analyze 

suadb=> explain analyze select * from t1;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..3081.98 rows=199998 width=13) (actual time=0.012..21.095 rows=199998 loops=1)
 Planning Time: 0.291 ms
 Execution Time: 30.840 ms
(3개 행)

작업시간: 31.968 ms

 

 

 

• Seq Scan on t1
  • Seq Scan: Sequential Scan을 의미하며, t1 테이블을 순차적으로 스캔하는 것을 나타냅니다.
  • cost=0.00..3081.98: 쿼리 실행에 필요한 비용 추정치입니다. 시작 비용은 0.00이고, 최종 비용은 3081.98입니다. 이 비용은 쿼리 최적화 과정에서 계산됩니다.
  • rows=199998: 예상되는 결과 행 수입니다. 여기서는 199998개의 행이 예상됩니다.
  • width=13: 결과 튜플의 평균 너비입니다. 여기서는 13바이트입니다.
• actual time=0.012..21.095
  • actual time: 실제 실행 시간을 나타냅니다.
  • 첫 번째 값인 0.012은 쿼리 실행의 시작 시간을 나타냅니다.
  • 두 번째 값인 21.095는 쿼리 실행이 완료된 시간을 나타냅니다.
• rows=199998 loops=1
  • rows: 실제로 반환된 행 수입니다. 여기서는 199998개의 행이 반환되었습니다.
  • loops: 계획 노드에서 반복된 횟수입니다. 여기서는 1번 반복되었습니다.
• Planning Time: 0.291 ms
  • Planning Time: 쿼리 최적화를 위해 사용된 시간입니다. 여기서는 0.291밀리초가 소요되었습니다.
• Execution Time: 30.840 ms
  • Execution Time: 쿼리를 실행하는 데 소요된 총 시간입니다. 여기서는 30.840밀리초가 소요되었습니다.

 

insert 하는데 걸린 시간 (수행도 함)

suadb=> explain analyze
suadb-> insert into t1
suadb-> select generate_series(1,99999), '0715test';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Insert on t1  (cost=0.00..1500.00 rows=0 width=0) (actual time=130.006..130.007 rows=0 loops=1)
   ->  Subquery Scan on "*SELECT*"  (cost=0.00..1500.00 rows=99999 width=42) (actual time=0.007..23.339 rows=99999 loops=1)
         ->  ProjectSet  (cost=0.00..500.01 rows=99999 width=36) (actual time=0.005..10.563 rows=99999 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.007 rows=1 loops=1)
 Planning Time: 0.095 ms
 Execution Time: 130.048 ms
(6개 행)

작업시간: 130.657 ms

 

 

• Insert on t1
  • Insert on t1: t1 테이블에 대한 삽입 작업을 나타냅니다.
  • cost=0.00..1500.00: 쿼리 실행에 필요한 비용 추정치입니다. 시작 비용은 0.00이고, 최종 비용은 1500.00입니다.
  • rows=0: 예상되는 결과 행 수는 0입니다. 이는 삽입 작업에서 반환되는 행이 없음을 나타냅니다.
  • width=0: 결과 튜플의 평균 너비입니다. 여기서는 0바이트입니다.
• Subquery Scan on "SELECT"
  • Subquery Scan: 하위 쿼리의 결과를 스캔하여 "SELECT"이라는 이름으로 사용합니다.
  • cost=0.00..1500.00: 하위 쿼리 실행에 필요한 비용 추정치입니다.
  • rows=99999: 하위 쿼리에서 반환되는 예상 행 수는 99999입니다.
  • width=42: 하위 쿼리의 결과 튜플의 평균 너비는 42바이트입니다.
• ProjectSet
  • ProjectSet: 결과를 생성하기 위한 프로젝션 세트 연산을 나타냅니다.
  • cost=0.00..500.01: 프로젝션 세트 연산에 필요한 비용 추정치입니다.
  • rows=99999: 예상되는 결과 행 수는 99999입니다.
  • width=36: 결과 튜플의 평균 너비는 36바이트입니다.
• Result
  • Result: 단순 결과 생성 연산을 나타냅니다.
  • cost=0.00..0.01: 결과 생성에 필요한 비용 추정치입니다.
  • rows=1: 예상되는 결과 행 수는 1입니다.
  • width=0: 결과 튜플의 평균 너비는 0바이트입니다.
• Planning Time: 0.095 ms
  • Planning Time: 쿼리 최적화를 위해 사용된 시간입니다. 여기서는 0.095밀리초가 소요되었습니다.
• Execution Time: 130.048 ms
  • Execution Time: 쿼리를 실행하는 데 소요된 총 시간입니다. 여기서는 130.048밀리초가 소요되었습니다.

 

 

pg_stat_statements로 가장 오래걸린 쿼리 확인하기 

select queryid , query, calls , mean_exec_time, rows, shared_blks_hit, shared_blks_written , wal_records, wal_bytes 
from pg_stat_statements order by total_exec_time desc limit 10;

 

ueryid             |query                                                                                                                                                                                                                                                          |calls|mean_exec_time     |rows  |shared_blks_hit|shared_blks_written|wal_records|wal_bytes|
-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+-------------------+------+---------------+-------------------+-----------+---------+
5146504151523065246|explain analyze¶insert into t1 ¶select generate_series(1,99999), '0715test'                                                                                                                                                                                    |    1|         131.469697|     0|         101090|                540|      99999|  6800558|
1671877142296610048|explain analyze¶insert into t1 ¶select generate_series(1,99999), '0715test'                                                                                                                                                                                    |    1|         130.230716|     0|         101082|                541|      99999|  6799932|
8657898367407064797|create database suaDB                                                                                                                                                                                                                                          |    1|         123.864218|     0|             71|                  0|        301|    15263|
 -22186280837627084|-- 대량 데이터 insert ¶insert into t1 ¶select generate_series($1,$2), $3                                                                                                                                                                                            |    2| 113.12703149999999|199998|         202168|               1082|     199998| 13599864|
7560397248061821299|explain analyze select count(*) from t1                                                                                                                                                                                                                        |    1|          44.145325|     0|           1085|                  0|          0|        0|
7145772046314854443|explain analyze select * from t1                                                                                                                                                                                                                               |    3|  34.52462633333334|     0|           3269|                  0|          0|        0|
1523263593140479898|select count(*) from t1                                                                                                                                                                                                                                        |    4|        19.31473775|     4|           4327|                  0|          0|        0|
 -96618895079891428|select pg_tablespace_size($1)                                                                                                                                                                                                                                  |    2| 17.706819499999998|     2|              2|                  0|          0|        0|
5016033534714678944|SELECT pp.oid as poid, pp.* FROM pg_catalog.pg_proc pp WHERE pp.proname ILIKE $1 AND pp.pronamespace IN ($2,$3,$4) ORDER BY pp.proname LIMIT $5                                                                                                                |   20|          1.8081364|   100|           2134|                  0|          3|      168|
6593845899897366767|SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, $1, $2),¶  pg_catalog.pg_get_constraintdef(con.oid, $3), contype, condeferrable, condeferred, i.indisreplident, c2.reltablespace¶FRO|   66|0.20331674242424236|   125|           5473|                  0|          0|        0|