Hanumath Rao Maduri created DRILL-6844:
------------------------------------------
Summary: 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
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)
