PG_STAT_STATEMENTS Sistem Tablosu - İstatistikleri İçerir

Giriş

Açıklaması şöyle

The pg_stat_statements view returns statistics for all SQL queries that have run on the database since the last reset of statistics.

Note that the pg_stat_statements view is not enabled by default in Postgres. You may need to have a chat with your DevOps/DB admin team to enable it.

Kurulum

Eğer kurulu değilse kurmak için şöyle yaparız

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

calls Sütunu

Kaç defa çağrıldığını gösterir

Örnek

Şöyle yaparız. total_time milisaniyeden dakikaya çevrilir. Ortalama ise milisaniye olarak gösterilir

SELECT
  (total_time / 1000 / 60) as total,
  (total_time/calls) as avg,
  query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

Örnek

Şöyle yaparız

SELECT
  pss.userid,
  pss.dbid,
  pd.datname as db_name,
  round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time,
  pss.calls,
  round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean,
  round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
  pss.query
FROM pg_stat_statements pss, pg_database pd
WHERE pd.oid=pss.dbid
ORDER BY (pss.total_exec_time + pss.total_plan_time)
DESC LIMIT 100;

mean_time Sütunu

Örnek

En yavaş 20 sorguyu görmek için şöyle yaparız

SELECT query,
    round(total_time::numeric, 2) AS total_time,
    calls,
    round(mean_time::numeric, 2) AS mean,
    round(stddev_time::numeric, 2) AS std_dev,
    round ((100* total_time / sum (total_time::numeric)
       OVER ())::numeric, 2) AS percentage_overall, 
    userid, dbid, queryid
FROM pg_stat_statements
WHERE mean_time > 10
ORDER BY total_time DESC
LIMIT 20;

total_time Sütunu

Örnek - Query Profiling

Yavaş sorguları bulmak için kullanılabilir. Açıklaması şöyle.

This can be accomplished through their modules auto_explain and pg_stat_statements, checking pg_stat_activity for table and index activity on your server, analyzing the slow query log, or reviewing in your code.

Açıklaması şöyle

Query profiling is a technique for analyzing query performance and identifying potential bottlenecks. PostgreSQL provides support for query profiling through the use of the pg_stat_statements module, which can be used to collect and analyze query execution statistics.

Örnek

En yavaş 10 sorguyu görmek için şöyle yaparız

SELECT query, total_time, calls, rows FROM pg_stat_statements 
  ORDER BY total_time DESC LIMIT 10;

Örnek

En yavaş 10 sorguyu görmek için şöyle yaparız

-- Identify slow-running queries
SELECT query, calls, total_time, rows
FROM pg_stat_statements
WHERE total_time > '1 second'
ORDER BY total_time DESC;

2. Query Caching

Açıklaması şöyle

Query caching is a technique for storing the results of frequently executed queries in memory for faster access. PostgreSQL provides support for query caching through the use of the shared cache, which can be configured to cache query plans and results.

Örnek

Şöyle yaparız

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all