Skip to content

Instantly share code, notes, and snippets.

@virusdave

virusdave/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Last active January 27, 2022 21:33
Show Gist options
  • Save virusdave/83ed288576e4b96b7d16d88dad13e782 to your computer and use it in GitHub Desktop.
Save virusdave/83ed288576e4b96b7d16d88dad13e782 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. virusdave revised this gist Dec 9, 2021. 1 changed file with 6 additions and 3 deletions.
    9 changes: 6 additions & 3 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -77,17 +77,20 @@ ORDER BY age desc;
    -- vacuum command
    VACUUM (VERBOSE, ANALYZE);

    -- all database users
    -- ???
    select * from pg_stat_activity where current_query not like '<%';

    -- all databases and their sizes
    -- all database users
    select * from pg_user;

    -- all tables and their size, with/without indexes
    -- all databases and their sizes
    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    -- all tables and their size, with/without indexes
    ???

    -- cache hit rates (should not be less than 0.99)
    SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM pg_statio_user_tables;
  2. virusdave revised this gist Dec 9, 2021. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -92,8 +92,12 @@ order by pg_database_size(datname) desc;
    SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
    -- table index usage rates (should not be less than 99.0)
    SELECT
    relname as tablename,
    10000 * idx_scan / (seq_scan + idx_scan) / 100.0 as percent_of_times_index_used,
    n_live_tup as rows_in_table,
    (seq_scan + idx_scan) as accesses
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;

  3. virusdave revised this gist Sep 27, 2018. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -29,6 +29,9 @@ WHERE state = 'active'
    ORDER BY age desc;


    -- Forcibly kill idle connections or queries, or mid-transaction statement batches.
    SELECT pg_terminate_backend(procpid);

    -- Below are less custom tailored for us, but useful references --

    -- show running queries
    @@ -68,9 +71,6 @@ WHERE state != 'idle'
    AND AGE(clock_timestamp(), query_start) > INTERVAL '1 minute'
    ORDER BY age desc;

    -- kill idle connections or queries
    SELECT pg_terminate_backend(procpid);



    -- Below are much less useful for us --
  4. virusdave revised this gist Oct 26, 2016. 1 changed file with 33 additions and 0 deletions.
    33 changes: 33 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,36 @@
    -- CHECK TIMINGS ON ACTIVE AND EXPENSIVE QUERIES
    SELECT activity.*
    FROM (
    SELECT
    pid,
    CASE WHEN state = 'active' THEN AGE(clock_timestamp(), query_start)
    ELSE AGE(state_change, query_start)
    END as query_duration, -- This is how long the most recent query was running (or is running so far, if still active)
    AGE(clock_timestamp(), xact_start) as xact_duration, -- Same, but for the currently active transaction
    CASE WHEN state = 'active' THEN INTERVAL '0'
    ELSE AGE(clock_timestamp(), state_change)
    END as sleep_age, -- For idle connections, how long ago the last query completed.
    state,
    query,
    client_addr,
    query_start,
    state_change
    FROM pg_stat_activity) activity
    WHERE (activity.sleep_age < INTERVAL '5 minutes' AND activity.query_duration > INTERVAL '5 ms') OR activity.state = 'active'
    ORDER BY query_duration DESC, xact_duration DESC;


    -- THIS KILLS THE QUERY - kill long-running quries.
    SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, client_addr, query
    FROM pg_stat_activity
    WHERE state = 'active'
    AND query NOT ILIKE '%pg_stat_activity%'
    AND AGE(clock_timestamp(), query_start) > INTERVAL '10 seconds'
    ORDER BY age desc;


    -- Below are less custom tailored for us, but useful references --

    -- show running queries
    SELECT pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
    FROM pg_stat_activity
  5. virusdave revised this gist Aug 16, 2016. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -5,6 +5,12 @@ WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY age desc;

    -- Show outstanding connections
    SELECT client_hostname, client_addr, COUNT(*)
    FROM pg_stat_activity
    GROUP BY client_hostname, client_addr
    ORDER BY count DESC;

    -- count of running queries
    SELECT COUNT(*)
    FROM pg_stat_activity
  6. virusdave revised this gist Jul 19, 2016. 1 changed file with 28 additions and 9 deletions.
    37 changes: 28 additions & 9 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,21 +1,40 @@
    -- show running queries (pre 9.2)
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    -- show running queries
    SELECT pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY age desc;

    -- show running queries (9.2)
    SELECT pid, age(query_start, clock_timestamp()), usename, query
    -- count of running queries
    SELECT COUNT(*)
    FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'

    -- count expensive or stalled queries
    SELECT COUNT(*)
    FROM pg_stat_activity
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    AND AGE(clock_timestamp(), query_start) > INTERVAL '30 seconds'

    -- kill running query
    SELECT pg_cancel_backend(procpid);

    -- kill idle query
    -- kill long running active queries
    SELECT pg_cancel_backend(pid) as killed, pid, AGE(clock_timestamp(), query_start) as age, query, usename, *
    FROM pg_stat_activity
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    AND AGE(clock_timestamp(), query_start) > INTERVAL '1 minute'
    ORDER BY age desc;

    -- kill idle connections or queries
    SELECT pg_terminate_backend(procpid);



    -- Below are much less useful for us --
    -- vacuum command
    VACUUM (VERBOSE, ANALYZE);

  7. @rgreenjr rgreenjr renamed this gist Sep 8, 2015. 1 changed file with 0 additions and 0 deletions.
    0 Useful PostgreSQL Queries and Commands → postgres_queries_and_commands.sql
    View file
    File renamed without changes.
  8. @rgreenjr rgreenjr renamed this gist Sep 8, 2013. 1 changed file with 0 additions and 0 deletions.
    0 postgres.sql → Useful PostgreSQL Queries and Commands
    View file
    File renamed without changes.
  9. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,3 +42,9 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql
  10. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 0 additions and 4 deletions.
    4 changes: 0 additions & 4 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,7 +42,3 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U username -h hostname databasename > dump.sql

  11. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -44,5 +44,5 @@ SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_b
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql
    pg_dump -U username -h hostname databasename > dump.sql

  12. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,3 +42,7 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql

  13. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,9 @@ SELECT pg_cancel_backend(procpid);
    -- kill idle query
    SELECT pg_terminate_backend(procpid);

    -- vacuum command
    VACUUM (VERBOSE, ANALYZE);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

  14. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 1 addition and 4 deletions.
    5 changes: 1 addition & 4 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -28,10 +28,7 @@ from pg_database
    order by pg_database_size(datname) desc;

    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
  15. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 9 additions and 21 deletions.
    30 changes: 9 additions & 21 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -23,34 +23,22 @@ select * from pg_stat_activity where current_query not like '<%';
    select * from pg_user;

    -- all tables and their size, with/without indexes
    select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM
    pg_statio_user_tables;
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
    n_live_tup rows_in_table
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
    SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;

    -- how many indexes are in cache
    SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM
    pg_statio_user_indexes;
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;
  16. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,15 @@
    -- show running queries
    -- show running queries (pre 9.2)
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- show running queries (9.2)
    SELECT pid, age(query_start, clock_timestamp()), usename, query
    FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- kill running query
    SELECT pg_cancel_backend(procpid);

  17. @rgreenjr rgreenjr revised this gist Oct 6, 2012. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,9 @@ ORDER BY query_start desc;
    -- kill running query
    SELECT pg_cancel_backend(procpid);

    -- kill idle query
    SELECT pg_terminate_backend(procpid);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

  18. @rgreenjr rgreenjr revised this gist Oct 3, 2012. 1 changed file with 9 additions and 1 deletion.
    10 changes: 9 additions & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,4 +36,12 @@ SELECT
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
    n_live_tup DESC;

    -- how many indexes are in cache
    SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM
    pg_statio_user_indexes;
  19. @rgreenjr rgreenjr revised this gist Oct 3, 2012. 1 changed file with 19 additions and 0 deletions.
    19 changes: 19 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,3 +18,22 @@ select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;


    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM
    pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
    n_live_tup rows_in_table
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
  20. @rgreenjr rgreenjr created this gist Sep 5, 2012.
    20 changes: 20 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    -- show running queries
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- kill running query
    SELECT pg_cancel_backend(procpid);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

    -- all databases and their sizes
    select * from pg_user;

    -- all tables and their size, with/without indexes
    select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;