Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-6844

Query with ORDER BY DESC on indexed column does not pick secondary index

    XMLWordPrintableJSON

    Details

      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

            Activity

              People

              • Assignee:
                hanu.ncr Hanumath Rao Maduri
                Reporter:
                hanu.ncr Hanumath Rao Maduri
                Reviewer:
                Aman Sinha
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: