Query performance analysis when using LIMIT clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Query performance analysis when using LIMIT clause

talluri abhishek
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)


Reply | Threaded
Open this post in threaded view
|

Re: Query performance analysis when using LIMIT clause

talluri abhishek
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 <[hidden email]> 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)