search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.96k stars 1.07k forks source link

[YSQL] Sort is slower in YB in comparison to PostgreSQL #19345

Open sushantrmishra opened 1 year ago

sushantrmishra commented 1 year ago

Jira Link: DB-8147

Description

TLDR: Sort operation takes ~90ms vs ~200ms in YugabyteDB.

TEST SETUP:

Client is in us-west-2a and work_mem is set to 50MB.

RDS PostgreSQL Instance: db.m5.2xlarge (us-west-2a), PostgreSQL version 15.3, GP3

Default PostgreSQL execution: It used the index scan and incremental sort, hence much faster. Though not relevant to issue, just putting here for completeness.

taqo_basic=> explain analyze SELECT t1.k1,
       t1.k2,
       t1.v1,
       t1.v2
FROM   t1
ORDER BY t1.k1, t1.v1 limit 100000;
                                                               QUERY PLAN

---------------------------------------------------------------------------------------------------------------
-------------------------
 Limit  (cost=0.48..9468.05 rows=100000 width=34) (actual time=0.031..32.249 rows=100000 loops=1)
   ->  Incremental Sort  (cost=0.48..47338.30 rows=500000 width=34) (actual time=0.029..25.175 rows=100000 loop
s=1)
         Sort Key: k1, v1
         Presorted Key: k1
         Full-sort Groups: 3125  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         ->  Index Scan using t1_pkey on t1  (cost=0.42..24838.30 rows=500000 width=34) (actual time=0.015..13.
647 rows=100001 loops=1)
 Planning Time: 0.070 ms
 Execution Time: 35.946 ms
(8 rows)

Time: 36.587 ms

Turn off the incremental sorting and parallelism (to get the YB equivalent plan):

taqo_basic=> set max_parallel_workers_per_gather = 0;
SET
Time: 0.495 ms
taqo_basic=> set enable_incremental_sort to false ;
SET
Time: 0.402 ms
taqo_basic=> explain analyze SELECT t1.k1,
       t1.k2,
       t1.v1,
       t1.v2
FROM   t1
ORDER BY t1.k1, t1.v1 limit 100000;
                                                      QUERY PLAN

---------------------------------------------------------------------------------------------------------------
--------
 Limit  (cost=53191.10..53441.10 rows=100000 width=34) (actual time=115.115..127.209 rows=100000 loops=1)
   ->  Sort  (cost=53191.10..54441.10 rows=500000 width=34) (actual time=115.113..120.134 rows=100000 loops=1)
         Sort Key: k1, v1
         Sort Method: top-N heapsort  Memory: 13957kB
         ->  Seq Scan on t1  (cost=0.00..9167.00 rows=500000 width=34) (actual time=0.008..35.090 rows=500000 l
oops=1)
 Planning Time: 0.061 ms
 Execution Time: 131.762 ms
(7 rows)

Time: 132.441 ms

YB Test: Configuration: -rf 3 cluster, leaders in us-west-2a, colocated database, c5.2xlarge , 2.19.3-b122

taqo_basic=# explain (analyze)  SELECT t1.k1,
       t1.k2,
       t1.v1,
       t1.v2
FROM   t1
ORDER BY t1.k1, t1.v1 limit 100000;
                                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------
------
 Limit  (cost=149.83..152.33 rows=1000 width=72) (actual time=348.651..381.975 rows=100000 loops=1)
   ->  Sort  (cost=149.83..152.33 rows=1000 width=72) (actual time=348.648..355.852 rows=100000 loops=1)
         Sort Key: k1, v1
         Sort Method: top-N heapsort  Memory: 13957kB
         ->  Seq Scan on t1  (cost=0.00..100.00 rows=1000 width=72) (actual time=0.957..155.454 rows=500000 loo
ps=1)
 Planning Time: 0.054 ms
 Execution Time: 401.862 ms
 Peak Memory Usage: 22807 kB
(8 rows)

Time: 402.594 ms

With DIST option:

taqo_basic=# explain (analyze,dist)  SELECT t1.k1,
       t1.k2,
       t1.v1,
       t1.v2
FROM   t1
ORDER BY t1.k1, t1.v1 limit 100000;
                                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------
------
 Limit  (cost=149.83..152.33 rows=1000 width=72) (actual time=345.545..378.578 rows=100000 loops=1)
   ->  Sort  (cost=149.83..152.33 rows=1000 width=72) (actual time=345.542..352.683 rows=100000 loops=1)
         Sort Key: k1, v1
         Sort Method: top-N heapsort  Memory: 13957kB
         ->  Seq Scan on t1  (cost=0.00..100.00 rows=1000 width=72) (actual time=0.972..154.830 rows=500000 loo
ps=1)
               Storage Table Read Requests: 489
               Storage Table Read Execution Time: 50.522 ms
 Planning Time: 0.055 ms
 Execution Time: 398.323 ms
 Storage Read Requests: 489
 Storage Read Execution Time: 50.522 ms
 Storage Write Requests: 0.000
 Catalog Read Requests: 0
 Catalog Write Requests: 0.000
 Storage Flush Requests: 0
 Storage Execution Time: 50.522 ms
 Peak Memory Usage: 22802 kB
(17 rows)

Time: 399.126 ms

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

sushantrmishra commented 1 year ago

Similar is observed for HashAggregate as well: 

taqo_basic=# explain (analyze, dist) SELECT t1.k1,
taqo_basic-#        t1.k2,
taqo_basic-#        t1.v1,
taqo_basic-#        t1.v2
taqo_basic-# FROM   t1
taqo_basic-# GROUP BY t1.k1, t1.k2, t1.v1, t1.v2 limit 100000
taqo_basic-# ;

                                                     QUERY PLAN

---------------------------------------------------------------------------------------------------------------
------
 Limit  (cost=105.00..107.00 rows=200 width=72) (actual time=995.416..1047.885 rows=100000 loops=1)
   ->  HashAggregate  (cost=105.00..107.00 rows=200 width=72) (actual time=995.414..1018.708 rows=100000 loops=
1)
         Group Key: k1, k2
         ->  Seq Scan on t1  (cost=0.00..100.00 rows=1000 width=72) (actual time=6.957..706.215 rows=500000 loo
ps=1)
               Storage Table Read Requests: 489
               Storage Table Read Execution Time: 601.210 ms
 Planning Time: 3.678 ms
 Execution Time: 1071.829 ms
 Storage Read Requests: 489
 Storage Read Execution Time: 601.210 ms
 Storage Write Requests: 0.000
 Catalog Read Requests: 23
 Catalog Read Execution Time: 7.653 ms
 Catalog Write Requests: 0.000
 Storage Flush Requests: 0
 Storage Execution Time: 608.862 ms
 Peak Memory Usage: 82546 kB
(17 rows)

taqo_basic=#

Execute the same query against RDSPG: 

taqo_basic=> explain analyze SELECT t1.k1,
       t1.k2,
       t1.v1,
       t1.v2
FROM   t1
GROUP BY t1.k1, t1.k2, t1.v1, t1.v2 limit 100000
;
                                                       QUERY PLAN

---------------------------------------------------------------------------------------------------------------
---------
 Limit  (cost=11667.00..12667.00 rows=100000 width=34) (actual time=325.321..354.242 rows=100000 loops=1)
   ->  HashAggregate  (cost=11667.00..16667.00 rows=500000 width=34) (actual time=325.320..347.195 rows=100000
loops=1)
         Group Key: k1, k2
         Batches: 1  Memory Usage: 65553kB
         ->  Seq Scan on t1  (cost=0.00..9167.00 rows=500000 width=34) (actual time=0.010..117.864 rows=500000
loops=1)
 Planning Time: 0.095 ms
 Execution Time: 374.058 ms
(7 rows)