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