[YSQL] BNLJ with query LIMIT and size-based limit does not reset row limit after first request, resulting in extra RPCs

[timothy-e]

Jira Link: DB-7610

Description

CREATE TABLE tb1 (a INT PRIMARY KEY, b TEXT);
CREATE TABLE tb2 (a INT PRIMARY KEY, b TEXT);
INSERT INTO tb1 SELECT i, REPEAT('a', 1000) FROM generate_series(1, 2000) i;
INSERT INTO tb2 SELECT i, REPEAT('b', 1000) FROM generate_series(1, 2000) i;
SET yb_bnl_batch_size = 1024;

Query:

explain (analyze, dist, costs off, timing off) /*+ nestloop(a b) */
select tb1.a, tb2.b from tb1 join tb2 on tb1.a = tb2.a LIMIT 10;

With row limit

SET yb_fetch_size_limit = 0;
SET yb_fetch_row_limit = 1024;

The query has 4 requests: three for the outer table (10 + 1024 + 1024 rows), one for the inner table (10 rows). This works as expected.

With size limit

SET yb_fetch_size_limit = 1024;
SET yb_fetch_row_limit = 0;

The query gives the plan:

                              QUERY PLAN
------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  YB Batched Nested Loop Join (actual rows=10 loops=1)
         Join Filter: (tb1.a = tb2.a)
         ->  Seq Scan on tb1 (actual rows=1024 loops=1)
               Storage Table Read Requests: 103
         ->  Index Scan using tb2_pkey on tb2 (actual rows=10 loops=1)
               Index Cond: (a = ANY (ARRAY[tb1.a, $1, $2, ..., $1023]))
               Storage Table Read Requests: 1
 Planning Time: 0.952 ms
 Execution Time: 45.907 ms
 Storage Read Requests: 104
 Storage Write Requests: 0
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 0
 Peak Memory Usage: 800 kB
(16 rows)

Since there is no row limit set, the LIMIT passed down by the query is never overwritten by anything, and every request is sent with a 10 row limit. We sent 103 requests for 10 rows each, until we have enough to fill the batch size.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.