[MCOL-3664] Subquery wrapping affects performance Created: 2019-12-10  Updated: 2020-03-25  Resolved: 2020-03-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.4.1
Fix Version/s: 1.4.4

Type: Bug Priority: Minor
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Attachments: PNG File subquery wrap bug.png    
Issue Links:
Relates
relates to MCOL-3747 Regression in 1.4 working_ssb_compare... Closed
Sprint: 2020-2, 2020-3, 2020-4, 2020-5

 Description   

Wrapping a query in a subquery seems to impact performance in the 1.4 tree. See attached image for an example.

Marked as to be investigated for 1.4.3 for now.

 Comments   
Comment by Roman [ 2020-02-05 ]

My tests showed that regression has been fixed with MCOL-3747. Moreover the query contains the same pattern IN+uncorrelated subquery.

Comment by Daniel Lee (Inactive) [ 2020-03-25 ]

Build verified: 1.4.4-1 source

/root/ColumnStore/buildColumnstoreFromGithubSource/server
commit aaf2a53452447b0223866db16e88d52448986ea3
Author: Monty <monty@mariadb.org>
Date: Thu Aug 8 23:04:05 2019 +0300

MENT-401: Include Aria and S3 index length limit increase in ES 10.4

Cherry-picked from:

98ea611940fd492fc5f883625f2afcbbab312795

MDEV-20279 Increase Aria index length limit

/root/ColumnStore/buildColumnstoreFromGithubSource/server/engine
commit 0574127148e4c0a1cd9a341c7b9e8ffc982d0d24
Merge: ca3e2d7 cd7372f
Author: David.Hall <david.hall@mariadb.com>
Date: Mon Mar 23 13:43:27 2020 -0500

Merge pull request #1099 from pleblanc1976/mcol-2022-1.4

Mcol 2022 1.4

Verified that the reported issue no longer an issue. I ran both disk-run and cached-run and got similar results.

MariaDB [tpch10]> select c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, sum(l.l_quantity) from customer c, orders o, lineitem l where o.o_orderkey in( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 313 ) and c.c_custkey = o.o_custkey and o.o_orderkey = l.l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice limit 100;

-------------------------------------------------------------------------------+

c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l.l_quantity)

-------------------------------------------------------------------------------+

Customer#000065491 65491 7549735 1994-06-30 424178.05 315.00
Customer#001172513 1172513 36667107 1997-06-06 550142.18 322.00
Customer#000778949 778949 40876003 1992-05-10 481422.24 314.00
Customer#001247930 1247930 59138305 1993-10-31 509372.97 321.00

.
.

Customer#000480857 480857 12809282 1994-05-21 417929.65 314.00
Customer#000287270 287270 37531878 1997-03-20 512044.58 315.00
Customer#001185599 1185599 54188961 1995-06-17 407873.05 317.00

-------------------------------------------------------------------------------+
99 rows in set (57.456 sec)

MariaDB [tpch10]> select * from (select c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, sum(l.l_quantity) from customer c, orders o, lineitem l where o.o_orderkey in( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 313 ) and c.c_custkey = o.o_custkey and o.o_orderkey = l.l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice limit 100) dummytable;

-------------------------------------------------------------------------------+

c_name c_custkey o_orderkey o_orderdate o_totalprice sum(l.l_quantity)

-------------------------------------------------------------------------------+

Customer#000065491 65491 7549735 1994-06-30 424178.05 315.00
Customer#000347839 347839 53623108 1995-05-07 481251.56 316.00
Customer#001247930 1247930 59138305 1993-10-31 509372.97 321.00

.
.

Customer#000126200 126200 23861472 1997-10-30 472802.59 314.00
Customer#001288183 1288183 48943904 1996-07-22 398081.59 325.00
Customer#000480857 480857 12809282 1994-05-21 417929.65 314.00

-------------------------------------------------------------------------------+
99 rows in set (55.819 sec)

Generated at Thu Feb 08 02:44:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.