all

This is definitely something that has come up a lot recently. We have some ideas on how to solve this. It is too late to implement this in 1.1 but we could do this for a later version.

There is current a workaround if you are doing "order by X limit Y" and that is to wrap it in a subquery such as:

select * from (select * from t2 order by b limit 4) sq;

You need the "limit" though because "order by" will be optimised out without it.

This has been implemented in 1.2 for subqueries, etc... That effort is tracked in the subtask. It will be ported to 1.4 for all ColumnStore queries which will be tracked here.

Build verified: 1.4.1-1

[dlee@master centos7]$ cat gitversionInfo.txt
engine commit:
bd41ffd

1.4.0-1 beta and 1.4.1-1 is much faster than 1.2.5-1 and 1.2.6-1, using single or multiple thread.

In 1.4.0-1, using multiple also has a great performance gain.

1.4.1-1

MariaDB [tpch10]> set columnstore_orderby_threads=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [tpch10]> select l_orderkey from lineitem order by l_orderkey limit 10;
------------

------------

------------
10 rows in set (4.232 sec)

MariaDB [tpch10]> select l_orderkey, l_comment from lineitem order by l_comment limit 10;
----------------------+

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

----------------------+
10 rows in set (35.206 sec)

MariaDB [tpch10]> set columnstore_orderby_threads=8;
Query OK, 0 rows affected (0.001 sec)

MariaDB [tpch10]> select l_orderkey from lineitem order by l_orderkey limit 10;
------------

------------

------------
10 rows in set (2.524 sec)

MariaDB [tpch10]> set columnstore_orderby_threads=8;
Query OK, 0 rows affected (0.001 sec)

MariaDB [tpch10]> select l_orderkey, l_comment from lineitem order by l_comment limit 10;
----------------------+

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

----------------------+
10 rows in set (16.405 sec)

closing ticket per test results for both this ticket and its subtask.