* Visual Explain, originally a [http://sources.redhat.com/rhdb/visualexplain.html RedHat component] that has been kept current and improved by Enterprise DB, comes bundled with the EnterpriseDB Advanced Server package. It can be built to run against other PostgreSQL installations using the source code to their [http://www.enterprisedb.com/products/download.do Developer Studio] package.

* Visual Explain, originally a [http://sources.redhat.com/rhdb/visualexplain.html RedHat component] that has been kept current and improved by Enterprise DB, comes bundled with the EnterpriseDB Advanced Server package. It can be built to run against other PostgreSQL installations using the source code to their [http://www.enterprisedb.com/products/download.do Developer Studio] package.

Revision as of 17:51, 22 November 2012

Figuring out why a statement is taking so long to execute is done with the EXPLAIN command. You can run this two ways; if you use EXPLAIN ANALYZE, it will actually run the statement and let you compare what the planner thought was going to happen with what actually did. Note that if the statement changes data, that will also happen when you run with EXPLAIN ANALYZE; if you just use EXPLAIN the statement doesn't do anything to the database.

Visual Explain, originally a RedHat component that has been kept current and improved by Enterprise DB, comes bundled with the EnterpriseDB Advanced Server package. It can be built to run against other PostgreSQL installations using the source code to their Developer Studio package.

PostgreSQL 9.0 High Performance (2010) is a book with a long discussion of how to use EXPLAIN, read the resulting query plans, and make changes to get different plans.

A common problem that causes the planner to make bad decisions is not keeping Planner Statistics updated. Another is leaving the tuning parameters that let the server know how memory is available at the very small defaults. For example, in the stock configuration, sorts that take more than 1MB are swapped to disk as being too big to process in memory. Tuning Your PostgreSQL Server covers good practices for sizing the memory and other tuning parameters that most impact query planning.

One thing you do when stumped by a plan is to submit the full EXPLAIN ANALYZE output, along with the schema of the involved queries, to the pgsql-performance mailing list. To get a useful reply more quickly, please read SlowQueryQuestions before posting. Note that if you're not running a relatively current version of PostgreSQL, it's quite possible the answer you'll get is that the problem is resolved in a later one. It may save you some time to try at least the most current update to the version you're using (i.e. upgrade to 8.2.6 if that's the current latest rev and you're using 8.2.3) and see if that improves the plan you get.

An advanced technique here is to save your explain plans over time and see how they change as the amount of data in the table grows. This can give you an idea if you're collecting statistics aggressively enough. Putting EXPLAIN results into a table gives a technique for automating that. A simple pl/pgsql example is at generic options for explain.