Product Documentation

Appearance

All platforms

Plan Hinting: ROWS Hint ​

Overview ​

The ROWS hint allows you to adjust the estimated row count at the SCAN, JOIN, or SUBQUERY level. This adjustment affects the cost estimation used by the planner and can be applied with different correction methods.

Correction Methods: ​

  • +: Adds to the existing estimate.
  • -: Subtracts from the existing estimate.
  • #: Overrides the estimate.
  • *: Multiplies the estimate.
sql
ROWS( <table_name> <correction_method> <row_correction> )

Examples: ​

Override row estimate at SCAN level ​

Assume we have a table t with 5 rows :

sql
yellowbrick=# EXPLAIN(CONFIDENCE) SELECT * FROM t;
                               QUERY PLAN
-------------------------------------------------------------------------
 id   rows_planned   rows_max_planned   confidence   workers   node
  5              5                  5         High       all   SELECT
  2              5                  5         High       all   SCAN t

If one wants to let the planner think the table contains 42k rows instead:

sql
yellowbrick=# EXPLAIN(CONFIDENCE) SETTING( ROWS( t # 42000) ) SELECT * FROM t;
                               QUERY PLAN
-------------------------------------------------------------------------
 id   rows_planned   rows_max_planned   confidence   workers   node
  5          42000              42000       Hinted       all   SELECT
  2          42000              42000       Hinted       all   SCAN t

The estimated row count for the table t was changed 42000.

Add to a JOIN row output estimate ​

Let's now join table t with another table t2:

sql
yellowbrick=# EXPLAIN(CONFIDENCE) SELECT * FROM t INNER JOIN t2 USING(i);
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 id   rows_planned   rows_max_planned   confidence   workers   node
 13              3                 15          Low       all   SELECT
  1              3                 15          Low       all   INNER HASH JOIN ON (t2.i = t.i)
  4              5                  5         High       all   |-SCAN t
                                                               |   t.i = bloom(7) AND scan_constraints: min_max(t.i)
  7              3                  3         High       all   |-BUILD
 10              3                  3         High       all     SCAN t2

We can use SETTING to add 100 rows to the join output:

sql
yellowbrick=# EXPLAIN(CONFIDENCE) SETTING (ROWS(t, t2 + 100)) SELECT * FROM t INNER JOIN t2 USING(i);
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 id   rows_planned   rows_max_planned   confidence   workers   node
 13            103                103       Hinted       all   SELECT
  1            103                103       Hinted       all   INNER HASH JOIN ON (t2.i = t.i)
  4              5                  5         High       all   |-SCAN t
                                                               |   t.i = bloom(7) AND scan_constraints: min_max(t.i)
  7              3                  3         High       all   |-BUILD
 10              3                  3         High       all     SCAN t2