You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Pritesh Maker (JIRA)" <ji...@apache.org> on 2018/11/11 23:47:00 UTC
[jira] [Updated] (DRILL-6844) Query with ORDER BY DESC on indexed
column does not pick secondary index
[ https://issues.apache.org/jira/browse/DRILL-6844?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pritesh Maker updated DRILL-6844:
---------------------------------
Fix Version/s: 1.15.0
> 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)