Parameterized Path Hint

Function

Specifies the parameterized path and the conditional predicate pushdown method.

Syntax

1
2

predpush(src1 src2)
predpush(src, dest)

Parameter Description

• src, src1, and src2 indicate the set of candidates tables pushed down by predpush.
• dest indicates the specified destination table pushed down by predpush.
• If predpush does not contain commas (,), all tables are candidates table. If predpush contains commas (,), both candidates tables and destination tables are specified.

Use the predpush hint to move the filter expression as close to the data source as possible to optimize the query.

• Before using the predpush hint, ensure that the rewrite_rule GUC parameter contains the PREDPUSH|REDPUSHFORCE|PREDPUSHNORMAL option.

• subquery_block can also be a view or materialized view.

Examples

Use the predpush hint to improve the statement execution efficiency. Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

set rewrite_rule = 'predpushnormal';
explain (costs off) SELECT /*+PREDPUSH(t2, st3)*/ *
FROM t2,
    (SELECT sum(t3.b), t3.a FROM t3, t4 where t3.a = t4.a GROUP BY t3.a) st3
WHERE st3.a = t2.a;
 id |                      operation
----+------------------------------------------------------
  1 | ->  Streaming (type: GATHER)
  2 |    ->  Nested Loop (3,4)
  3 |       ->  Seq Scan on t2
  4 |       ->  GroupAggregate
  5 |          ->  Nested Loop (6,7)
  6 |             ->  Index Only Scan using t4_a_idx on t4
  7 |             ->  Materialize
  8 |                ->  Index Scan using t3_a_idx on t3
(8 rows)

 Predicate Information (identified by plan id)
-----------------------------------------------
   6 --Index Only Scan using t4_a_idx on t4
         Index Cond: (a = t2.a)
   8 --Index Scan using t3_a_idx on t3
         Index Cond: (a = t2.a)
(4 rows)

If the predpush hint is not used, t3 and t4 in the subquery are not processed outside the query block before being joined. As a result, the returned result set is large, causing performance waste.

However, as shown in the preceding plan, after the predpush hint is used, condition filtering is performed on t3 and t4 based on t2 before they are joined. The result set returned after joining is small, which effectively improves the performance.