Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-15149

Queries with aliased where predicate > and < , use primary scans instead of expected index scan

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Blocker
    • 4.0.0
    • 4.0.0
    • query
    • Security Level: Public
    • 400-2213
      sabre dataload
      4 gsi indexes, attached screenshot
    • Untriaged
    • Unknown

    Description

      1. load 13M data on sabre-default bucket
      2. create indexes on default - all successful

      3. Most of the expected queries were taking longer to run, so I ran the explain stmts to check - looks like each query was using the primary scan inplace of expected Index scan.
      cbq> select * from system:indexes where keyspace_id="default";
      {
      "requestID": "85f1adb5-4b8d-489a-a4a3-4cbe4b61aa6b",
      "signature":

      { "*": "*" }

      ,
      "results": [
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "cc9643d53917d052", "index_key": [ "`SequenceNumber`" ], "keyspace_id": "default", "name": "seq_num", "namespace_id": "default", "state": "online", "using": "gsi" }

      },
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "ba277bd5832cc61", "index_key": [], "is_primary": true, "keyspace_id": "default", "name": "px_default", "namespace_id": "default", "state": "online", "using": "gsi" }

      },
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "fb6582da11ef61ca", "index_key": [ "((`AirItinerary`.`OriginDestinationOptions`).`OriginDestinationOption`)" ], "keyspace_id": "default", "name": "airportcode", "namespace_id": "default", "state": "pending", "using": "gsi" }

      },
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "f2e31e0f3021497e", "index_key": [ "(((((`AirItineraryPricingInfo`.`PTC_FareBreakdowns`).`PTC_FareBreakdown`).`PassengerFare`).`TotalFare`).`Amount`)" ], "keyspace_id": "default", "name": "amt", "namespace_id": "default", "state": "online", "using": "gsi" }

      }
      ],
      "status": "success",
      "metrics":

      { "elapsedTime": "1.555576513s", "executionTime": "1.555072793s", "resultCount": 4, "resultSize": 1831 }

      }

      cbq> explain select AirItineraryPricingInfo.PTC_FareBreakdowns.PTC_FareBreakdown.PassengerFare.TotalFare.Amount as a123 from default where a123}
      {
      "requestID": "e28feb99-47d0-4c57-a0bf-ea698d44cbec",
      "signature": "json",
      "results": [
      {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "PrimaryScan", "index": "px_default", "keyspace": "default", "namespace": "default", "using": "gsi" }

      ,
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "Fetch", "keyspace": "default", "namespace": "default" }

      ,

      { "#operator": "Filter", "condition": "((250 \u003c (`default`.`a123`)) and ((`default`.`a123`) \u003c 450))" }

      ,
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "as": "a123", "expr": "((((((`default`.`AirItineraryPricingInfo`).`PTC_FareBreakdowns`).`PTC_FareBreakdown`).`PassengerFare`).`TotalFare`).`Amount`)" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]
      },

      { "#operator": "Limit", "expr": "2" }

      ]
      }
      ],
      "status": "success",
      "metrics":

      { "elapsedTime": "2.312823047s", "executionTime": "2.197771563s", "resultCount": 1, "resultSize": 2217 }

      }

      4. The above uses indexscan when I use full field reference instead of the alias
      cbq>

      {.PTC_FareBreakdowns.PTC_FareBreakdown.PassengerFare.TotalFare.Amount>250 and AirItineraryPricingInfo.PTC_FareBreakdowns.PTC_FareBreakdown}

      {
      "requestID": "5483c80b-f471-4616-808b-c1a86cd46554",
      "signature": "json",
      "results": [
      {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "index": "amt",
      "keyspace": "default",
      "limit": 9.223372036854776e+18,
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "High": [ "450" ], "Inclusion": 0, "Low": [ "250" ] }

      ,
      "Seek": null
      }
      ],
      "using": "gsi"
      },
      {
      "#operator": "Parallel",
      "~child": {
      "#operator": "Sequence",
      "~children": [

      { "#operator": "Fetch", "keyspace": "default", "namespace": "default" }

      ,

      { "#operator": "Filter", "condition": "((250 \u003c ((((((`default`.`AirItineraryPricingInfo`).`PTC_FareBreakdowns`).`PTC_FareBreakdown`).`PassengerFare`).`TotalFare`).`Amount`)) and (((((((`default`.`AirItineraryPricingInfo`).`PTC_FareBreakdowns`).`PTC_FareBreakdown`).`PassengerFare`).`TotalFare`).`Amount`) \u003c 450))" }

      ,
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "as": "a123", "expr": "((((((`default`.`AirItineraryPricingInfo`).`PTC_FareBreakdowns`).`PTC_FareBreakdown`).`PassengerFare`).`TotalFare`).`Amount`)" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]
      },

      { "#operator": "Limit", "expr": "2" }

      ]
      }
      ],
      "status": "success",
      "metrics":

      { "elapsedTime": "7.540619ms", "executionTime": "7.310345ms", "resultCount": 1, "resultSize": 3095 }

      }

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          People

            keshav Keshav Murthy
            ketaki Ketaki Gangal (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty