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)
