phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "M. Aaron Bossert" <maboss...@gmail.com>
Subject unexpected behavior...MIN vs ORDER BY and LIMIT 1
Date Wed, 16 Jan 2019 02:55:00 GMT
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