phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From talluri abhishek <abhishektall...@gmail.com>
Subject Re: Query performance analysis when using LIMIT clause
Date Tue, 07 Jan 2020 19:42:58 GMT
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 <abhishektalluri@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)
>
>

Mime
View raw message