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 <abhishektall...@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) > >
