phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas D'Silva" <tdsi...@salesforce.com>
Subject Re: unexpected behavior...MIN vs ORDER BY and LIMIT 1
Date Fri, 18 Jan 2019 00:26:41 GMT
The first query scans over all the rows in the index, while the second
query reads one row (SERVER 1 ROW LIMIT ).

On Tue, Jan 15, 2019 at 6:55 PM M. Aaron Bossert <mabossert@gmail.com>
wrote:

> I have a table (~ 724M rows) with a secondary index on the "TIME" column.
> When I run a MIN function on the table, the query takes ~290 sec to
> complete and by selecting on TIME and ORDERing by TIME, the query runs in
> about 0.04 sec.
>
> Here is the explain output for both queries...I totally understand that
> reading the entire table takes much longer than reading one row, I don't
> quite get why there is such a large performance delta.  Am I missing
> something?
>
> 0: jdbc:phoenix:thin:url=http://localhost:876> EXPLAIN SELECT MIN(TIME)
> FROM CHASE.LANL_FLOW LIMIT 1;
>
> +----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> |                                                PLAN
>                           | EST_BYTES_READ  | EST_ROWS_READ  |
> EST_INFO_TS   |
>
> +----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> | CLIENT 176-CHUNK 723963021 ROWS 54735673888 BYTES PARALLEL 1-WAY FULL
> SCAN OVER CHASE:ASYNC_INDEX  | 54735673888     | 723963021      |
> 1547591769731  |
> |     SERVER FILTER BY FIRST KEY ONLY
>                           | 54735673888     | 723963021      |
> 1547591769731  |
> |     SERVER AGGREGATE INTO SINGLE ROW
>                            | 54735673888     | 723963021      |
> 1547591769731  |
> | CLIENT 1 ROW LIMIT
>                            | 54735673888     | 723963021      |
> 1547591769731  |
>
> +----------------------------------------------------------------------------------------------------+-----------------+----------------+----------------+
> 4 rows selected (0.036 seconds)
> 0: jdbc:phoenix:thin:url=http://localhost:876> EXPLAIN SELECT TIME FROM
> CHASE.LANL_FLOW ORDER BY TIME ASC LIMIT 1;
>
> +-------------------------------------------------------------------------------+-----------------+----------------+--------------+
> |                                     PLAN
>       | EST_BYTES_READ  | EST_ROWS_READ  | EST_INFO_TS  |
>
> +-------------------------------------------------------------------------------+-----------------+----------------+--------------+
> | CLIENT 2-CHUNK 1 ROWS 77 BYTES SERIAL 1-WAY FULL SCAN OVER
> CHASE:ASYNC_INDEX  | 77              | 1              | 0            |
> |     SERVER FILTER BY FIRST KEY ONLY
>      | 77              | 1              | 0            |
> |     SERVER 1 ROW LIMIT
>       | 77              | 1              | 0            |
> | CLIENT 1 ROW LIMIT
>       | 77              | 1              | 0            |
>
> +-------------------------------------------------------------------------------+-----------------+----------------+--------------+
> 4 rows selected (0.02 seconds)
>

Mime
View raw message