PostgreSQL's Explain Analyze Made Readable

Seems a much more efficient approach than analyzing and diagramming (sometimes) complex explain results.

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 [1] explains how cost is calculated.

Note: this is all my understanding, and someone more knowledgeable might know better.

[1] https://stackoverflow.com/a/8177490

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.

FYI, the tool saves the plans on your local storage, so links won't work :)

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.

I do wish that it was as polished as the database core itself seems to be.