F.29. pg_stat_statements

F.29.1. pg_stat_statements 뷰

이 모듈로 제공하는 쿼리 실행 통계 정보는 pg_stat_statements 이름의 뷰로 제공된다. 이 뷰는 하나의 로우가 하나의 식별되는 쿼리를 뜻하며, 부가적으로 그 쿼리가 실행되었던 데이터베이스 ID, 사용자 ID등을 포함 하고 있다. (이 뷰에서 제공하고 있는 최대 쿼리 개수는 이 모듈 환경 설정값에 따라 결정 된다.) 표 F-22 표에서는 이 뷰의 각 칼럼에 대한 설명을 하고 있다.

보안 문제로, 슈퍼유저가 아닌 일반 사용자는 다른 사용자의 쿼리 내용과 queryid를 볼 수 없지만, 그것들의 통계 정보는 볼 수 있다. (물론 해당 데이터베이스 이 모듈의 확장 기능이 설치 되어 있는 경우를 말한다)

실행계획을 짤 수 있는 쿼리들(SELECT, INSERT, UPDATE, DELETE 구문들)은 그 실행계획이 같은 쿼리들에 대해서는 그 조건 값들이 다르더라도 같은 쿼리로 취급한다. 즉, 쿼리에서 사용한 글자 그대로의 상수 값들만 달라서 실질적으로 그 실행계획이 같은 것들은 pg_stat_statements 뷰에서는 하나의 쿼리로 취급한다. 하지만, 앞에서 설명한 쿼리들이 아닌 객체 정의 명령들은 상수 값들과 관계 없이 무조건 개별 쿼리로 취급한다.

pg_stat_statements 뷰에서 상수 부분에 ? 표시된 것이 앞에서 설명한 동일 쿼리로 취급하기 위한 상수 값의 변경된 값이다. 첫번째 쿼리는 pg_stat_statements 뷰의 항목과 관련된 특정 해시 값을 가진다. 그래서 다음 실행되는 쿼리가 이렇게 변환된 것과 같은 해시 값을 가진다면, 같은 쿼리로 간주한다. ( - 다음 문장 의역임) The rest of the query text is that of the first query that had the particular hash value associated with the pg_stat_statements entry.

또한 몇몇 상황에서는 실제 쿼리가 다른 쿼리임에도 불구하고, pg_stat_statements 뷰에서 같은 항목의 쿼리로 취급되기도 한다. 일반적으로 이런 경우는 구문적으로 보면 동일 쿼리이지만, 그 해시 값이 약간 다른 경우, 같은 쿼리로 취급한다. (하지만, 이런 통합작업은 사용자나 데이터베이스가 다른 상황에서는 일어나지 않는다.)

이 해시 값 비교 방식은 쿼리 구문분석 다음에 처리 되기 때문에, 위와 반대로 동일 쿼리임에도 불구하고, search_path 와 같은 설정값이 다르기 때문에 다른 쿼리로 처리하는 경우도 발생할 수 있다.

Consumers of pg_stat_statements may wish to use queryid (perhaps in combination with dbid and userid) as a more stable and reliable identifier for each entry than its query text. However, it is important to understand that there are only limited guarantees around the stability of the queryid hash value. Since the identifier is derived from the post-parse-analysis tree, its value is a function of, among other things, the internal object identifiers appearing in this representation. This has some counterintuitive implications. For example, pg_stat_statements will consider two apparently-identical queries to be distinct, if they reference a table that was dropped and recreated between the executions of the two queries. The hashing process is also sensitive to differences in machine architecture and other facets of the platform. Furthermore, it is not safe to assume that queryid will be stable across major versions of PostgreSQL.

As a rule of thumb, queryid values can be assumed to be stable and comparable only so long as the underlying server version and catalog metadata details stay exactly the same. Two servers participating in replication based on physical WAL replay can be expected to have identical queryid values for the same query. However, logical replication schemes do not promise to keep replicas identical in all relevant details, so queryid will not be a useful identifier for accumulating costs across a set of logical replicas. If in doubt, direct testing is recommended.

The representative query texts are kept in an external disk file, and do not consume shared memory. Therefore, even very lengthy query texts can be stored successfully. However, if many long query texts are accumulated, the external file might grow unmanageably large. As a recovery method if that happens, pg_stat_statements may choose to discard the query texts, whereupon all existing entries in the pg_stat_statements view will show null query fields, though the statistics associated with each queryid are preserved. If this happens, consider reducing pg_stat_statements.max to prevent recurrences.

F.29.2. 함수들

F.29.3. 환경설정 매개변수들

이 모듈은 pg_stat_statements.max * track_activity_query_size 바이트만큼의 공유 메모리를 추가로 필요로 한다. 참고할 사항은 pg_stat_statements.track 값을 none으로 지정했다 하더라도 앞의 계산식 만큼의 공유 메모리는 항상 사용한다는 점이다.

postgresql.conf 파일에 이 모듈을 사용할 때의 기본 내용은 다음과 같다:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.29.4. 예제 출력

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
-[ RECORD 1 ]---------------------------------------------------------------------
query       | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?;
calls       | 3000
total_time  | 9609.00100000002
rows        | 2836
hit_percent | 99.9778970000200936
-[ RECORD 2 ]---------------------------------------------------------------------
query       | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?;
calls       | 3000
total_time  | 8015.156
rows        | 2990
hit_percent | 99.9731126579631345
-[ RECORD 3 ]---------------------------------------------------------------------
query       | copy pgbench_accounts from stdin
calls       | 1
total_time  | 310.624
rows        | 100000
hit_percent | 0.30395136778115501520
-[ RECORD 4 ]---------------------------------------------------------------------
query       | UPDATE pgbench_accounts SET abalance = abalance + ? WHERE aid = ?;
calls       | 3000
total_time  | 271.741999999997
rows        | 3000
hit_percent | 93.7968855088209426
-[ RECORD 5 ]---------------------------------------------------------------------
query       | alter table pgbench_accounts add primary key (aid)
calls       | 1
total_time  | 81.42
rows        | 0
hit_percent | 34.4947735191637631

F.29.5. 만든이

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp>. Query normalization added by Peter Geoghegan <peter@2ndquadrant.com>.