[ https://issues.apache.org/jira/browse/DRILL-6844?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pritesh Maker updated DRILL-6844: --------------------------------- Reviewer: Aman Sinha > Query with ORDER BY DESC on indexed column does not pick secondary index > ------------------------------------------------------------------------ > > Key: DRILL-6844 > URL: https://issues.apache.org/jira/browse/DRILL-6844 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.14.0 > Reporter: Hanumath Rao Maduri > Assignee: Hanumath Rao Maduri > Priority: Major > Fix For: 1.15.0 > > > Query with ORDER BY DESC on indexed column does not pick secondary index > {noformat} > // Query that uses the secondary index defined on ts. > 0: jdbc:drill:schema=dfs.tmp> explain plan for > . . . . . . . . . . . . . . > select ts from dfs.`/c8/test3` order by ts > limit 1; > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(ts=[$0]) > 00-02 SelectionVectorRemover > 00-03 Limit(fetch=[1]) > 00-04 Scan(table=[[dfs, /c8/test3]], groupscan=[JsonTableGroupScan > [ScanSpec=JsonScanSpec [tableName=maprfs:///c8/test3, condition=null, > indexName=ts], columns=[`ts`], limit=1, maxwidth=125]]) > {noformat} > // Same query with ORDER BY ts DESC does not use the secondary index defined > on ts. > 0: jdbc:drill:schema=dfs.tmp> explain plan for > . . . . . . . . . . . . . . > select ts from dfs.`/c8/test3` order by ts desc > limit 1; > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(ts=[$0]) > 00-02 SelectionVectorRemover > 00-03 Limit(fetch=[1]) > 00-04 SingleMergeExchange(sort0=[0 DESC]) > 01-01 OrderedMuxExchange(sort0=[0 DESC]) > 02-01 SelectionVectorRemover > 02-02 Limit(fetch=[1]) > 02-03 SelectionVectorRemover > 02-04 TopN(limit=[1]) > 02-05 HashToRandomExchange(dist0=[[$0]]) > 03-01 Scan(table=[[dfs, /c8/test3]], groupscan=[JsonTableGroupScan > [ScanSpec=JsonScanSpec [tableName=maprfs:///c8/test3, condition=null], > columns=[`ts`], maxwidth=8554]]) > {noformat} > { noformat} > Index definition is, > maprcli table index list -path /c8/test3 -json > { > "timestamp":1538066303932, > "timeofday":"2018-09-27 04:38:23.932 GMT+0000 PM", > "status":"OK", > "total":2, > "data":[ > { > "cluster":"c8", > "type":"maprdb.si", > "indexFid":"2176.68.131294", > "indexName":"ts", > "hashed":false, > "indexState":"REPLICA_STATE_REPLICATING", > "idx":1, > "indexedFields":"ts:ASC", > "isUptodate":false, > "minPendingTS":1538066077, > "maxPendingTS":1538066077, > "bytesPending":0, > "putsPending":0, > "bucketsPending":1, > "copyTableCompletionPercentage":100, > "numTablets":32, > "numRows":80574368, > "totalSize":4854052160 > }, > { > "cluster":"c8", > "type":"maprdb.si", > "indexFid":"2176.72.131302", > "indexName":"ts_desc", > "hashed":false, > "indexState":"REPLICA_STATE_REPLICATING", > "idx":2, > "indexedFields":"ts:DESC", > "isUptodate":false, > "minPendingTS":1538066077, > "maxPendingTS":1538066077, > "bytesPending":0, > "putsPending":0, > "bucketsPending":1, > "copyTableCompletionPercentage":100, > "numTablets":32, > "numRows":80081344, > "totalSize":4937154560 > } > ] > } > {noformat} -- This message was sent by Atlassian JIRA (v7.6.3#76005)