Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 1.4.1
    • 1.4.4
    • None
    • None
    • 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.

      Attachments

        Issue Links

          Activity

            drrtuy Roman added a comment -

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

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

            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)

            dleeyh Daniel Lee (Inactive) added a comment - 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)

            People

              dleeyh Daniel Lee (Inactive)
              LinuxJedi Andrew Hutchings (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.