I am completely new to Yugabyte and have just downloaded the latest docker image for a test.
Following the quick start guide for docker I ran the aggregation test query:
yb_demo=# SELECT source, ROUND(SUM(orders.total)) AS total_sales
FROM users, orders WHERE users.id=orders.user_id
GROUP BY source
ORDER BY total_sales DESC;
On my test system (a laptop with 16G running Ubuntu 20.04) the query took more than 12 seconds. I use the same test data on a postgresql instance on the same system, and the query took only 0.1s.
How is this huge performance difference to be explained? Surely something is wrong with my setup but I have just followed the quick start guide.
I just tried the same with my setup (16gb laptop, ubuntu 18.04, i7,ssd). The aggregate query finished in 3-4 seconds. Are you testing from ysqlsh with \timing ? If not, can you try that and report back ?
yb_colocated=# SELECT source, ROUND(SUM(orders.total)) AS total_sales
yb_colocated-# FROM users, orders WHERE users.id=orders.user_id
yb_colocated-# GROUP BY source
yb_colocated-# ORDER BY total_sales DESC;
source | total_sales
-----------+-------------
Facebook | 333454
Google | 325184
Organic | 319637
Twitter | 319449
Affiliate | 297605
(5 rows)
Time: 3980.298 ms (00:03.980)
yb_demo=# SELECT source, ROUND(SUM(orders.total)) AS total_sales
yb_demo-# FROM users, orders WHERE users.id=orders.user_id
yb_demo-# GROUP BY source
yb_demo-# ORDER BY total_sales DESC;
SELECT source, ROUND(SUM(orders.total)) AS total_sales
FROM users, orders WHERE users.id=orders.user_id
GROUP BY source
ORDER BY total_sales DESC;
source | total_sales
-----------+-------------
Facebook | 333454
Google | 325184
Organic | 319637
Twitter | 319449
Affiliate | 297605
(5 rows)
Time: 10270.887 ms (00:10.271)
A few observations:
I am running yugabyte from docker image. Maybe this could make a difference?
the same query when run from pgAdmin4 console took consistently longer than from ysql:
Anyway, even 4 seconds for such a simple query is beyond my expectation. As previously noted, it took 100ms on postgresql. And I just did a test on cockroachdb with 3 nodes and it took 150ms. All tests are performed under docker on the same laptop which has AMD Ryzen 5 3500U and 16GB memory with 512G SSD.
