Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
1.14.0
Description
Query with ORDER BY DESC on indexed column does not pick secondary index
// 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]])
// 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}
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
}
]
}
|
Attachments
Issue Links
- links to