Slow query speed even with intersect scan of two indexes, high query time variability

SQL++
1

This query time ranges from 1 sec to 30 sec depending upon the target ‘targetuser’ some users with no data generate the longest query times.

SELECT *
FROM vigorlending
WHERE (`globalStats`.`bailoutupuser` = "targetuser"
        OR `globalStats`.`bailoutuser` = "targetuser")
    AND event="afterfull"
    AND type="stateUpdate"
ORDER BY block.num DESC

I created some indexes which are used in the query using an intersect scan ().

  CREATE INDEX `type` ON `vigorlending`(`type`)
  CREATE INDEX `event` ON `vigorlending`(`event`)

I created other indexes more specifically for this query but they don’t seem to be used.

CREATE INDEX `bailoutupuser` ON `vigorlending`(`globalStats`.`bailoutupuser`)
     CREATE INDEX `bailoutuser` ON `vigorlending`(`globalStats`.`bailoutuser`)
    CREATE INDEX `userbailout` \
    ON `vigorlending`(`globalStats`.`bailoutuser`,`globalStats`.`bailoutupuser`)\
    WHERE`event`=`afterfull`\
    AND `type`=`stateUpdate`

Plan text for query above

{
"#operator": "Sequence",
"~children": [
    {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "IntersectScan",
                "scans": [
                    {
                        "#operator": "IndexScan3",
                        "index": "event",
                        "index_id": "b62a3ab7211b8455",
                        "index_projection": {
                            "primary_key": true
                        },
                        "keyspace": "vigorlending",
                        "namespace": "default",
                        "spans": [
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "high": "\"afterfull\"",
                                        "inclusion": 3,
                                        "low": "\"afterfull\""
                                    }
                                ]
                            }
                        ],
                        "using": "gsi"
                    },
                    {
                        "#operator": "IndexScan3",
                        "index": "type",
                        "index_id": "fa19a2417d223998",
                        "index_projection": {
                            "primary_key": true
                        },
                        "keyspace": "vigorlending",
                        "namespace": "default",
                        "spans": [
                            {
                                "exact": true,
                                "range": [
                                    {
                                        "high": "\"stateUpdate\"",
                                        "inclusion": 3,
                                        "low": "\"stateUpdate\""
                                    }
                                ]
                            }
                        ],
                        "using": "gsi"
                    }
                ]
            },
            {
                "#operator": "Fetch",
                "keyspace": "vigorlending",
                "namespace": "default"
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "condition": "((((((`vigorlending`.`globalStats`).`bailoutupuser`) = \"netdanieleee\") or (((`vigorlending`.`globalStats`).`bailoutuser`) = \"netdanieleee\")) and ((`vigorlending`.`event`) = \"afterfull\")) and ((`vigorlending`.`type`) = \"stateUpdate\"))"
                        },
                        {
                            "#operator": "InitialProject",
                            "result_terms": [
                                {
                                    "expr": "self",
                                    "star": true
                                }
                            ]
                        }
                    ]
                }
            }
        ]
    },
    {
        "#operator": "Order",
        "sort_terms": [
            {
                "desc": true,
                "expr": "((`vigorlending`.`block`).`num`)"
            }
        ]
    },
    {
        "#operator": "FinalProject"
    }
]

}

2

As you are looking same user in both the fields Instead of OR use ARRAY index (converting both fields in to ARRAY and look inside the array)

AVOID IntersectScans by providing USE INDEX hint.

Also index WHERE clause values must be single or double quote (not back-ticks. other wise it considers field in the document) ex: afterfull, stateUpdate

Use EXPLAIN and checkout spans section and understand what values it passed to IndexScan. More exact values passed from predicate will perform better.

   CREATE INDEX ix1 ON `vigorlending`(event,  DISTINCT [`globalStats`.`bailoutuser`,`globalStats`.`bailoutupuser`],  block.num DESC)
        WHERE  `type`="stateUpdate";

SELECT *
FROM vigorlending
WHERE  ANY v IN  [`globalStats`.`bailoutuser`,`globalStats`.`bailoutupuser`] SATISFIES v = "targetuser" END
    AND event="afterfull"
    AND type="stateUpdate"
ORDER BY block.num DESC;

https://index-advisor.couchbase.com/indexadvisor/#1

Response from this post Identify top N queries in couchbase has details how to identify which part of query taking time.

1 Like
3

Thanks your solution improved my query speeds dramatically. I have a follow-up question. When creating the index is there a reason you did not include AND event=“afterfull”?

Thanks also for the reference link that’s useful for me.

4

If you think your queries doesn’t go across different event sure you can include it.

1 Like