My favorite tool for this kind of analysis was pgAdmin3, which had a very nice diagramming output for EXPLAIN ANALYZE which you can see here [0]. Hovering over the various images in the diagram would display their time, rows, and which statement caused them.
There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance [1].
Thanks for the tip. Looks interesting, but still on the "analyze and do something" side vs monitoring then making suggestions.
In other words, this could be another input for the kind of tool I'm thinking of.
Kind of surprised this doesn't exist, as it seems doable. Given a combination of query optimizers, slow query monitoring, and common tuning techniques, even a rudimentary, heuristics-based recommendation tool could provide significantly more value then manually analyzing explain and log file output.
Looks like there is a service similar to what you asked for, but it only applies to MySQL, PerconaDB, and MariaDB right now [0]. Maybe as PostgreSQL grows in popularity more services will target it. Or go build your own, it sounds like a viable business.
Agreed. Also I like that it's so quick (at least for a regular EXPLAIN) - I would very often hit F7 to see the query plan visualization, even for just a split second, before pressing F5 to run the query. If I see the table-scan icon appear somewhere when I'm not expecting it to, it's a big hint I've done something dumb.
pgAdmin4 has become 100x better since the first preview was released - unfortunately it's still a web client and struggles with "large" data sets (I wouldn't call a million rows large, pgAdmin3 would return it in a snap, you could copy it to your clipboard, etc., pgAdmin4 wheezes under the weight of its webshit infrastructure)
It may be worth noting that while pgAdmin3 is officially deprecated, the binaries can still be downloaded and used. Latest version is 1.22.2 from November of 2016 [0].
There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance [1].
[0] http://www.postgresonline.com/images/journal/explain_plan_5....
[1] http://www.postgresonline.com/journal/archives/27-Reading-Pg...