phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From talluri abhishek <abhishektall...@gmail.com>
Subject Query performance analysis when using LIMIT clause
Date Wed, 04 Dec 2019 17:47:16 GMT
Hi All,

My setup has phoenix 4.14 and a table with 2 column families(CF1 & CF2)
where CF1 has around 100 columns and CF2 has 3 columns. Below are a few
queries which show the difference in execution times with and without limit
clause and their query plans. There is almost > 20x performance degradation
when using limit clause on these queries. Any thoughts on this behavior?

0: jdbc:phoenix:> select CF1.AA, CF2.A, CF2.B from test_limit where CF2.A =
'lgak';

*+-------+-------+-------+*

*| ** AA  ** | **  A  ** | **  B  ** |*

*+-------+-------+-------+*

*| *znvv * | *lgak * | *wjkm * |*

*| *kiry * | *lgak * | *gnpu * |*

*| *qbnp * | *lgak * | *yowh * |*

*| *xzfc * | *lgak * | *nibn * |*

*+-------+-------+-------+*

4 rows selected (0.603 seconds)

0: jdbc:phoenix:> select CF1.AA, CF2.A, CF2.B from test_limit where CF2.A =
'lgak' limit 2;

*+-------+-------+-------+*

*| ** AA  ** | **  A  ** | **  B  ** |*

*+-------+-------+-------+*

*| *znvv * | *lgak * | *wjkm * |*

*| *kiry * | *lgak * | *gnpu * |*

*+-------+-------+-------+*

2 rows selected (12.115 seconds)

0: jdbc:phoenix:> select CF1.AA, CF2.A, CF2.B from test_limit where CF2.A =
'lgak' limit 10;

*+-------+-------+-------+*

*| ** AA  ** | **  A  ** | **  B  ** |*

*+-------+-------+-------+*

*| *znvv * | *lgak * | *wjkm * |*

*| *kiry * | *lgak * | *gnpu * |*

*| *qbnp * | *lgak * | *yowh * |*

*| *xzfc * | *lgak * | *nibn * |*

*+-------+-------+-------+*

4 rows selected (15.338 seconds)

0: jdbc:phoenix:> explain select CF1.AA, CF2.A, CF2.B from test_limit where
CF2.A = 'lgak';

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

*| **                                             PLAN
                         ** | **EST_BYTES_READ ** | **EST_ROWS_READ ** | **
EST_INFO_TS  ** |*

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

*| *CLIENT 2-CHUNK 35112 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN
FULL SCAN OVER TEST_LIMIT * | *314572800      * | *35112         * | *
1575395762384 * |*

*| *    SERVER FILTER BY CF2.A = 'lgak'
                       * | *314572800      * | *35112         * | *
1575395762384 * |*

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

2 rows selected (0.033 seconds)

0: jdbc:phoenix:> explain select CF1.AA, CF2.A, CF2.B from test_limit where
CF2.A = 'lgak' limit 10;

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

*| **                                             PLAN
                         ** | **EST_BYTES_READ ** | **EST_ROWS_READ ** | **
EST_INFO_TS  ** |*

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

*| *CLIENT 2-CHUNK 35112 ROWS 314572800 BYTES PARALLEL 1-WAY ROUND ROBIN
FULL SCAN OVER TEST_LIMIT * | *314572800      * | *35112         * | *
1575395762384 * |*

*| *    SERVER FILTER BY CF2.A = 'lgak'
                       * | *314572800      * | *35112         * | *
1575395762384 * |*

*| *    SERVER 10 ROW LIMIT
                       * | *314572800      * | *35112         * | *
1575395762384 * |*

*| *CLIENT 10 ROW LIMIT
                       * | *314572800      * | *35112         * | *
1575395762384 * |*

*+-------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+*

4 rows selected (0.032 seconds)

Mime
View raw message