Explain Analyze will show the plan and also run the query so you can compare the plan to the results the query finds. This will help determine whether a statistical update is needed (that is, whether you need to run 'analyze' or 'vacuum analyze' on the tables in the query), or if the planner is making a bad decision.

Explain verbose is usually not needed, but it shows a lot more detail on how the planner decides which way to construct the query. You should only need to use this if asked on a mailing list.

Seq Scan means PostgreSQL has to do a sequential or full table scan to return the results. This makes sense - we're not returning a small result from the table, we're returning everything so every row needs to be looked at.

The cost=0.00..86765.00 message has two parts.

The first part (0.00) is the estimated time before a result can be output. If you add an order by to a query, this is the value that will change as we will see.

The second value is the cost to retrieve the rows or records we want. This doesn't take limiting results into account.

These costs are a measure of how many disk page fetches it will take to retrieve the data. A disk page fetch is when PostgreSQL looks at the data on disk, so in this case it will have to look at the disk approximately 86,765 times - this sounds like an awful lot but in reality it's not that much - remember it's examining 5 million rows too.

The rows=5000000 value is the estimated number of rows that will be output by this query.

The last value (width=25) is the average width of each row of data in bytes, or the total number of bytes each result contains.

This time, PostgreSQL will use the index (Index Scan) and it's going to use the unique_id_index on the table.

The cost of ordering the results is still 0.

The number of times PostgreSQL will look at the disk is approximately 22,269.57 times - significantly less than the 86,000 previously seen.

This query will return around 966,338 rows and each row contains 25 bytes of data.

Why isn't PostgreSQL returning 999,999 rows, it says there are only going to be 966,338 rows? These numbers are only estimates of the actual results PostgreSQL will find and use. The vacuum analyze command that was run on the table updated internal statistics that PostgreSQL uses to work out values for the planner. When PostgreSQL works out what sort of query plan to use and therefore the displayed figures for explain commands, it chooses a random set of data from this cache to work out whether an index is used, or if the whole table needs to be looked at.