PgHero.sql

Note: This has been replaced with PgHero.

Postgres insights made easy

Supports PostgreSQL 9.4+

A big thanks to Craig Kerstiens and Heroku for the initial queries 👏

Features

Queries

View all running queries with:

SELECT * FROM pghero_running_queries;

Queries running for longer than five minutes

SELECT * FROM pghero_long_running_queries;

Queries can be killed by pid with:

SELECT pghero_kill(123);

Kill all running queries with:

SELECT pghero_kill_all();

Index Usage

All usage

SELECT * FROM pghero_index_usage;

Missing indexes

SELECT * FROM pghero_missing_indexes;

Unused Indexes

SELECT * FROM pghero_unused_indexes;

Space

Largest tables and indexes

SELECT * FROM pghero_relation_sizes;

Cache Hit Ratio

SELECT pghero_index_hit_rate();

and

SELECT pghero_table_hit_rate();

Both should be above 99%.

Install

Run this command from your shell:

curl https://raw.githubusercontent.com/ankane/pghero.sql/master/install.sql | psql db_name

or copy its contents into a SQL console.

Uninstall

curl https://raw.githubusercontent.com/ankane/pghero.sql/master/uninstall.sql | psql db_name

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help:

• Report bugs
• Fix bugs and submit pull requests
• Write, clarify, or fix documentation
• Suggest or add new features