[YSQL] Orderby operation with a sequential scan with filter condition(expression pushdown) is 2x faster than a sequential scan without filter.

Jira Link: DB-5388

Description

Orderby clause on a non indexed/primary key column causes a sequential scan but

• without a filter condition it fetches 1M rows in batch of 1024 from docDB resulting in 977 rpcs
• where as in case of filter, an expression is pushed down and it results in 327 rpc which reduces the docDB time.
• both fetching 1M rows

In case of even a seq scan without a filter YB should pushdown and reduce rpcs and docDB execution time.

Orderby clause with filter

yugabyte=# explain (analyse, verbose, dist) select col_bigint_id_1 from nonIndexed1M_1 where col_bigint_id_1>1 order by col_bigint_id_1
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=152.33..154.83 rows=1000 width=8) (actual time=787.262..879.237 rows=999999 loops=1)
   Output: col_bigint_id_1
   Sort Key: nonindexed1m_1.col_bigint_id_1
   Sort Method: external merge  Disk: 17696kB
   ->  Seq Scan on public.nonindexed1m_1  (cost=0.00..102.50 rows=1000 width=8) (actual time=2.331..526.595 rows=999999 loops=1)
         Output: col_bigint_id_1
         Remote Filter: (nonindexed1m_1.col_bigint_id_1 > 1)
         Storage Table Read Requests: 327
         Storage Table Execution Time: 438.996 ms
 Planning Time: 0.042 ms
 Execution Time: 915.294 ms
 Storage Read Requests: 327
 Storage Write Requests: 0
 Storage Execution Time: 438.996 ms
 Peak Memory Usage: 6297 kB

Orderby clause without filter

yugabyte=# explain (analyse, verbose, dist)select col_bigint_id_1 from nonIndexed1M_1 order by col_bigint_id_1
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=149.83..152.33 rows=1000 width=8) (actual time=1710.624..1802.716 rows=1000000 loops=1)
   Output: col_bigint_id_1
   Sort Key: nonindexed1m_1.col_bigint_id_1
   Sort Method: external merge  Disk: 17696kB
   ->  Seq Scan on public.nonindexed1m_1  (cost=0.00..100.00 rows=1000 width=8) (actual time=2.003..1447.262 rows=1000000 loops=1)
         Output: col_bigint_id_1
         Storage Table Read Requests: 979
         Storage Table Execution Time: 1390.988 ms
 Planning Time: 0.037 ms
 Execution Time: 1838.801 ms
 Storage Read Requests: 979
 Storage Write Requests: 0
 Storage Execution Time: 1390.988 ms
 Peak Memory Usage: 6253 kB
(14 rows)

Schema details:

Without filter: Goal: ORDG1_yb_ordby_bigint_non_indexed_column https://github.com/yugabyte/benchbase/blob/main/config/yugabyte/orderby_workloads/yb_regular/ORDG1_yb_index_type_impact.yaml With filter: Goal: ORDG2_yb_ordby_bigint_non_indexed_column https://github.com/yugabyte/benchbase/blob/main/config/yugabyte/orderby_workloads/yb_regular/ORDG2_yb_index_filter_condition_type_impact.yaml