pg_hint_plan for Batched Nested Loop Join in YugabyteDB
Published: 2024-01-01 15:50:35

In my [previous post](https://dev.to/yugabyte/could-batched-nested-loop-improve-postgresql-like-it-does-for-yugabytedb-4bjb) I mentioned that the Batched Nested Loop feature has been improved and is now enabled by default. However, if you previously used this feature during its preview phase and utilized optimizer hints, there is an important change that you should be aware of. In short, it was an alternative to Nested Loop in preview, but now it is a full-fledged join method completing the existing ones: Nested Loop, Merge Join, and Hash Join.

---

**TL;DR:** If you used the `NestLoop` hint in the previous version to enforce a Nested loop and set `yb_bnl_batch_size` higher than 1 to batch it, you need to replace the hint with `YBbatchedNL`. This is because `NestLoop` hint will now enforce only the non-batched one.

---

I've started a cluster with the Northwind demo and two additional indexes to join `customers` to `orders`:
```sql
yugabyted start
yugabyted demo connect
\d

create index customers_city     on customers(city, customer_id);
create index orders_customer_id on orders(customer_id);
```

Here is the execution plan in YugabyteDB 2.21 with all defaults and no hints:
```sql
yb_demo_northwind=# explain (analyze, costs off)
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Sort (actual time=10.076..10.077 rows=46 loops=1)
   Sort Key: customers.customer_id
   Sort Method: quicksort  Memory: 37kB
   ->  Hash Join (actual time=9.438..10.027 rows=46 loops=1)
         Hash Cond: (orders.customer_id = customers.customer_id)
         ->  Seq Scan on orders (actual time=2.336..2.734 rows=830 loops=1)
         ->  Hash (actual time=7.053..7.053 rows=6 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Index Scan using customers_city on customers (actual time=7.018..7.029 rows=6 loops=1)
                     Index Cond: ((city)::text = 'London'::text)
 Planning Time: 0.248 ms
 Execution Time: 10.169 ms
 Peak Memory Usage: 315 kB
(13 rows)
```

In the previous version, to enable a batched nested loop you used the `NestedLoop` hint for the join method and set `yb_bnl_batch_size` to enabled Batched Nested Loop.

If you re-use the same hint in 2.21 you will see the non-batched nested loop:
```sql
yb_demo_northwind=# explain (analyze, costs off)
 /*+ NestLoop(customers orders) Set(yb_bnl_batch_size 1024) */
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;

                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Nested Loop (actual time=2.692..6.457 rows=46 loops=1)
   ->  Index Scan using customers_city on customers (actual time=1.440..1.449 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Index Scan using orders_customer_id on orders (actual time=0.801..0.808 rows=8 loops=6)
         Index Cond: (customer_id = customers.customer_id)
 Planning Time: 0.215 ms
 Execution Time: 6.526 ms
 Peak Memory Usage: 56 kB
(8 rows)
;
```
This does the same a PostgreSQL `Nested Loop` with one loop for each outer row (`rows=6` / `loops=6` )

To force a Batched Nested Loop you don't need to set the batch size, but use the new hint `YBbatchedNL`:
```sql

explain (analyze, costs off)
 /*+ YBbatchedNL(customers orders) */
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;
;

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=3.944..3.958 rows=46 loops=1)
   Join Filter: (customers.customer_id = orders.customer_id)
   Sort Keys: customers.customer_id
   ->  Index Scan using customers_city on customers (actual time=2.620..2.653 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Index Scan using orders_customer_id on orders (actual time=1.097..1.136 rows=46 loops=1)
         Index Cond: (customer_id = ANY (ARRAY[customers.customer_id, $1, $2, ..., $1023]))
 Planning Time: 0.654 ms
 Execution Time: 4.289 ms
 Peak Memory Usage: 593 kB
(10 rows)
```
Now, all rows were fetched in one loop (`loops=1`)

If you didn't use a hint but session parameters to disable other methods, you can do the same. However, now, disabling Nested Loop with `enable_nestloop=off` still allows the Batched Nested Loop:

```sql
yb_demo_northwind=# set enable_hashjoin=off;
SET
yb_demo_northwind=# set enable_nestloop=off;
SET

yb_demo_northwind=# explain (analyze, costs off)
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=2.908..2.926 rows=46 loops=1)
   Join Filter: (customers.customer_id = orders.customer_id)
   Sort Keys: customers.customer_id
   ->  Index Scan using customers_city on customers (actual time=1.355..1.365 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Index Scan using orders_customer_id on orders (actual time=1.339..1.381 rows=46 loops=1)
         Index Cond: (customer_id = ANY (ARRAY[customers.customer_id, $1, $2, ..., $1023]))
 Planning Time: 0.648 ms
 Execution Time: 3.213 ms
 Peak Memory Usage: 657 kB
(10 rows)
```

If you want to disable all Nested Loops, you need to also set  `yb_enable_batchednl=off`:

```sql
yb_demo_northwind=# set enable_hashjoin=off;
SET
yb_demo_northwind=# set enable_nestloop=off;
SET
yb_demo_northwind=# set yb_enable_batchednl=off;
SET

yb_demo_northwind=# explain (analyze, costs off)
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Merge Join (actual time=5.839..6.334 rows=46 loops=1)
   Merge Cond: (customers.customer_id = orders.customer_id)
   ->  Index Scan using customers_city on customers (actual time=1.961..1.983 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Sort (actual time=3.851..3.897 rows=679 loops=1)
         Sort Key: orders.customer_id
         Sort Method: quicksort  Memory: 150kB
         ->  Seq Scan on orders (actual time=1.992..2.717 rows=830 loops=1)
 Planning Time: 0.306 ms
 Execution Time: 6.465 ms
 Peak Memory Usage: 373 kB
(11 rows)
```

Finally if I reset all parameters but analyze the tables, The Batched Nested Loop is chosen by the query planner: 
```sql
yb_demo_northwind=# \c
You are now connected to database "yb_demo_northwind" as user "yugabyte".

yb_demo_northwind=# analyze;
WARNING:  'analyze' is a beta feature!
ANALYZE

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 YB Batched Nested Loop Join (actual time=7.830..7.851 rows=46 loops=1)
   Join Filter: (customers.customer_id = orders.customer_id)
   Sort Keys: customers.customer_id
   ->  Index Scan using customers_city on customers (actual time=1.058..1.066 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Index Scan using orders_customer_id on orders (actual time=6.150..6.208 rows=46 loops=1)
         Index Cond: (customer_id = ANY (ARRAY[customers.customer_id, $1, $2, ..., $1023]))
 Planning Time: 17.507 ms
 Execution Time: 8.226 ms
 Peak Memory Usage: 602 kB
(10 rows)
```

Letting the query planner choose the join method is probably the best, especially if you also set the Cost Based Optimizer with `yb_enable_optimizer_statistics` and `yb_enable_base_scans_cost_model`. But if you need to disable batching for a specific join, you can use `YBbatchedNL`
```sql
yb_demo_northwind=# explain (analyze, costs off)
 /*+ NoYBbatchedNL(customers orders) */
 select * from customers
 join orders using(customer_id)
where city='London'
order by customer_id;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Nested Loop (actual time=2.573..7.722 rows=46 loops=1)
   ->  Index Scan using customers_city on customers (actual time=1.548..1.558 rows=6 loops=1)
         Index Cond: ((city)::text = 'London'::text)
   ->  Index Scan using orders_customer_id on orders (actual time=0.989..0.999 rows=8 loops=6)
         Index Cond: (customer_id = customers.customer_id)
 Planning Time: 0.433 ms
 Execution Time: 7.818 ms
 Peak Memory Usage: 56 kB
(8 rows)
```