There is also this excellent post which shows how to break down an EXPLAIN and reason about the performance .
Seems a much more efficient approach than analyzing and diagramming (sometimes) complex explain results.
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.
By the way, if you think this is cool, Hubert (creator of depesz) is a DBA at Instructure, making software for schools (Canvas) and employee training (Bridge). We open-source a ton of stuff (code.instructure.com).
We're hiring leads + senior engineers in Chicago, Salt Lake City, Seattle, and Budapest! If you're interested, feel free to reach out to email@example.com or check out what's available at https://jobs.lever.co/instructure?lever-via=NiHimSaI8r&team=...
But most of those things I mentioned aren't constant. Available resources/system load changes, number of rows in tables change, and # of locks and length of holding on rows/tables change. So what you're left with is a cost that reflects the relative performance of a query compared to the same result set obtained by a different query.
For reference, in Oracle, this SO link  explains how cost is calculated.
Note: this is all my understanding, and someone more knowledgeable might know better.
Actual run time is heavily dependent on distribution of data. There may be characteristics of your data not modelled in statistics used to calculate costs.
If I were running a query that might take 15 minutes, I'd break it down, and put limit clauses in pinch points to figure out which bits of the query are sensitive (e.g. how much run time changes with increments to limits).
But often just looking at the plan will tell me if the database is doing joins in the order I think would be more or less efficient, given the extra context I have on the data distribution.
That is pretty much it. For other database engines' query planners too.
Everything that goes into the cost estimate is at best an educated guess, so the result is never going to map neatly to wall-clock time or any other measure.
Remember: the query planner's job is not to find the best plan possible for a given statement. This would be an impossible task. The planner's job is to try find a plan that is good enough and do so fast as possible.
To cut a short post long:
The cost is an educated guess, based on index statistics where available. Assuming it is similar to the cost calculations SQL Server uses is a mix of expected CPU use, I/O bandwidth use, memory required for the operation to touch disk as little possible, how large a load of locks the process might need to take (depending on the current isolation level settings), and so forth. Because you might have faster/slower CPU cores, more/less of them, faster/slower RAM, more/less cache per core, a lower/higher latency network, a set of drives that are faster/slower for random/bulk IO, etc., than the reference systems this mix was based upon, the cost is not going to exactly map to anything on your machine even if it does on some reference machine(s) somewhere in the development/testing chain.
Those reference machines might not even really exist. In SQL Server's case they are probably machines that sat under some developer's desks in the late 90s, so the balance might be significantly off for most modern use cases. This is part of why MS are playing around with cardinality estimators and other related bits in recent versions: the guesses they used to make don't make as much sense on modern systems, so they are having to be updated to make them more relevant. In some cases the estimates (those applied to table variables for instance) are still a stab in the dark, but they are now a stab that is more likely to be close to correct on modern kit than the stabs it used to take.
This is why index hints are needed. Because of the many variables involved, all the query planner can hope to do to assess each plan against the other possibilities, without a more exhaustive analysis which for many queries might take longer than running the query on the worst plan ever would, is a best guess. If you asking it to do something complex, sometimes you have to guide it towards the better plan.
This is a claim that is extremely difficult for me to believe. Because this statement says that you really can't say that a query in one system with cost of 10 is any more likely to be run faster than a query with a cost of 10^300 (these are made up numbers) in another environment. If there were way to get a mapping with even accuracy within two orders of magnitude (e.g. a query with cost x is likely to run something between 10 and 1000 minutes) on my current system, I would be perfectly happy. Even three orders of magnitude accuracy would likely be helpful.
> ...besides the default constants being chosen relative to the cost of a sequential read as a basis (and Postgres admits these relative values are not scientific or necessarily accurate)
The costs will always be relative to each other as defined by your constants. The whole point of the cost model is to compare plans so that the fastest one can be chosen. If you hyper-tune your constants according to your system (which I feel is probably pretty difficult to do accurately), you could maybe get something within the orders of magnitude you want.
Just the estimates (ie not actually running the query) should give clues as to which table it scans, and the predicates gives hints as to which index would have helped.
Not always easy fix though, sometimes what you're asking for just isn't possible to index in a nice way. Just the other day me and a colleague was trying to optimize a query, and we couldn't get it to use the index simply because the selectivity was too low. The most selective part of the where clause was a non-trivial and highly dynamic "exists" sub-query... We ended up using a few materialized views with manual refresh but still the query took 2-3 seconds (down from 10-15).
FYI, the tool saves the plans on your local storage, so links won't work :)
I would pay good money for an extension that could answer how the query planner makes decisions about a given query.
Someone made a flame graph analyzer for Oracle queries, which seems like an even better way of visualizing explains, and seems like it would be fairly straightforward to translate to postgres: https://externaltable.blogspot.com/2014/05/flame-graphs-for-...
In my opinion isqlw is the gold standard, and it had something like this built right in.
My only nits - it's kind of annoying to have to click a checkbox to make sure my analysis is not public and to keep the window open so I can delete it after I'm done.
This is an indispensable tool for Postgres query plan analysis. Even for those who are experienced at reading them, this can help to sort problems.
It's also a great way to share query plans when asking for help from the Postgres community.
FYI because I wish someone had pointed this out to me when I stumbled across tools like this: For profiling really busy production DBs, I highly recommend https://pganalyze.com/
Not affiliated - just an ecstatic customer.
I rather like the PostgreSQL Explain Vizualizer. 
I can keep multiple plans around, and it has a lot of detail.
For example, five chosen at random:
I think it should de-dupe identical plans at submission time.
I do wish that it was as polished as the database core itself seems to be.
1. The server status window (when it used to work).
2. The visual explain query view.
3. The statistics tab. Though, DBeaver now has a simplified one for tables.