Skip to content

Instantly share code, notes, and snippets.

@formigarafa

formigarafa/postgres_queries_and_commands.sql

Forked from biinari/postgres_queries_and_commands.sql
Last active October 21, 2020 09:00
Download ZIP

Revisions

  1. formigarafa revised this gist Oct 21, 2020. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions rails-shortcuts.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,12 @@

    ApplicationRecord.connection.execute("").to_a

    # show running queries (>= 9.2)
    ApplicationRecord.connection.execute("SELECT pid, age(clock_timestamp(), query_start) AS age, usename, state, query, age(clock_timestamp(), xact_start) AS xact_age FROM pg_stat_activity WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY age DESC;").to_a

    # kill running query
    ApplicationRecord.connection.execute("SELECT pg_cancel_backend(procpid);").to_a

    # kill idle query
    ApplicationRecord.connection.execute("SELECT pg_terminate_backend(procpid);").to_a

  2. @biinari biinari revised this gist Aug 17, 2017. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -46,7 +46,12 @@ FROM pg_stat_user_tables
    ORDER BY n_live_tup 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,
    CASE WHEN sum(heap_blks_hit) != 0
    THEN (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit)
    ELSE 0
    END AS ratio
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
  3. @biinari biinari revised this gist Aug 17, 2017. 1 changed file with 14 additions and 17 deletions.
    31 changes: 14 additions & 17 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -14,23 +14,20 @@ SELECT pg_cancel_backend(procpid);
    -- kill idle query
    SELECT pg_terminate_backend(procpid);

    -- show running queries
    SELECT * FROM pg_stat_activity WHERE current_query NOT LIKE '<%';

    -- all database users
    SELECT * FROM pg_user;
    SELECT usename,usecreatedb,usesuper,usecatupd,userepl,valuntil,useconfig FROM pg_user;

    -- all databases and their size on disk
    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
    END AS Size
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
    END AS Size
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    ELSE NULL
    END;

    -- all tables and their size on disk
    @@ -53,23 +50,23 @@ SELECT sum(heap_blks_read) AS heap_read, sum(heap_blks_hit) AS heap_hit, (sum(h
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT relname,
    SELECT relname,
    CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan)
    THEN 100.0 * idx_scan / (seq_scan + idx_scan)
    ELSE 0
    END AS percent_of_times_index_used,
    n_live_tup AS rows_in_table
    FROM pg_stat_user_tables
    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;

    -- show locks
    SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
    FROM pg_locks l, pg_stat_all_tables t
    WHERE l.relation=t.relid
    SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
    FROM pg_locks l, pg_stat_all_tables t
    WHERE l.relation=t.relid
    ORDER BY relation ASC;

    -- Dump database on remote host to file
  4. @biinari biinari revised this gist Aug 17, 2017. 1 changed file with 11 additions and 11 deletions.
    22 changes: 11 additions & 11 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,31 +1,31 @@
    -- show running queries (>= 9.2)
    SELECT pid,
    age(clock_timestamp(), query_start) as age,
    age(clock_timestamp(), query_start) AS age,
    usename, state, query, waiting,
    age(clock_timestamp(), xact_start) as xact_age
    age(clock_timestamp(), xact_start) AS xact_age
    FROM pg_stat_activity
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY age desc;
    ORDER BY age 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 '<%';
    -- show running queries
    SELECT * FROM pg_stat_activity WHERE current_query NOT LIKE '<%';

    -- all database users
    select * from pg_user;
    SELECT * FROM pg_user;

    -- all databases and their size on disk
    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
    END AS SIZE
    END AS Size
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    @@ -39,7 +39,7 @@ SELECT nspname || '.' || relname AS "relation",
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND C.relkind != 'i'
    AND nspname !~ '^pg_toast'
    ORDER BY pg_total_relation_size(C.oid) DESC;

    @@ -49,7 +49,7 @@ FROM pg_stat_user_tables
    ORDER BY n_live_tup 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)
    @@ -63,7 +63,7 @@ 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
    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;

    -- show locks
    @@ -76,4 +76,4 @@ ORDER BY relation ASC;
    $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql
    $ psql -d newdb -f dump.sql
  5. @biinari biinari revised this gist Aug 17, 2017. 1 changed file with 28 additions and 6 deletions.
    34 changes: 28 additions & 6 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -20,11 +20,28 @@ select * from pg_stat_activity where current_query not like '<%';
    -- all database users
    select * from pg_user;

    -- all databases and their size
    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    where datname != 'rdsadmin'
    order by pg_database_size(datname) desc;
    -- all databases and their size on disk
    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE 'No Access'
    END AS SIZE
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    END;

    -- all tables and their size on disk
    SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    ORDER BY pg_total_relation_size(C.oid) DESC;

    -- list tables with row count
    SELECT schemaname,relname,n_live_tup
    @@ -36,7 +53,12 @@ SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(h
    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
    SELECT relname,
    CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan)
    ELSE 0
    END AS percent_of_times_index_used,
    n_live_tup AS rows_in_table
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;

  6. @biinari biinari revised this gist Aug 17, 2017. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -17,12 +17,13 @@ SELECT pg_terminate_backend(procpid);
    -- 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 size
    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    where datname != 'rdsadmin'
    order by pg_database_size(datname) desc;

    -- list tables with row count
  7. @biinari biinari revised this gist Sep 15, 2016. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -25,6 +25,11 @@ select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    -- list tables with row count
    SELECT schemaname,relname,n_live_tup
    FROM pg_stat_user_tables
    ORDER BY n_live_tup 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;
  8. @biinari biinari revised this gist Sep 15, 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
    @@ -38,6 +38,12 @@ ORDER BY n_live_tup DESC;
    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;

    -- show locks
    SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted
    FROM pg_locks l, pg_stat_all_tables t
    WHERE l.relation=t.relid
    ORDER BY relation ASC;

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

  9. @biinari biinari revised this gist Sep 15, 2016. 1 changed file with 8 additions and 4 deletions.
    12 changes: 8 additions & 4 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,12 @@
    -- 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;
    SELECT pid,
    age(clock_timestamp(), query_start) as age,
    usename, state, query, waiting,
    age(clock_timestamp(), xact_start) as xact_age
    FROM pg_stat_activity
    WHERE state != 'idle'
    AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY age desc;

    -- kill running query
    SELECT pg_cancel_backend(procpid);
  10. @biinari biinari revised this gist Sep 15, 2016. 1 changed file with 0 additions and 3 deletions.
    3 changes: 0 additions & 3 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -10,9 +10,6 @@ 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 '<%';

  11. @biinari biinari revised this gist Sep 15, 2016. 1 changed file with 1 addition and 7 deletions.
    8 changes: 1 addition & 7 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,4 @@
    -- 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)
    -- 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%'
  12. @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.
  13. @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.
  14. @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
  15. @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

  16. @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

  17. @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

  18. @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 '<%';

  19. @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)
  20. @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;
  21. @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);

  22. @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 '<%';

  23. @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;
  24. @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;
  25. @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;