N1QL is very slow with "order by" clause

SQL++
1

Hi,
I’m using couchbase server 4.5.0-2601 and found out that the N1QL is very slow with order by clause regardless I already create index for it. Detail:

  • I have 130k data on “default” database
  • I created index for “postingDate” :
    Create index pd_idx on default(postingDate)
  • The query without “order by” run very fast ( < 1 seconds)
    select * from default where postingDate > 20160801 limit 10
  • But the query with “order by” run very slow ( > 25 seconds)
    select * from default where postingDate > 20160801 order by postingDate DESC limit 10

Can you help ?[quote=“tinygipxy, post:1, topic:9828, full:true”]
Hi,
I’m using couchbase server 4.5.0-2601 and found out that the N1QL is very slow with order by clause regardless I already create index for it. Detail:

  • I have 130k data on “default” database
  • I created index for “postingDate” :
    Create index pd_idx on default(postingDate)
  • The query without “order by” run very fast ( < 1 seconds)
    select * from default where postingDate > 20160801 limit 10
  • But the query with “order by” run very slow ( > 25 seconds)
    select * from default where postingDate > 20160801 order by postingDate limit 10

Can you help ?
[/quote]

2

hi @tinygipxy,
this looks unusual. Can you provide li’l more info.

I have 130k data on “default” database

what is the number of documents, size of each document? If possible, provide a sample document.

  1. post EXPLAIN output of the SELECT query.
    what is the size of result set, in bytes, and number of documents?

  2. post the metrics collected in system:completed_requests for this query.
    SELECT * FROM system:completed_requests ;

  3. details of the cluster setup? how many nodes? are you running all services (query, index, data) on same node? configured memory & cores etc?

-Prasad

3

Thanks Prasad,

For your detail:

  1. My database is just simple database running on only one node Centos 6.8 (dedicated server for couchbase, 4 cores, 8GB RAM, right now free 1GB ram) with exactly 261334 records (select count(*) from default), each record about 3KB

  2. I also found out the query is only very slow with DESC condition when ordering, this is the completed_request:

2.1) With DESC: 19s

"completed_requests":  {
  "ClientContextID": "f69c6ba7-9c68-44a6-adb6-ea6dd92b729c",
  "ElapsedTime": "19.88647505s",
  "ErrorCount": 0,
  "PhaseCounts": {
    "Fetch": 249307,
    "IndexScan": 249307,
    "Sort": 249308
  },
  "PhaseOperators": {
    "Fetch": 1,
    "IndexScan": 1,
    "Sort": 1
  },
  "RequestId": "9f30aea7-ae8e-46f5-9822-cf27351493dc",
  "ResultCount": 1,
  "ResultSize": 3107,
  "ServiceTime": "19.886412997s",
  "State": "completed",
  "Statement": "select * from default where ngayDangTin > 20140801 order by ngayDangTin desc limit 1",
  "Time": "2016-09-03 10:31:57.184899229 +0700 ICT"
}

2.2) Without DESC: very fast 31.12ms

My N1QL: “select * from default where ngayDangTin > 20140801 order by ngayDangTin limit 2”, but I dont’ know why completed_request does not capture it.

  1. EXPLAIN of query:

3.1) With DESC

[
 {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan",
              "index": "ngayDangTin_idx",
              "index_id": "683705dc9f6b6507",
              "keyspace": "default",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 0,
                    "Low": [
                      "20140801"
                    ]
                  }
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Parallel",
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Fetch",
                    "keyspace": "default",
                    "namespace": "default"
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(20140801 < (`default`.`ngayDangTin`))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Order",
          "limit": "2",
          "sort_terms": [
            {
              "desc": true,
              "expr": "(`default`.`ngayDangTin`)"
            }
          ]
        },
        {
          "#operator": "Limit",
          "expr": "2"
        },
        {
          "#operator": "FinalProject"
        }
      ]
    },
    "text": "select * from default where ngayDangTin > 20140801 order by ngayDangTin DESC limit 2"
  }
]

3.2) Without DESC:

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "IndexScan",
              "index": "ngayDangTin_idx",
              "index_id": "683705dc9f6b6507",
              "keyspace": "default",
              "limit": "2",
              "namespace": "default",
              "spans": [
                {
                  "Range": {
                    "Inclusion": 0,
                    "Low": [
                      "20140801"
                    ]
                  }
                }
              ],
              "using": "gsi"
            },
            {
              "#operator": "Parallel",
              "maxParallelism": 1,
              "~child": {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "Fetch",
                    "keyspace": "default",
                    "namespace": "default"
                  },
                  {
                    "#operator": "Filter",
                    "condition": "(20140801 < (`default`.`ngayDangTin`))"
                  },
                  {
                    "#operator": "InitialProject",
                    "result_terms": [
                      {
                        "expr": "self",
                        "star": true
                      }
                    ]
                  }
                ]
              }
            }
          ]
        },
        {
          "#operator": "Limit",
          "expr": "2"
        },
        {
          "#operator": "FinalProject"
        }
      ]
    },
    "text": "select * from default where ngayDangTin > 20140801 order by ngayDangTin limit 2"
  }
]
4

Hi, anybody can help ?

5

I think this is bug of 4.5, anybody know, does this work on 4.1 ?