-
Statements statistics on postgres database
Top N statistics about slowest or most used queries.
Query Calls Avg time Total time Rows Temp blocks written COPY (SELECT date_trunc('seconds'. now()). checkpoints_timed. checkpoints_req. checkpoint_write_time. checkpoint_sync_time. buffers_checkpoint. buffers_clean. maxwritten_clean. buffers_backend. buffers_backend_fsync. buffers_alloc. date_trunc('seconds'. stats_reset) AS stats_reset FROM pg_stat_bgwriter ) TO STDOUT CSV DELIMITER ';'; 21 0.010s 0.223s 21 0 COPY (SELECT date_trunc(?. now()). checkpoints_timed. checkpoints_req. checkpoint_write_time. checkpoint_sync_time. buffers_checkpoint. buffers_clean. maxwritten_clean. buffers_backend. buffers_backend_fsync. buffers_alloc. date_trunc(?. stats_reset) AS stats_reset FROM pg_stat_bgwriter ) TO STDOUT CSV DELIMITER ';'; 21 0.010s 0.215s 21 0 COPY (SELECT date_trunc('seconds'. now()). r.rolname. d.datname. regexp_replace(regexp_replace(query. E'[ \n]+'. ' '. 'g'). E';'. ';'. 'g'). calls. total_time. rows. shared_blks_hit. shared_blks_read. shared_blks_written. local_blks_hit. local_blks_read. local_blks_written. temp_blks_read. temp_blks_written FROM public.pg_stat_statements q. pg_database d. pg_roles r WHERE q.userid=r.oid and q.dbid=d.oid ORDER BY r.rolname. d.datname) TO STDOUT CSV DELIMITER ';'; 20 0.008s 0.166s 6843 0 COPY (SELECT date_trunc(?. now()). r.rolname. d.datname. regexp_replace(regexp_replace(query. ?. ?. ?). ?. ?. ?). calls. total_time. rows. shared_blks_hit. shared_blks_read. shared_blks_written. local_blks_hit. local_blks_read. local_blks_written. temp_blks_read. temp_blks_written FROM public.pg_stat_statements q. pg_database d. pg_roles r WHERE q.userid=r.oid and q.dbid=d.oid ORDER BY r.rolname. d.datname) TO STDOUT CSV DELIMITER ';'; 20 0.007s 0.154s 6843 0 COPY (SELECT date_trunc('seconds'. now()). spcname. pg_tablespace_size(spcname). CASE WHEN pg_tablespace_location(oid) = '' THEN CASE WHEN spcname = 'pg_default' THEN (select setting from pg_settings where name='data_directory')||'/base' ELSE (select setting from pg_settings where name='data_directory')||'/global' END ELSE pg_tablespace_location(oid) END as tablespace_location FROM pg_tablespace ORDER BY spcname) TO STDOUT CSV DELIMITER ';'; 20 0.006s 0.127s 40 0 COPY (SELECT date_trunc(?. now()). spcname. pg_tablespace_size(spcname). CASE WHEN pg_tablespace_location(oid) = ? THEN CASE WHEN spcname = ? THEN (select setting from pg_settings where name=?)||? ELSE (select setting from pg_settings where name=?)||? END ELSE pg_tablespace_location(oid) END as tablespace_location FROM pg_tablespace ORDER BY spcname) TO STDOUT CSV DELIMITER ';'; 20 0.005s 0.118s 40 0 COPY (SELECT date_trunc('seconds'. now()). datid. datname. pg_database_size(datid) AS size FROM pg_stat_database ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0.005s 0.117s 126 0 COPY (SELECT date_trunc(?. now()). datid. datname. pg_database_size(datid) AS size FROM pg_stat_database ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0.005s 0.111s 126 0 COPY (SELECT date_trunc('seconds'. now()). COUNT(*) AS total. coalesce(SUM((state NOT LIKE 'idle%')::integer). 0) AS active. coalesce(SUM(waiting::integer). 0) AS waiting. coalesce(SUM((state = 'idle in transaction')::integer). 0) AS idle_in_xact. pg_database.datname AS datname FROM pg_stat_activity JOIN pg_database ON (pg_database.oid=pg_stat_activity.datid) WHERE pid <> pg_backend_pid() GROUP BY pg_database.datname) TO STDOUT CSV DELIMITER ';'; 21 0.001s 0.029s 20 0 COPY (SELECT date_trunc('seconds'. now()). count(*) AS num_file. pg_xlogfile_name(pg_current_xlog_location()) AS current. sum(is_recycled::int) AS is_recycled. sum((NOT is_recycled)::int) AS written. CASE WHEN max_wal1 > max_wal2 THEN max_wal1 ELSE max_wal2 END AS max_wal FROM ( SELECT file > first_value(file) OVER w AS is_recycled .1 + ( current_setting('checkpoint_segments')::float4 * ( 2 + current_setting('checkpoint_completion_target')::float4 )) AS max_wal1. 1 + current_setting('wal_keep_segments')::float4 + current_setting('checkpoint_segments')::float4 AS max_wal2 FROM pg_ls_dir('pg_xlog') as file WHERE file ~ '^[0-9A-F]{24}$' WINDOW w AS ( ORDER BY (pg_stat_file('pg_xlog/'||file)).modification DESC ) ) AS t GROUP BY 6 ) TO STDOUT CSV DELIMITER ';'; 20 0.001s 0.021s 20 0 COPY (SELECT date_trunc('seconds'. now()). pid. usesysid. usename. application_name. client_addr. client_hostname. client_port. date_trunc('seconds'. backend_start) AS backend_start. state. pg_current_xlog_location() AS master_location. sent_location. write_location. flush_location. replay_location. sync_priority. sync_state FROM pg_stat_replication ORDER BY application_name) TO STDOUT CSV DELIMITER ';'; 21 0s 0.013s 0 0 COPY (SELECT date_trunc(?. now()). COUNT(*) AS total. coalesce(SUM((state NOT LIKE ?)::integer). ?) AS active. coalesce(SUM(waiting::integer). ?) AS waiting. coalesce(SUM((state = ?)::integer). ?) AS idle_in_xact. pg_database.datname AS datname FROM pg_stat_activity JOIN pg_database ON (pg_database.oid=pg_stat_activity.datid) WHERE pid <> pg_backend_pid() GROUP BY pg_database.datname) TO STDOUT CSV DELIMITER ';'; 21 0s 0.012s 20 0 COPY (SELECT date_trunc('seconds'. now()). * FROM pg_stat_database_conflicts ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0s 0.012s 126 0 COPY (SELECT date_trunc('seconds'. now()). datid. datname. numbackends. xact_commit. xact_rollback. blks_read. blks_hit. tup_returned. tup_fetched. tup_inserted. tup_updated. tup_deleted. conflicts. date_trunc('seconds'. stats_reset) AS stats_reset. temp_files. temp_bytes. deadlocks. blk_read_time. blk_write_time FROM pg_stat_database ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0s 0.011s 126 0 SELECT setting FROM pg_settings WHERE name IN (?.?.?.?) ORDER BY name; 21 0s 0.009s 84 0 COPY (SELECT date_trunc(?. now()). datid. datname. numbackends. xact_commit. xact_rollback. blks_read. blks_hit. tup_returned. tup_fetched. tup_inserted. tup_updated. tup_deleted. conflicts. date_trunc(?. stats_reset) AS stats_reset. temp_files. temp_bytes. deadlocks. blk_read_time. blk_write_time FROM pg_stat_database ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0s 0.006s 126 0 COPY (SELECT date_trunc(?. now()). count(*) AS num_file. pg_xlogfile_name(pg_current_xlog_location()) AS current. sum(is_recycled::int) AS is_recycled. sum((NOT is_recycled)::int) AS written. CASE WHEN max_wal1 > max_wal2 THEN max_wal1 ELSE max_wal2 END AS max_wal FROM ( SELECT file > first_value(file) OVER w AS is_recycled .? + ( current_setting(?)::float4 * ( ? + current_setting(?)::float4 )) AS max_wal1. ? + current_setting(?)::float4 + current_setting(?)::float4 AS max_wal2 FROM pg_ls_dir(?) as file WHERE file ~ ? WINDOW w AS ( ORDER BY (pg_stat_file(?||file)).modification DESC ) ) AS t GROUP BY 6 ) TO STDOUT CSV DELIMITER ';'; 20 0s 0.003s 20 0 COPY (SELECT date_trunc(?. now()). * FROM pg_stat_database_conflicts ORDER BY datname) TO STDOUT CSV DELIMITER ';'; 21 0s 0.002s 126 0 SELECT pg_stat_statements_reset(); 1 0s 0.000s 1 0 COPY (SELECT date_trunc(?. now()). pid. usesysid. usename. application_name. client_addr. client_hostname. client_port. date_trunc(?. backend_start) AS backend_start. state. pg_current_xlog_location() AS master_location. sent_location. write_location. flush_location. replay_location. sync_priority. sync_state FROM pg_stat_replication ORDER BY application_name) TO STDOUT CSV DELIMITER ';'; 21 0s 0.000s 0 0 SELECT n.nspname||?||p.proname FROM pg_proc p. pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname NOT IN (?. ?); 1 0s 0.000s 2 0 SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn; 21 0s 0.000s 84 0 SET application_name TO pgcluu; 21 0s 0.000s 0 0 SELECT version(); 2 0s 0.000s 2 0 SELECT nspname FROM pg_namespace WHERE nspname !~ ? AND nspname <> ? ORDER BY 1 1 0s 0.000s 1 0 SELECT n.nspname||?||p.proname FROM pg_proc p. pg_namespace n WHERE p.proname=? AND p.pronamespace=n.oid; 1 0s 0.000s 1 0 SELECT extname||?||extversion FROM pg_extension; 1 0s 0.000s 2 0 SELECT count(tgname) FROM pg_trigger WHERE NOT tgisinternal; 1 0s 0.000s 1 0 SELECT ? FROM pg_user WHERE usename=? AND usesuper 1 0s 0.000s 1 0