Filter Path
The Filter
step evaluates predicates on a single table. It accepts a set of rows, eliminates some of them (based on the criteria you provide in your query), and returns the rest. For example, the optimizer can filter local data of a join input that will be joined with another re-segmented join input.
The following statement queries the customer_dimension
table and uses the WHERE clause to filter the results only for male customers in Massachusetts and New Hampshire.
EXPLAIN SELECT CD.customer_name, CD.customer_state, AVG(CD.customer_age) AS avg_age, COUNT(*) AS count FROM customer_dimension CDWHERE CD.customer_state in ('MA','NH')
AND CD.customer_gender = 'Male'
GROUP BY CD.customer_state, CD.customer_name;
The query plan output is as follows:
Access Path: +-GROUPBY HASH [Cost: 378, Rows: 544] (PATH ID: 1) | Aggregates: sum_float(CD.customer_age), count(CD.customer_age), count(*) | Group By: CD.customer_state, CD.customer_name | Execute on: Query Initiator | +---> STORAGE ACCESS for CD [Cost: 372, Rows: 544] (PATH ID: 2) | | Projection: public.customer_dimension_DBD_1_rep_vmartdb_design_vmartdb_design_node0001 | | Materialize: CD.customer_state, CD.customer_name, CD.customer_age | |Filter:
(CD.customer_gender = 'Male') | |Filter:
(CD.customer_state = ANY (ARRAY['MA', 'NH'])) | | Execute on: Query Initiator