We have migrated to new Google sites!
Post date: Oct 10, 2013 6:48:26 PM
Query plans are a tree plan of nodes. Each node in the plan represents a single operation, such as table scan, join, aggregation or a sort. Plans should be read from the bottom up as each node feeds rows into the node directly above it. The bottom nodes of a plan are usually table scan operations (sequential, index or bitmap index scans). If the query requires joins, aggregations, or sorts (or other operations on the raw rows) then there will be additional nodes above the scan nodes to perform these operations. The topmost plan nodes are usually the Greenplum Database motion nodes (redistribute, explicit redistribute, broadcast, or gather motions). These are the operations responsible for moving rows between the segment instances during query processing.
The output of EXPLAIN has one line for each node in the plan tree, showing the basic node type plus the following cost estimates that the planner made for the execution of that plan node:
=======================================================================================================================
cost - measured in units of disk page fetches; that is, 1.0 equals one sequential disk page read. The first estimate is the start-up cost (cost of getting to the first
row) and the second is the total cost (cost of getting all rows). Note that the total cost assumes that all rows will be retrieved, which may not always be the case (if
using LIMIT for example).
rows - the total number of rows output by this plan node. This is usually less than the actual number of rows processed or scanned by the plan node, reflecting the
estimated selectivity of any WHERE clause conditions. Ideally the top-level nodes estimate will approximate the number of rows actually returned, updated, or
deleted by the query.
width - total bytes of all the rows output by this plan node.
=======================================================================================================================
It is important to note that 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 planner seeks to minimize. It is also important to realize that the cost only reflects things that the query planner cares about. In particular, the cost does not consider the time spent transmitting result rows to the client.
To illustrate how to read an EXPLAIN query plan, consider the following example for a very simple query:
sachi=> EXPLAIN select * from employees where employee_id=198;
QUERY PLAN
-----------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..3.34 rows=1 width=85)
-> Seq Scan on employees (cost=0.00..3.34 rows=1 width=85)
Filter: employee_id = 198::numeric
(3 rows)
sachi=>
If we read the plan from the bottom up, the query planner starts by doing a sequential scan of the employees table. Notice that the WHERE clause is being applied as a filter condition. This means that the scan operation checks the condition for each row it scans, and outputs only the ones that pass the condition.
The results of the scan operation are passed up to a gather motion operation. In Greenplum Database, a gather motion is when segments send rows up to the master. In this case we have 1 segment instances sending to 1 master instance (1:1). This operation is working on slice1 of the parallel query execution plan.
In Greenplum Database a query plan is divided into slices so that portions of the query plan can be worked on in parallel by the segments. The estimated startup cost for this plan is 00.00 (no cost) and a total cost of 3.34 disk page fetches. The planner is estimating that this query will return one row.