Skip to content

Instantly share code, notes, and snippets.

@jagj77

jagj77/varios_pg.sql

Created February 19, 2018 15:47
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jagj77/81893818d03ce79631e17412f10c6ee7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jagj77/81893818d03ce79631e17412f10c6ee7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jagj77 created this gist Feb 19, 2018.
    20 changes: 20 additions & 0 deletions varios_pg.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@

    -- Querys
    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;

    -- 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;

    -- index used
    SELECT relname, 100.0 * 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;