PostgreSQL Performance Notes

PostgreSQL Performance Notes

Databases are magic from my perspective. They take in queries, find all the information you tasked it to find, and POOF. In the blink of an eye, there it is, all packaged up nice and neat. Or it blows up in your face, but that never happens, right? Let’s take a look at some steps we can take when things are not returned in the blink of and eye. What can we do to improve our database performance.

Queries to use

Let’s use this query to get a quick overview of what tables in your database are getting scanned too often and could benefit from a carefully placed index.

SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname='public' AND pg_relation_size(relname::regclass)>1000
ORDER BY too_much_seq DESC;

That will give us a nice starting point to see if maybe we need to employ some indexes

people12722526Missing Index?81921272253812
addresses11098278Missing Index?17104896133957212297443
phones6065872Missing Index?294912607769311821
events3599326Missing Index?8192359938155
pterodactyl3343980Missing Index?819233508856905
trex3153419Missing Index?8192315346344

Thanks to for the great query

Tools to Analyze

Explain Visualizer

If you have a query, you can use the EXPLAIN directive to understand how Postgres plans a query. This is helpful to identify bottlenecks and areas that are costly to run.

SELECT * FROM people
WHERE age > 25;

Take the resulting JSON from that query and paste it into a New Plan on

From there, you can explore which areas of the query are most costly and suggestions for improvement.

A Quick Database Inspection Tool

If you want to try a quick db inspection tool. Look into