How to Interpret a Query's Explain Plan

How do you interpret a query's explain plan?

I shudder whenever I see comments that full tablescans are bad and index access is good. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst and combined with a knowledge of the database structure and the purpose of a query in order to reach any meaningful conclusion.

A full scan is simply the most efficient way of reading a large proportion of the blocks of a data segment (a table or a table (sub)partition), and, while it often can indicate a performance problem, that is only in the context of whether it is an efficient mechanism for achieving the goals of the query. Speaking as a data warehouse and BI guy, my number one warning flag for performance is an index based access method and a nested loop.

So, for the mechanism of how to read an explain plan the Oracle documentation is a good guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

Have a good read through the Performance Tuning Guide also.

Also have a google for "cardinality feedback", a technique in which an explain plan can be used to compare the estimations of cardinality at various stages in a query with the actual cardinalities experienced during the execution. Wolfgang Breitling is the author of the method, I believe.

So, bottom line: understand the access mechanisms. Understand the database. Understand the intention of the query. Avoid rules of thumb.

Understanding EXPLAIN ANALYZE query plan

I am not certain, but my guess is that the “Merge Join” consumed only 5634 rows from the “Sort” node.

PostgreSQL execution works “on demand”, that is, a result row is requested from a lower node whenever the upper node needs it.

And while the “Sort” definitely needs all rows from the “Seq Scan”, the merge join could be finished before all available sorted rows have been read.

That was not your question, but to speed up the query, you need indexes on people (name) and movies (title).

To know if your data are cached, use EXPLAIN (ANALYZE, BUFFERS). Then you see the number of blocks that were found in the cache (hit) and the number of blocks read from the operating system (read). Note, however, that "read" data might come from the file system cache.

How to interpret huge costs in a query plan

There are two issues here. One is the actual behaviour of EXPLAIN, the other is a bug.

The first issue is that in Postgres, EXPLAIN costs are to the maximum extent possible intended to be realistic and be true to the actual, real-world cost and time required by an operation.

This is not the case with EXPLAIN in Redshift.

In Redshift, costs are arbitrary numbers. They have been selected by the developers, I think in an effort to rather crudely control the query planner.

I can see no advantages to this method, and no end of disadvantages, but there it is. (For example, you can't compare costs across queries - even the same basic query which you're only experimenting with to find the most efficient solution).

So, for example, in Redshift scanning a table has a cost of 1 per row.

Sorting a table has a cost of I think it was 1,000,000,000 (one billion), plus 1 per row - so scanning 1b records is considered cheaper than sorting one row, which is nuts. This is why the query planner goes wrong at times.

The second issue is that there is a bug in the costs presented by EXPLAIN with DS_DIST_BOTH. I believe it uses an uninitialized variable, and as a result has a cost which is about a million times more atoms than there are in the Universe.

I did try to tell Support. I tried for a while and then gave up. You have to understand the limiations of Redshift Support - they don't understand Redshift, and they don't really seem to be able to think very much for themselves. I came away from the discussion with the view that someone, at some point, had told them plan costs could become very large numbers, and from that point on it became impossible for them to comprehend that there could be a very large number and it could actually be wrong. This is by far not the only bug I have given up trying to get Support to comprehend.

How to understand SQLite `EXPLAIN QUERY PLAN` result?

The line EXECUTE CORRELATED SCALAR SUBQUERY 1 is just there because you're using a different query syntax.

The actual execution is the same in both cases: SQLite goes through all records of the Queue table, and tries to look up the corresponding record in the LastQueue table.

That the estimated record counts are different is of no concern to you, because you know that the actual number of records is the same.

Joins might be better than subqueries in other databases, but in SQLite, which uses only nested loop joins, the only difference is that a join allows SQLite to choose the outer and the inner table in the join.

In any case, you should write the query in the most simple and maintainable way, and optimize it only if you have measured that you get a noticeable and necessary improvement.

Please note that instead of:

WHERE (SELECT ...) IS NOT NULL

it would be more idiomatic to write:

WHERE EXISTS (SELECT ...)

Understanding the results of Execute Explain Plan in Oracle SQL Developer

The output of EXPLAIN PLAN is a debug output from Oracle's query optimiser. The COST is the final output of the Cost-based optimiser (CBO), the purpose of which is to select which of the many different possible plans should be used to run the query. The CBO calculates a relative Cost for each plan, then picks the plan with the lowest cost.

(Note: in some cases the CBO does not have enough time to evaluate every possible plan; in these cases it just picks the plan with the lowest cost found so far)

In general, one of the biggest contributors to a slow query is the number of rows read to service the query (blocks, to be more precise), so the cost will be based in part on the number of rows the optimiser estimates will need to be read.

For example, lets say you have the following query:

SELECT emp_id FROM employees WHERE months_of_service = 6;

(The months_of_service column has a NOT NULL constraint on it and an ordinary index on it.)

There are two basic plans the optimiser might choose here:

Plan 1: Read all the rows from the "employees" table, for each, check if the predicate is true (months_of_service=6).
Plan 2: Read the index where months_of_service=6 (this results in a set of ROWIDs), then access the table based on the ROWIDs returned.

Let's imagine the "employees" table has 1,000,000 (1 million) rows. Let's further imagine that the values for months_of_service range from 1 to 12 and are fairly evenly distributed for some reason.

The cost of Plan 1, which involves a FULL SCAN, will be the cost of reading all the rows in the employees table, which is approximately equal to 1,000,000; but since Oracle will often be able to read the blocks using multi-block reads, the actual cost will be lower (depending on how your database is set up) - e.g. let's imagine the multi-block read count is 10 - the calculated cost of the full scan will be 1,000,000 / 10; Overal cost = 100,000.

The cost of Plan 2, which involves an INDEX RANGE SCAN and a table lookup by ROWID, will be the cost of scanning the index, plus the cost of accessing the table by ROWID. I won't go into how index range scans are costed but let's imagine the cost of the index range scan is 1 per row; we expect to find a match in 1 out of 12 cases, so the cost of the index scan is 1,000,000 / 12 = 83,333; plus the cost of accessing the table (assume 1 block read per access, we can't use multi-block reads here) = 83,333; Overall cost = 166,666.

As you can see, the cost of Plan 1 (full scan) is LESS than the cost of Plan 2 (index scan + access by rowid) - which means the CBO would choose the FULL scan.

If the assumptions made here by the optimiser are true, then in fact Plan 1 will be preferable and much more efficient than Plan 2 - which disproves the myth that FULL scans are "always bad".

The results would be quite different if the optimiser goal was FIRST_ROWS(n) instead of ALL_ROWS - in which case the optimiser would favour Plan 2 because it will often return the first few rows quicker, at the cost of being less efficient for the entire query.

difference between explain plan and execution plan

explain plan is the statement that is used to display the execution plan.

The two samples you have shown are just formatted differently, that's all.

You did not tell us how exactly you generated those outputs nor which tool you were using.

But if'm not mistaken, one of them is the output of an autotrace inside SQL*Plus the other the output when using of of the procedures of the dbms_xplan package.