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)
