• Home
  • SysInfo
  • Cluster
    • Databases sizes
    • Tablespaces sizes
    • Connections
      • Connections
      • Connections by type
    • Deadlocks
    • Cache utilization
    • Shared buffers statistics
      • Shared buffers utilization
      • Database in shared buffers
      • Usagecount in shared buffers
      • Usagecount in dirty buffers
    • Background writer
      • Background writer buffers
      • Background writer counters
    • Temporary files
      • Temporary files
      • Temporary files size
    • Wal / Checkpoint
      • Wal files
      • Wal bytes written
      • Checkpoints counter
      • Checkpoints write time
      • Archiver stats
    • Queries Reads / Writes
      • Read tuples
      • Write ratio
      • Read vs Write queries
      • Commits vs Rollbacks
    • Replication statistics
      • Replication lag
      • Canceled queries
      • Conflicts
    • Configurations
      • PostgreSQL configuration
      • PostgreSQL settings
      • Database/Roles settings
      • PostgreSQL ALTER SYSTEM configuration
      • PostgreSQL recovery configuration
      • PostgreSQL authorization
      • User Name Maps
      • Pgbouncer settings
  • Databases
    • engine
      • Database info
      • Tables statistics
        • Size and tuples
      • Indexes statistics
        • Size and tuples
        • Unused Indexes
        • Redundant indexes
        • Missing indexes
      • Buffercache Statistics
      • Statements statistics
      • Database size
      • Connections
        • Connections
        • Connections by type
      • Cache utilization
      • Locks
        • Deadlocks
        • Locks per types
        • Locks per modes
        • Locks granted or not
      • Temporary files
        • Temporary files
        • Temporary files size
      • Queries Reads / Writes
        • Read tuples
        • Write ratio
        • Read vs Write queries
        • Commits vs Rollbacks
      • Canceled queries
      • Conflicts
    • engine2
      • Database info
      • Tables statistics
      • Indexes statistics
      • Buffercache Statistics
      • Statements statistics
      • Database size
      • Connections
        • Connections
        • Connections by type
      • Cache utilization
      • Locks
        • Deadlocks
        • Locks per types
        • Locks per modes
        • Locks granted or not
      • Temporary files
        • Temporary files
        • Temporary files size
      • Queries Reads / Writes
        • Read tuples
        • Write ratio
        • Read vs Write queries
        • Commits vs Rollbacks
      • Canceled queries
      • Conflicts
    • engine_dao_tests
      • Database info
      • Tables statistics
      • Indexes statistics
      • Buffercache Statistics
      • Statements statistics
      • Database size
      • Connections
        • Connections
        • Connections by type
      • Cache utilization
      • Locks
        • Deadlocks
        • Locks per types
        • Locks per modes
        • Locks granted or not
      • Temporary files
        • Temporary files
        • Temporary files size
      • Queries Reads / Writes
        • Read tuples
        • Write ratio
        • Read vs Write queries
        • Commits vs Rollbacks
      • Canceled queries
      • Conflicts
    • postgres
      • Database info
      • Tables statistics
      • Indexes statistics
      • Buffercache Statistics
      • Statements statistics
      • Database size
      • Connections
        • Connections
        • Connections by type
      • Cache utilization
      • Locks
        • Deadlocks
        • Locks per types
        • Locks per modes
        • Locks granted or not
      • Temporary files
        • Temporary files
        • Temporary files size
      • Queries Reads / Writes
        • Read tuples
        • Write ratio
        • Read vs Write queries
        • Commits vs Rollbacks
      • Canceled queries
      • Conflicts
  • System
    • Cpu
    • Memory
    • Swap
    • Load
    • Task list
    • Run queue
    • Context switches
    • Tasks created per second
    • Blocks
    • Transfers per second
    • Pages
    • Devices
      • sda
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • luks-9a431792-a6b7-43f9-9f68-b77a9e4784ba
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • fedora_dhcp131--31-root
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • fedora_dhcp131--31-swap
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • fedora_dhcp131--31-home
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • loop0
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • loop1
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
      • docker-253:1-632-pool
        • Cpu utilization
        • Read/write bytes per second
        • Transfert per second
        • Average service time
    • Network
      • wlp4s0
        • Network utilization
        • Network errors
      • virbr0-nic
        • Network utilization
        • Network errors
      • docker0
        • Network utilization
        • Network errors
      • bond0
        • Network utilization
        • Network errors
      • virbr0
        • Network utilization
        • Network errors
      • lo
        • Network utilization
        • Network errors
      • em1
        • Network utilization
        • Network errors
  • About




  • 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 ';';210.010s0.222s210
    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 ';';210.010s0.215s210
    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 ';';200.007s0.157s67480
    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 ';';200.007s0.145s67480
    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 ';';200.006s0.127s400
    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 ';';200.005s0.118s400
    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 ';';210.004s0.095s1260
    COPY (SELECT date_trunc(?. now()). datid. datname. pg_database_size(datid) AS size FROM pg_stat_database ORDER BY datname) TO STDOUT CSV DELIMITER ';';210.004s0.090s1260
    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 ';';210.001s0.025s200
    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 ';';200.001s0.021s200
    COPY (SELECT date_trunc('seconds'. now()). * FROM pg_stat_database_conflicts ORDER BY datname) TO STDOUT CSV DELIMITER ';';210s0.011s1260
    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 ';';210s0.010s200
    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 ';';210s0.010s00
    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 ';';210s0.009s1260
    SELECT setting FROM pg_settings WHERE name IN (?.?.?.?) ORDER BY name;210s0.009s840
    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 ';';210s0.005s1260
    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 ';';200s0.003s200
    COPY (SELECT date_trunc(?. now()). * FROM pg_stat_database_conflicts ORDER BY datname) TO STDOUT CSV DELIMITER ';';210s0.002s1260
    SELECT n.nspname||?||p.proname FROM pg_proc p. pg_namespace n WHERE p.pronamespace=n.oid AND n.nspname NOT IN (?. ?);10s0.000s20
    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 ';';210s0.000s00
    SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn;210s0.000s840
    SELECT pg_stat_statements_reset();10s0.000s10
    SET application_name TO pgcluu;210s0.000s00
    SELECT nspname FROM pg_namespace WHERE nspname !~ ? AND nspname <> ? ORDER BY 110s0.000s10
    SELECT n.nspname||?||p.proname FROM pg_proc p. pg_namespace n WHERE p.proname=? AND p.pronamespace=n.oid;10s0.000s10
    SELECT version();20s0.000s20
    SELECT extname||?||extversion FROM pg_extension;10s0.000s20
    SELECT count(tgname) FROM pg_trigger WHERE NOT tgisinternal;10s0.000s10
    SELECT ? FROM pg_user WHERE usename=? AND usesuper10s0.000s10

© Gilles Darold 2012-2015

Report generated by pgCluu 2.4.