Postgres has a great ability to show you how it will actually execute a query under the covers. This is known as an execution plan, and it is exposed by the explain command. Understanding this tells you how you can optimize your database with indexes to improve performance.
Every query within Postgres has an execution plan when executed. There are three ways to run explain to expose this to you:
The generic form (only shows what is likely to happen)
The Analyze form (which actually runs the query, and outputs what does happen)
The Verbose form (verbose information)
Most commonly, explain is run on the SELECT
statements. However, you can also use it on the following:
INSERT
UPDATE
DELETE
EXECUTE
DECLARE