Using EXPLAIN ANALYZE to measure actual execution

Suppose you want to inspect the actual execution of the following statement:

Place EXPLAIN ANALYZE before the statement:

Type the command into the SQL editor and run it. You should see output similar to the following:

Your timing and buffer values will probably differ from the ones shown here. They can change between executions depending on your computer, other activity in the database, and whether the required data is already cached.

The estimated cost, row count, and plan itself should usually be more stable, although they can also change if the data or database statistics change.

The plan still begins with the estimates displayed by a plain EXPLAIN:

Because the statement was executed, the node now contains an additional group of values:

Let's examine each of these values.

The two numbers show how long the node took to start producing rows and how long it took to finish. Unlike PostgreSQL's estimated costs, actual times are measured in milliseconds.

In this example:

• 0.019 milliseconds is how long the node took to produce its first row.

• 0.910 milliseconds is how long the node took to finish producing all its rows.

The second number already includes the first. So, the Seq Scan node took approximately 0.910 milliseconds to complete, not 0.929 milliseconds.

The plain EXPLAIN output contained the following estimate:

The actual section reports:

5000 is the number of rows the planner estimated this node would produce.

5000.00 is the average number of rows the node actually produced each time it was executed.

You may notice that the actual row count is displayed as 5000.00 rather than 5000. PostgreSQL reports this value as an average per execution of the node, so it can contain decimal places.

In this example, loops=1, so the node was executed only once and actually produced 5,000 rows.

The estimate and actual result are equal:

This tells us that the planner estimated the number of rows accurately.

Comparing estimated and actual row counts is one of the most useful reasons to run EXPLAIN ANALYZE. Large differences, especially differences of several orders of magnitude, may indicate that PostgreSQL doesn't have enough information to estimate the result accurately.

An inaccurate estimate doesn't automatically mean that PostgreSQL selected a poor plan, but it's an important warning sign because row estimates influence decisions about scans, joins, sorting, and other operations.

The loops value tells you how many times the plan node was executed.

In this example, the Seq Scan node was executed once.

Some plan nodes can be executed multiple times. When that happens, the actual time and rows values shown for the node are averages for one execution of that node.

For example:

This means the node was executed 10 times, and each execution produced an average of 2 rows.

The output may also contain information such as:

PostgreSQL stores table and index data in units called blocks. When PostgreSQL accesses this data, the required blocks are held in an area of memory called shared buffers.

shared hit=46 means that 46 block accesses found the required blocks already in shared buffers. PostgreSQL didn't need to read those blocks into shared buffers again.

You may also see a read value:

read=26 means that PostgreSQL had to read 26 blocks into shared buffers. This doesn't necessarily mean that the blocks were read from physical storage, because the operating system may already have had the data in memory.

For now, the important distinction is:

• hit means the required data was already in PostgreSQL's shared buffers.

• read means the required data had to be read into shared buffers.

The output includes the time PostgreSQL spent creating the execution plan:

Planning time includes the time required to create and optimize the plan from the rewritten statement. It doesn't include the earlier parsing and rewriting stages.

For small and simple statements, planning can occasionally take longer than execution. This isn't necessarily a problem. It may simply mean that executing the selected plan required very little work.

The output also reports:

Execution time is the total time PostgreSQL spent running the statement through the executor. It includes executor startup and shutdown work in addition to the work performed by the plan nodes.

It doesn't include the time spent:

• Parsing the statement

• Rewriting the statement

• Creating and optimizing the execution plan

• Sending the resulting rows to the client

Although EXPLAIN ANALYZE executes the SELECT statement, it discards the result instead of sending the 5,000 product rows to the client.

Therefore, Execution Time isn't the complete time an application would experience. It measures the time PostgreSQL spent executing the statement, but it doesn't include the time required to send the resulting rows to the client. EXPLAIN ANALYZE also adds some measurement overhead of its own.

As I explained earlier, EXPLAIN ANALYZE actually executes the SQL statement, unlike plain EXPLAIN.

For a SELECT statement, PostgreSQL executes the query but discards the rows instead of returning them to the client. However, statements such as INSERT, UPDATE, DELETE, and MERGE will make their normal changes to the database.

For example, the following statement would actually update the matching row:

When you want to analyze a data-changing statement without keeping its changes, you can run it inside a transaction and roll the transaction back:

EXPLAIN ANALYZE still executes the UPDATE, allowing PostgreSQL to collect the actual execution statistics, but ROLLBACK undoes the transactional changes afterward.

ANALYZE can also be supplied using the parenthesized option syntax:

This is equivalent to:

The parenthesized form is useful when you want to combine ANALYZE with other options: