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

n1ql "max()" is much slower than "min()" operation

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Duplicate
    • Major
    • 5.0.0
    • 4.5.0
    • query
    • None

    Description

      n1ql "max()" is much slower than "min()" operation on couchbase 4.5 preview edition.

      the detail:

      SELECT min(time_first) FROM statis_user_success
      {{{
      "requestID": "c5e8b6df-9f0e-4690-b3a7-350d07b1e2e4",
      "signature":

      { "$1": "json" }

      ,
      "results":
      {
      "0":

      { "$1": 1465663242 }

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

      { "elapsedTime": "7.153457ms", "executionTime": "7.100002ms", "resultCount": 1, "resultSize": 40 }

      }}}

      SELECT max(time_first) FROM statis_user_success
      {{{
      "requestID": "c5e8b6df-9f0e-4690-b3a7-350d07b1e2e4",
      "signature":

      { "$1": "json" }

      ,
      "results":
      {
      "0":

      { "$1": 1465663242 }

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

      { "elapsedTime": "43860.153457ms", "executionTime": "43860.100002ms", "resultCount": 1, "resultSize": 40 }

      }}}

      SELECT * FROM statis_user_success limit 3
      {{[
      {
      "statis_user_success":

      { "uid": "EZS108-02", "time_first": 1465678128 }

      },
      {
      "statis_user_success":

      { "uid": "PZM215-02", "time_first": 1465722317 }

      },
      {
      "statis_user_success":

      { "uid": "H03217-02", "time_first": 1465678130 }

      }
      ]}}

      SELECT * FROM system:indexes where keyspace_id='statis_user_success' and name='idx_time_first'
      {{[
      {
      "indexes":

      { "datastore_id": "http://127.0.0.1:8091", "id": "9d8f7238fa7276a5", "index_key": [ "`time_first`" ], "keyspace_id": "statis_user_success", "name": "idx_time_first", "namespace_id": "default", "state": "online", "using": "gsi" }

      }
      ]}}

      explain select min(time_first) from statis_user_success
      {{[
      {
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "covers": [
      "cover ((`statis_user_success`.`time_first`))",
      "cover ((meta(`statis_user_success`).`id`))"
      ],
      "index": "idx_time_first",
      "index_id": "9d8f7238fa7276a5",
      "keyspace": "statis_user_success",
      "limit": "1",
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "Inclusion": 0, "Low": [ "null" ] }

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

      { "#operator": "InitialGroup", "aggregates": [ "min(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

      ]
      }
      },

      { "#operator": "IntermediateGroup", "aggregates": [ "min(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

      ,

      { "#operator": "FinalGroup", "aggregates": [ "min(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

      ,
      {
      "#operator": "Parallel",
      "maxParallelism": 1,
      "~child": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "InitialProject",
      "result_terms": [

      { "expr": "min(cover ((`statis_user_success`.`time_first`)))" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]
      },
      "text": "select min(time_first) from statis_user_success"
      }
      ]}}

      explain select max(time_first) from statis_user_success
      {{[
      {
      "plan": {
      "#operator": "Sequence",
      "~children": [
      {
      "#operator": "IndexScan",
      "covers": [
      "cover ((`statis_user_success`.`time_first`))",
      "cover ((meta(`statis_user_success`).`id`))"
      ],
      "index": "idx_time_first",
      "index_id": "9d8f7238fa7276a5",
      "keyspace": "statis_user_success",
      "namespace": "default",
      "spans": [
      {
      "Range":

      { "Inclusion": 0, "Low": [ "null" ] }

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

      { "#operator": "InitialGroup", "aggregates": [ "max(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

      ]
      }
      },

      { "#operator": "IntermediateGroup", "aggregates": [ "max(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

      ,

      { "#operator": "FinalGroup", "aggregates": [ "max(cover ((`statis_user_success`.`time_first`)))" ], "group_keys": [] }

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

      { "expr": "max(cover ((`statis_user_success`.`time_first`)))" }

      ]
      },

      { "#operator": "FinalProject" }

      ]
      }
      }
      ]
      },
      "text": "statis_user_success"
      }
      ]}}

      Attachments

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

        Activity

          People

            Prerna.Manaktala Prerna Manaktala (Inactive)
            kexianbin Malcolm Ke Win
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty