You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by talluri abhishek <ab...@gmail.com> on 2019/12/04 17:47:16 UTC
Query performance analysis when using LIMIT clause
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)
Re: Query performance analysis when using LIMIT clause
Posted by talluri abhishek <ab...@gmail.com>.
Hi All,
Bumping this up to see if anyone has any thoughts on this behavior when
using a LIMIT clause.
Thanks,
Abhishek
On Wed, Dec 4, 2019 at 12:47 PM talluri abhishek <ab...@gmail.com>
wrote:
> 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)
>
>