Parameterized Path Hint

Description

Specifies the parameterized path and the conditional predicate pushdown method.

Syntax

1
2

predpush( [@queryblock] src1 src2)
predpush( [@queryblock] src, dest)

Parameter Description

• For details about @queryblock, see Hint Specifying the Query Block Where the Hint Is Located. @queryblock can be omitted, indicating that the hint takes effect in the current query block.
• 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. The following is an example.

CREATE TABLE pt2(a int, b int);
CREATE TABLE pt3(a int, b int);
CREATE TABLE pt4(a int, b int);
CREATE INDEX t4_a_idx on pt4(a);
CREATE INDEX t3_a_idx on pt3(a, b);
CREATE INDEX t2_a_idx on pt2(a);
SET rewrite_rule='predpushforce';
SET enable_fast_query_shipping = off;
SET explain_perf_mode=pretty;
gaussdb=# EXPLAIN (costs off) SELECT /*+PREDPUSH(pt2 st3) */ *
FROM pt2,
    (SELECT /*+ indexscan(pt3) indexscan(pt4)  */sum(pt3.b), pt3.a FROM pt3, pt4 where pt3.a = pt4.a GROUP BY pt3.a) st3
WHERE st3.a = pt2.a;
 id |                       operation
----+-------------------------------------------------------
  1 | ->  Streaming (type: GATHER)
  2 |    ->  Nested Loop (3,4)
  3 |       ->  Seq Scan on pt2
  4 |       ->  HashAggregate
  5 |          ->  Nested Loop (6,7)
  6 |             ->  Index Only Scan using t3_a_idx on pt3
  7 |             ->  Index Only Scan using t4_a_idx on pt4
(7 rows)
 Predicate Information (identified by plan id)
-----------------------------------------------
   6 --Index Only Scan using t3_a_idx on pt3
         Index Cond: (a = pt2.a)
   7 --Index Only Scan using t4_a_idx on pt4
         Index Cond: (a = pt2.a)
(4 rows)

If the predpush hint is not used, pt3 and pt4 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 pt3 and pt4 based on pt2 before they are joined. The result set returned after joining is small, which effectively improves the performance.