Irregular n1ql performance

SQL++
1

Hi,

We are seeing irregular n1ql performance with the same query. Some of them are super fast, but some of them takes 600ms+. As some of them are super fast, I don’t think this is index problem. I would appreciate any suggestions.

Query:

select *from catalog bucket1 where bucket1.clientId = 'value' and bucket1.form = 'value' and bucket1.values.slug= 'value' ;

Explain:

{
    "requestID": "0fc748f0-8fc9-41c8-b250-8a8f8b37d3c8",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "IntersectScan",
                    "scans": [
                        {
                            "#operator": "IndexScan",
                            "index": "idx_creative_form",
                            "keyspace": "catalog",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "\"creative\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"creative\""
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        },
                        {
                            "#operator": "IndexScan",
                            "index": "idx_creative_slug",
                            "keyspace": "catalog",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "\"\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"\""
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        },
                        {
                            "#operator": "IndexScan",
                            "index": "idx_creative_clientId",
                            "keyspace": "catalog",
                            "namespace": "default",
                            "spans": [
                                {
                                    "Range": {
                                        "High": [
                                            "\"\""
                                        ],
                                        "Inclusion": 3,
                                        "Low": [
                                            "\"\""
                                        ]
                                    }
                                }
                            ],
                            "using": "gsi"
                        }
                    ]
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "as": "bucket1",
                                "keyspace": "catalog",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((((`bucket1`.`clientId`) = \"\") and ((`bucket1`.`form`) = \"creative\")) and (((`bucket1`.`values`).`slug`) = \"\"))"
                            },
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "self",
                                        "star": true
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.455053ms",
        "executionTime": "9.414738ms",
        "resultCount": 1,
        "resultSize": 4320
    }
}

These are my test result in cbq.

cbq> 
{
    "requestID": "844276df-5d2f-44e4-96a7-97531f73236f",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "11.555695ms",
        "executionTime": "11.505606ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> 
{
    "requestID": "7effd6a7-3800-4d43-b76b-60300130d7c6",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "194.256269ms",
        "executionTime": "194.173828ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> 
{
    "requestID": "c8dc8596-ff0e-4321-807d-a75753948022",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "31.065162ms",
        "executionTime": "31.010268ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> 
{
    "requestID": "824c8da6-d175-4c84-aa0b-d2475af5966e",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "8.138917ms",
        "executionTime": "8.100675ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> hidden
{
    "requestID": "02cf4cf5-fdb4-4fbc-a4e7-c193453f936e",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "8.808598ms",
        "executionTime": "8.749748ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> hidden
{
    "requestID": "edfd6e3b-f84d-4a11-8278-942987f1898c",
    "signature": {
        "id": "json"
    },

    "status": "success",
    "metrics": {
        "elapsedTime": "401.523652ms",
        "executionTime": "401.428373ms",
        "resultCount": 1,
        "resultSize": 57
    }
}

cbq> hidden
{
    "requestID": "f5b7373f-f13f-48c8-be62-44ea36b4028b",
    "signature": {
        "id": "json"
    },
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "617.373537ms",
        "executionTime": "617.336218ms",
        "resultCount": 1,
        "resultSize": 57
    }
}
2

A couple of things:

  • Are you using the same values for each run, or different values?
  • If different values, are the result counts different?
  • Can you create an index with all three predicates, clientId, form, and values.slug? The most selective key should come first.
  • Finally, can you SELECT specific fields instead of SELECT *? If so, can you add the SELECT fields to the index?
3

Hi geraldss,

  • For this particular result, I used the same values. The reason I ran this test was that we were seeing the same result from our pre-prod test. Pre-prod used different values.
  • I will try and post the result as soon as possible
  • I really can’t. I need the whole document to hydrate my object. Would it help to index meta().id as a covering index then do get() separately?