[YSQL] For join condition on primary key without filter on both hash partition and range partitioned tables, inefficient plan are selected.

[shantanugupta-yb]

Jira Link: DB-5796

Description

By default joins are performed using Nested loop which is taking around ~3+ min where as with Merge/Hash join is 50-60X less at ~3-4 sec.

Nested loop selected by default

yugabyte=# explain (analyse, verbose, dist) select Hashtbl_1.col_bigint_id_1,Hashtbl_2.col_bigint_id_1 from Hashtbl_1 join Hashtbl_2 on Hashtbl_1.col_bigint_id_1=Hashtbl_2.col_bigint_id_1;
   -----------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..210003.89 rows=1000000 width=16) (actual time=2.038..194627.204 rows=1000000 loops=1)
   Output: hashtbl_1.col_bigint_id_1, hashtbl_2.col_bigint_id_1
   Inner Unique: true
   ->  Seq Scan on public.hashtbl_1  (cost=0.00..100000.00 rows=1000000 width=8) (actual time=1.681..394.715 rows=1000000 loops=1)
         Output: hashtbl_1.col_bigint_id_1, hashtbl_1.col_bigint_id_2, hashtbl_1.col_bigint_id_3, hashtbl_1.col_bigint_id_4, hashtbl_1.col_bigint_1, hashtbl_1.col_bigint_2, hashtbl_1.col_float2_1, hashtbl_1.col_float2_2, hashtbl_1.col_float5_1, hashtbl_1
.col_float5_2, hashtbl_1.col_boolean_1, hashtbl_1.col_varchar10_id_1, hashtbl_1.col_varchar100_id_1, hashtbl_1.col_varchar100_id_2, hashtbl_1.col_varchar500_id_1
         Storage Table Read Requests: 978
         Storage Table Execution Time: 3.000 ms
   ->  Index Scan using hashtbl_2_pkey on public.hashtbl_2  (cost=0.00..0.11 rows=1 width=8) (actual time=0.185..0.185 rows=1 loops=1000000)
         Output: hashtbl_2.col_bigint_id_1, hashtbl_2.col_bigint_id_2, hashtbl_2.col_bigint_id_3, hashtbl_2.col_bigint_id_4, hashtbl_2.col_bigint_1, hashtbl_2.col_bigint_2, hashtbl_2.col_float2_1, hashtbl_2.col_float2_2, hashtbl_2.col_float5_1, hashtbl_2
.col_float5_2, hashtbl_2.col_boolean_1, hashtbl_2.col_varchar10_id_1, hashtbl_2.col_varchar100_id_1, hashtbl_2.col_varchar100_id_2, hashtbl_2.col_varchar500_id_1
         Index Cond: (hashtbl_2.col_bigint_id_1 = hashtbl_1.col_bigint_id_1)
         Storage Index Read Requests: 1
         Storage Index Execution Time: 0.167 ms
 Planning Time: 17.961 ms
 Execution Time: 194788.206 ms
 Storage Read Requests: 1000978
 Storage Write Requests: 0
 Storage Execution Time: 167266.136 ms
 Peak Memory Usage: 70 kB

Hash join

yugabyte=# explain (analyse, verbose, dist) select Hashtbl_1.col_bigint_id_1,Hashtbl_2.col_bigint_id_1 from Hashtbl_1 join Hashtbl_2 on Hashtbl_1.col_bigint_id_1=Hashtbl_2.col_bigint_id_1;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=116407.00..230753.01 rows=1000000 width=16) (actual time=1491.217..3165.506 rows=1000000 loops=1)
   Output: hashtbl_1.col_bigint_id_1, hashtbl_2.col_bigint_id_1
   Inner Unique: true
   Hash Cond: (hashtbl_1.col_bigint_id_1 = hashtbl_2.col_bigint_id_1)
   ->  Seq Scan on public.hashtbl_1  (cost=0.00..100000.00 rows=1000000 width=8) (actual time=1.778..1329.712 rows=1000000 loops=1)
         Output: hashtbl_1.col_bigint_id_1
         Storage Table Read Requests: 978
         Storage Table Execution Time: 1208.987 ms
   ->  Hash  (cost=100000.00..100000.00 rows=1000000 width=8) (actual time=1487.786..1487.786 rows=1000000 loops=1)
         Output: hashtbl_2.col_bigint_id_1
         Buckets: 131072  Batches: 16  Memory Usage: 3471kB
         ->  Seq Scan on public.hashtbl_2  (cost=0.00..100000.00 rows=1000000 width=8) (actual time=1.619..1351.916 rows=1000000 loops=1)
               Output: hashtbl_2.col_bigint_id_1
               Storage Table Read Requests: 978
               Storage Table Execution Time: 1208.987 ms
 Planning Time: 0.139 ms
 Execution Time: 3200.476 ms
 Storage Read Requests: 1956
 Storage Write Requests: 0
 Storage Execution Time: 2417.974 ms
 Peak Memory Usage: 5414 kB

Merge join

yugabyte=# explain (analyse, verbose, dist) select Hashtbl_1.col_bigint_id_1,Hashtbl_2.col_bigint_id_1 from Hashtbl_1 join Hashtbl_2 on Hashtbl_1.col_bigint_id_1=Hashtbl_2.col_bigint_id_1;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=426664.69..446664.69 rows=1000000 width=16) (actual time=3220.029..3600.630 rows=1000000 loops=1)
   Output: hashtbl_1.col_bigint_id_1, hashtbl_2.col_bigint_id_1
   Inner Unique: true
   Merge Cond: (hashtbl_1.col_bigint_id_1 = hashtbl_2.col_bigint_id_1)
   ->  Sort  (cost=213332.34..215832.34 rows=1000000 width=8) (actual time=1601.939..1697.410 rows=1000000 loops=1)
         Output: hashtbl_1.col_bigint_id_1
         Sort Key: hashtbl_1.col_bigint_id_1
         Sort Method: external merge  Disk: 17696kB
         ->  Seq Scan on public.hashtbl_1  (cost=0.00..100000.00 rows=1000000 width=8) (actual time=1.684..1326.872 rows=1000000 loops=1)
               Output: hashtbl_1.col_bigint_id_1
               Storage Table Read Requests: 978
               Storage Table Execution Time: 1214.985 ms
   ->  Sort  (cost=213332.34..215832.34 rows=1000000 width=8) (actual time=1618.074..1715.133 rows=1000000 loops=1)
         Output: hashtbl_2.col_bigint_id_1
         Sort Key: hashtbl_2.col_bigint_id_1
         Sort Method: external merge  Disk: 17696kB
         ->  Seq Scan on public.hashtbl_2  (cost=0.00..100000.00 rows=1000000 width=8) (actual time=1.604..1340.324 rows=1000000 loops=1)
               Output: hashtbl_2.col_bigint_id_1
               Storage Table Read Requests: 978
               Storage Table Execution Time: 1202.986 ms
 Planning Time: 0.122 ms
 Execution Time: 3640.803 ms
 Storage Read Requests: 1956
 Storage Write Requests: 0
 Storage Execution Time: 2417.971 ms
 Peak Memory Usage: 11015 kB

Table Schema

yugabyte=# \d Hashtbl_1
                           Table "public.hashtbl_1"
       Column        |          Type          | Collation | Nullable | Default 
---------------------+------------------------+-----------+----------+---------
 col_bigint_id_1     | bigint                 |           | not null | 
 col_bigint_id_2     | bigint                 |           |          | 
 col_bigint_id_3     | bigint                 |           |          | 
 col_bigint_id_4     | bigint                 |           |          | 
 col_bigint_1        | bigint                 |           |          | 
 col_bigint_2        | bigint                 |           |          | 
 col_float2_1        | real                   |           |          | 
 col_float2_2        | real                   |           |          | 
 col_float5_1        | real                   |           |          | 
 col_float5_2        | real                   |           |          | 
 col_boolean_1       | boolean                |           |          | 
 col_varchar10_id_1  | character varying(10)  |           |          | 
 col_varchar100_id_1 | character varying(100) |           |          | 
 col_varchar100_id_2 | character varying(100) |           |          | 
 col_varchar500_id_1 | character varying(500) |           |          | 
Indexes:
    "hashtbl_1_pkey" PRIMARY KEY, lsm (col_bigint_id_1 HASH)
    "hashtbl_1_col_bigint_id_2_idx" lsm (col_bigint_id_2 HASH)
    "hashtbl_1_col_bigint_id_3_idx" lsm (col_bigint_id_3 ASC)

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

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