Skip to content

Instantly share code, notes, and snippets.

@aks

aks/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Last active April 23, 2018 16:01
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save aks/5faf2bebeea4fe78a61b7dee76d13526 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save aks/5faf2bebeea4fe78a61b7dee76d13526 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. aks revised this gist Apr 23, 2018. 1 changed file with 17 additions and 1 deletion.
    18 changes: 17 additions & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -89,4 +89,20 @@ SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) AS locked
    WHERE NOT blockedl.granted
    AND blockinga.datname = current_database()
    );
    -- select * from lock_monitor;
    -- select * from lock_monitor;

    -- select open locks
    SELECT a.datname,
    c.relname,
    l.transactionid,
    l.mode,
    l.GRANTED,
    a.usename,
    a.query,
    a.query_start,
    age(now(), a.query_start) AS "age",
    a.pid
    FROM pg_stat_activity a
    JOIN pg_locks l ON l.pid = a.pid
    JOIN pg_class c ON c.oid = l.relation
    ORDER BY a.query_start;
  2. aks revised this gist Apr 12, 2018. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,8 @@
    -- 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;
    -- 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
  3. aks revised this gist Apr 12, 2018. 1 changed file with 26 additions and 0 deletions.
    26 changes: 26 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -64,3 +64,29 @@ SELECT relname as "Table"
    WHERE relname NOT LIKE 'pg%'
    AND relkind = 'r'
    ORDER BY 2 DESC, 1;

    -- create a view of locks
    CREATE VIEW lock_monitor AS(
    SELECT COALESCE(blockingl.relation::regclass::text,blockingl.locktype) AS locked_item
    , now() - blockeda.query_start AS waiting_duration
    , blockeda.pid AS blocked_pid
    , blockeda.query AS blocked_query
    , blockedl.mode AS blocked_mode
    , blockinga.pid AS blocking_pid
    , blockinga.query AS blocking_query
    , blockingl.mode AS blocking_mode
    FROM pg_catalog.pg_locks blockedl
    JOIN pg_stat_activity blockeda
    ON blockedl.pid = blockeda.pid
    JOIN pg_catalog.pg_locks blockingl
    ON ((( blockingl.transactionid = blockedl.transactionid)
    OR ( blockingl.relation = blockedl.relation
    AND blockingl.locktype = blockedl.locktype))
    AND blockedl.pid != blockingl.pid)
    JOIN pg_stat_activity blockinga
    ON blockingl.pid = blockinga.pid
    AND blockinga.datid = blockeda.datid
    WHERE NOT blockedl.granted
    AND blockinga.datname = current_database()
    );
    -- select * from lock_monitor;
  4. aks revised this gist Mar 17, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -58,7 +58,7 @@ $ pg_dump -U username -h hostname databasename > dump.sql
    $ psql -d newdb -f dump.sql

    -- return row sizes of tables
    procore=> SELECT relname as "Table"
    SELECT relname as "Table"
    , to_char(reltuples::bigint, '9,999,999,999,999') AS "~Rows"
    FROM pg_class
    WHERE relname NOT LIKE 'pg%'
  5. aks revised this gist Mar 17, 2018. 1 changed file with 6 additions and 5 deletions.
    11 changes: 6 additions & 5 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -58,8 +58,9 @@ $ pg_dump -U username -h hostname databasename > dump.sql
    $ psql -d newdb -f dump.sql

    -- return row sizes of tables
    SELECT relname
    , round(relpages * 8192::bigint/reltuples, 2)
    FROM pg_class
    WHERE reltuples <> 0
    AND relname NOT LIKE 'pg%';
    procore=> SELECT relname as "Table"
    , to_char(reltuples::bigint, '9,999,999,999,999') AS "~Rows"
    FROM pg_class
    WHERE relname NOT LIKE 'pg%'
    AND relkind = 'r'
    ORDER BY 2 DESC, 1;
  6. aks revised this gist Mar 17, 2018. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -56,3 +56,10 @@ $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql

    -- return row sizes of tables
    SELECT relname
    , round(relpages * 8192::bigint/reltuples, 2)
    FROM pg_class
    WHERE reltuples <> 0
    AND relname NOT LIKE 'pg%';
  7. aks revised this gist Feb 26, 2018. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -30,6 +30,14 @@ select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    -- Show size of one table and it's indexes
    select
    pg_size_pretty(pg_table_size(oid)) table_size
    ,pg_size_pretty(pg_indexes_size(oid)) indexes_size
    ,pg_size_pretty(pg_total_relation_size(oid))
    from pg_class
    where relname = 'TABLENAME';

    -- 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;
    @@ -48,11 +56,3 @@ $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql

    -- Show size of a table and it's indexes
    select
    pg_size_pretty(pg_table_size(oid)) table_size
    ,pg_size_pretty(pg_indexes_size(oid)) indexes_size
    ,pg_size_pretty(pg_total_relation_size(oid))
    from pg_class
    where relname = 'TABLENAME';
  8. aks revised this gist Feb 26, 2018. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -48,3 +48,11 @@ $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql

    -- Show size of a table and it's indexes
    select
    pg_size_pretty(pg_table_size(oid)) table_size
    ,pg_size_pretty(pg_indexes_size(oid)) indexes_size
    ,pg_size_pretty(pg_total_relation_size(oid))
    from pg_class
    where relname = 'TABLENAME';
  9. @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.
  10. @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.
  11. @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
  12. @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

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

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

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

  16. @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)
  17. @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;
  18. @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);

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

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