N1QL performance reduces drastically on using ORDER BY


#1

Below is a N1QL query to retrieve data without ORDER BY, which give a response time of around 16ms:

         SELECT epoch FROM sales WHERE companyId=2  LIMIT 3;
        {
            "requestID": "8883934f-dff0-4097-8361-8b870cc6c9cb",
            "signature": {
                "epoch": "json"
            },
            "results": [
                {
                    "epoch": 1.441273895e+09
                },
                {
                    "epoch": 1.441272745e+09
                },
                {
                    "epoch": 1.441271542e+09
                }
            ],
            "status": "success",
            "metrics": {
                "elapsedTime": "16.93128ms",
                "executionTime": "16.781124ms",
                "resultCount": 3,
                "resultSize": 98
            }
        }

The N1QL query to retrieve data with ORDER BY, gives a response time of around 800ms.
SELECT epoch FROM sales WHERE companyId=2 ORDER BY epoch DESC LIMIT 3;
{
“requestID”: “f70ab2c2-4ba8-45c5-a4fa-e0fae6d07dc3”,
“signature”: {
“epoch”: “json”
},
“results”: [
{
“epoch”: 1.441280963e+09
},
{
“epoch”: 1.441280933e+09
},
{
“epoch”: 1.441280898e+09
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “795.476822ms”,
“executionTime”: “795.393153ms”,
“resultCount”: 3,
“resultSize”: 144,
“sortCount”: 10460
}
}

Is it possible to retrieve documents from a bucket in sorted order based on their Document Id without using ORDER BY? If not, how can we improve the performance using ORDER BY?


#2

ORDER BY performance depend on the amount of data we need to sift through to get to the answer. To discover that, simply run the query without limit 3 at the end. Next step to improve is to ask; if you could further limit the amount of rows that we need to work with with additional filters?
We are looking to do more in future in this area but this is the main way to optimize ORDER BY today.
thanks
-cihan


#3

Hi @cihangirb,

I am also facing the same problem.

I have 100000 documents and I am also facing problem with order by clause.

Sometimes I am getting below exception also.

Code - 12015
Message - Index scan timed out - cause: Index scan timed out

The performance is very low with Order By clause.

currently I am using Couch base Version: 4.1.0-5005 Enterprise Edition (build-5005)

Is there any solution for this.

Regards
Mukesh Raghuwanshi


#4

Hello all,

This issue has been fixed in the upcoming release: Optimize ORDER BY + LIMIT.


#5

We will get a preview put out in the next few weeks. pls standby.
-cihan