We have migrated to new Google sites!
Post date: Aug 25, 2014 7:27:2 PM
EXPLAIN and EXPLAIN analyze is a valuable tool to identify opportunities to improve query performance.
EXPLAIN => displays the query plan and estimated costs for a query but does not execute the query. EXPLAIN ANALYZE executes the query in addition to displaying the query plan.
EXPLAIN ANALYZE=> will discard any output from the SELECT statement; however, other operations in the statement will be performed (for example, INSERT, UPDATE, DELETE).
To use EXPLAIN ANALYZE on a DML statement without letting the command affect the data, explicitly use EXPLAIN ANALYZE in a transaction
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
The estimated costs of a query plan are cost, rows and width.
Cost 1.0 equals one sequential disk page read. The first estimate is the start-up cost (cost of getting the first row) and the second is the total cost (cost of getting all rows). The total cost assumes that all rows will be retrieved, which may not always be the case (for example, if using a LIMIT statement). Rows may be less than the
actual number of rows processed or scanned by the plan node, reflecting the estimated selectivity of WHERE clause conditions.
The cost of an upper-level node includes the cost of all its child nodes. The topmost node of the plan has the estimated total execution cost for the plan. This is
this number that the query planner seeks to minimize. Rows are the number of rows output by the plan node and width is the total bytes of all the rows output by the plan node.
EXPLAIN ANALYZE runs the statement in addition to displaying the plan with additional information as follows:
1. Total elapsed time (in milliseconds) to run the query
2. Number of workers (segments) involved in a plan node operation
3. Maximum number of rows returned by the segment (and its segment ID) that produced the most rows for an operation
4. The memory used by the operation
5. Time (in milliseconds) it took to retrieve the first row from the segment that produced the most rows, and the total time taken to retrieve all rows from that segment