EXPLAIN-Generated Query Plans
EXPLAIN returns the optimizer's query plan for executing a specified query. For example:
QUERY PLAN DESCRIPTION: ------------------------------ EXPLAIN SELECT customer_name, customer_state FROM customer_dimension WHERE customer_state IN ('MA','NH') AND customer_gender='Male' ORDER BY customer_name LIMIT 10; Access Path: +-SELECT LIMIT 10 [Cost: 365, Rows: 10] (PATH ID: 0) | Output Only: 10 tuples | Execute on: Query Initiator | +---> SORT [TOPK] [Cost: 365, Rows: 544] (PATH ID: 1) | | Order: customer_dimension.customer_name ASC | | Output Only: 10 tuples | | Execute on: Query Initiator | | +---> STORAGE ACCESS for customer_dimension [Cost: 326, Rows: 544] (PATH ID: 2) | | | Projection: public.customer_dimension_DBD_1_rep_VMartDesign_node0001 | | | Materialize: customer_dimension.customer_state, customer_dimension.customer_name | | | Filter: (customer_dimension.customer_gender = 'Male') | | | Filter: (customer_dimension.customer_state = ANY (ARRAY['MA', 'NH'])) | | | Execute on: Query Initiator
You can use EXPLAIN
to evaluate choices that the optimizer makes with respect to a given query. If you think query performance is less than optimal, run it through the Database Designer. For more information, see Incremental Design and Reducing Run-time of Queries.