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

relname too_much_seq case rel_size seq_scan idx_scan
people 12722526 Missing Index? 8192 12722538 12
addresses 11098278 Missing Index? 17104896 13395721 2297443
phones 6065872 Missing Index? 294912 6077693 11821
events 3599326 Missing Index? 8192 3599381 55
pterodactyl 3343980 Missing Index? 8192 3350885 6905
trex 3153419 Missing Index? 8192 3153463 44

Thanks to https://github.com/miguelvps for the great query


Tools to Analyize 

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.

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT * FROM people
WHERE age > 25;

Take the resulting JSON from that query and paste it into a New Plan on http://tatiyants.com/pev/#/plans

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 https://github.com/ankane/pghero.