Predicate Pushing

 

When processing most queries with a non-recursive common table expressions or views, local predicates specified on the main query are pushed down so fewer records need to be materialized. Pushing local predicates from the main query in to the defined recursive part of the query (through the Union ALL), however, may considerably alter the process of recursion itself. So as a general rule, the Union All specified in a recursive query is currently a predicate fence and predicates are not pushed down or up, through this fence.

The following is an example of how pushing a predicate in to the recursion limits the recursive results and alter the intent of the query. If the intent of the query is to find all destinations accessible from 'Chicago' but do not include the final destinations of 'Dallas', pushing the "arrival<>'Dallas'" predicate in to the recursive query alters the output of the intended results, preventing the output of final destinations that are not 'Dallas' but where 'Dallas' was an intermediate stop.

WITH destinations (departure, arrival, connects, cost ) AS
(
   SELECT f.departure,f.arrival, 0, ticket 
		FROM flights f 
		WHERE f.departure='Chicago' 
  UNION ALL
    SELECT
			r.departure, b.arrival, r.connects + 1 ,
			r.cost + b.ticket 
			FROM  destinations r, flights b 
			WHERE r.arrival=b.departure ) 

SELECT departure, arrival, connects, cost 
		FROM destinations 
		WHERE arrival != 'Dallas'

Conversely, the following is an example where a local predicate applied to all the recursive results is a good predicate to put in the body of the recursive definition because it may greatly decrease the amount of rows materialized from the RCTE/View. The better query request here is to specify the r.connects <=3 local predicate with in the RCTE definition, in the iterative fullselect.

WITH destinations (departure, arrival, connects, cost ) AS
(
   SELECT f.departure,f.arrival, 0, ticket 
		FROM flights f 
		WHERE f.departure='Chicago' OR
      f.departure='New York'
  UNION ALL
    SELECT
			r.departure, b.arrival, r.connects + 1 ,
			r.cost + b.ticket 
			FROM  destinations r, flights b 
			WHERE r.arrival=b.departure ) 

SELECT departure, arrival, connects, cost 
		FROM destinations  
		WHERE r.connects<=3 

Placement of local predicates is key in recursive queries as they can incorrectly alter the recursive results if pushed in to a recursive definition or can cause unnecessary rows to be materialized and then rejected when a local predicate may legitimately help limit the recursion.

 

Parent topic:

Recursive query optimization