Description
Jira Link: DB-17250
Description
It is preferable to control the use of batched nested loop join with the single GUC 'yb_enable_batchednl'. Currently, this GUC and 'yb_prefer_bnl' interact in confusing ways, for example:
yugabyte=# CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE
yugabyte=# CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE
yugabyte=#
yugabyte=# INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT 0 10000
yugabyte=# INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
INSERT 0 1000
yugabyte=#
yugabyte=# CREATE INDEX t1_val ON t1 (val);
CREATE INDEX
yugabyte=# CREATE INDEX t2_val ON t2 (val);
CREATE INDEX
yugabyte=#
yugabyte=# SET pg_hint_plan.debug_print TO on;
SET
yugabyte=# \set SHOW_CONTEXT always
yugabyte=# SET client_min_messages TO log;
SET
yugabyte=# set yb_enable_optimizer_statistics to on;
SET
yugabyte=# set yb_enable_base_scans_cost_model to on;
SET
yugabyte=# show yb_enable_batchednl;
yb_enable_batchednl
on
(1 row)
yugabyte=# show yb_prefer_bnl;
yb_prefer_bnl
on
(1 row)
-- Both GUCs are ON. We get BNL.
yugabyte=# EXPLAIN (COSTS false) SELECT t1.id FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN
YB Batched Nested Loop Join
Join Filter: (t1.val = t2.val)
-> Index Only Scan using t2_val on t2
-> Index Scan using t1_val on t1
Index Cond: (val = ANY (ARRAY[t2.val, $1, $2, ..., $1023]))
(5 rows)
-- Disable BNL. We still get BNL.
yugabyte=# set yb_enable_batchednl to off;
SET
yugabyte=# EXPLAIN (COSTS false) SELECT t1.id FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN
YB Batched Nested Loop Join
Join Filter: (t1.val = t2.val)
-> Index Only Scan using t2_val on t2
-> Index Scan using t1_val on t1
Index Cond: (val = ANY (ARRAY[t2.val, $1, $2, ..., $1023]))
(5 rows)
-- Turn off yb_prefer_bnl. We now get merge join instead of BNL.
yugabyte=# set yb_prefer_bnl to off;
SET
yugabyte=# EXPLAIN (COSTS false) SELECT t1.id FROM t1, t2 WHERE t1.val = t2.val;
QUERY PLAN
Merge Join
Merge Cond: (t1.val = t2.val)
-> Sort
Sort Key: t1.val
-> Seq Scan on t1
-> Sort
Sort Key: t2.val
-> Index Only Scan using t2_val on t2
(8 rows)
-- Reset both GUCs to ON.
yugabyte=# reset yb_enable_batchednl;
RESET
yugabyte=# reset yb_prefer_bnl;
RESET
-- Try hint that should prevent BNL. The hint is used but we still get BNL. This is because the hint
-- (incorrectly) only sets yb_enable_batchednl to OFF but fails to also set yb_prefer_bnl to OFF.
yugabyte=# /*+ NoYbBatchedNL(t1 t2) */ EXPLAIN (COSTS false) SELECT t1.id FROM t1, t2 WHERE t1.val = t2.val;
LOG: pg_hint_plan:
used hint:
NoYbBatchedNL(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
YB Batched Nested Loop Join
Join Filter: (t1.val = t2.val)
-> Index Only Scan using t2_val on t2
-> Index Scan using t1_val on t1
Index Cond: (val = ANY (ARRAY[t2.val, $1, $2, ..., $1023]))
(5 rows)
-- Set yb_prefer_bnl to OFF.
yugabyte=# set yb_prefer_bnl to off;
SET
-- Try the hint again. Now it works.
yugabyte=# /*+ NoYbBatchedNL(t1 t2) */ EXPLAIN (COSTS false) SELECT t1.id FROM t1, t2 WHERE t1.val = t2.val;
LOG: pg_hint_plan:
used hint:
NoYbBatchedNL(t1 t2)
not used hint:
duplication hint:
error hint:
QUERY PLAN
Merge Join
Merge Cond: (t1.val = t2.val)
-> Sort
Sort Key: t1.val
-> Seq Scan on t1
-> Sort
Sort Key: t2.val
-> Index Only Scan using t2_val on t2
(8 rows)
Issue Type
kind/bug
Warning: Please confirm that this issue does not contain any sensitive information
- I confirm this issue does not contain any sensitive information.