Slow or timeout n1ql query issue

Cytown · November 22, 2019, 3:00am

SELECT count(*) as ccc FROM xxx WHERE _type='au' AND src = 'blog' AND create_time >= 1 AND create_time <= 20000000000 AND id IS NOT MISSING LIMIT 101 OFFSET 0

This query return:

[
  {
    "ccc": 2784031
  }
]

Then it’s just 2.7m records.
When query like this:

SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result FROM xxx WHERE _type='au' AND src = 'blog' AND create_time >= 1 AND create_time <= 20000000000 AND id IS NOT MISSING ORDER BY id DESC LIMIT 101 OFFSET 0

It result timeout…
The query explain like this:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "index": "idx_au_blog_createtime_id",
            "index_id": "af8bb3de1d546698",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "xxx",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "20000000000",
                    "inclusion": 3,
                    "low": "1"
                  },
                  {
                    "inclusion": 1,
                    "low": "null"
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "keyspace": "xxx",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "((((((`xxx`.`_type`) = \"au\") and ((`xxx`.`src`) = \"blog\")) and (1 <= (`xxx`.`create_time`))) and ((`xxx`.`create_time`) <= 20000000000)) and ((`xxx`.`id`) is not missing))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`xxx`.`id`)"
                    },
                    {
                      "expr": "(`xxx`.`src`)"
                    },
                    {
                      "expr": "(`xxx`.`src_id`)"
                    },
                    {
                      "expr": "(`xxx`.`author`)"
                    },
                    {
                      "expr": "(`xxx`.`status`)"
                    },
                    {
                      "expr": "(`xxx`.`title`)"
                    },
                    {
                      "as": "body",
                      "expr": "substr0((`xxx`.`body`), 0, 500)"
                    },
                    {
                      "expr": "(`xxx`.`create_time`)"
                    },
                    {
                      "expr": "(`xxx`.`up_time`)"
                    },
                    {
                      "expr": "(`xxx`.`memo`)"
                    },
                    {
                      "expr": "(`xxx`.`history`)"
                    },
                    {
                      "expr": "(`xxx`.`result`)"
                    }
                  ]
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Order",
        "limit": "101",
        "sort_terms": [
          {
            "desc": true,
            "expr": "(`xxx`.`id`)"
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "101"
      },
      {
        "#operator": "FinalProject"
      }
    ]
  },
  "text": "SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result FROM `xxx` WHERE _type='au' AND src = 'blog' AND create_time >= 1 AND create_time <= 20000000000 AND id IS NOT MISSING ORDER BY id DESC LIMIT 101 OFFSET 0"
}

The idx_au_blog_createtime_id is:

CREATE INDEX `idx_au_blog_createtime_id` ON `xxx`(`create_time`,`id` DESC) WHERE ((`_type` = "au") and (`src` = "blog"))

Can someone check why the query is so slow or timeout and how to fix this?

Thanks a lot for any help.

vsr1 · November 22, 2019, 4:04am

As you have so many qualified items. Use Index ORDER

CREATE INDEX idx_au_blog_createtime_id ON xxx( id DESC, create_time) WHERE ((_type = “au”) and (src = “blog”));

Cytown · November 22, 2019, 4:24am

@vsr1 That’s so cool, it works!

I have another question, if there are many query items, such as src_id, create_time, author, status, title, body etc, should I use a index to contains all of them or create many indexes for each item, which is better? I don’t query them all, but query some of them.

Thanks.

vsr1 · November 22, 2019, 4:34am

Check rules https://blog.couchbase.com/create-right-index-get-right-performance/

CREATE INDEX  `idx_au_blog_createtime_id`  ON  `xxx` ( id DESC,  `create_time`, src_id, author ) WHERE (( `_type`  = “au”) and ( `src`  = “blog”));

SELECT *
FROM ` xxx` 
WHERE _type='au' AND src = 'blog' 
                     AND create_time >= 1 AND create_time <= 20000000000 
                     AND id IS NOT MISSING  AND author > "xyz"
ORDER BY id DESC 
LIMIT 101 
OFFSET 0

in above query u can add AND src_id IS NOT MISSING then author also passed to IndexScan

Cytown · November 25, 2019, 4:03am

@vsr1 strange issue here:

I have an index:

CREATE INDEX idx_au_src_author_id_DESC ON xxx(id DESC,src,author) WHERE (_type = “au”)

Then I query this, returns in few ms:

SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result
FROM xxx WHERE _type=“au” AND src = “blog” AND author like “mm%” and id is not missing ORDER BY id DESC LIMIT 101 OFFSET 0

I should think the following query should result at least the same as above, but actually change to 5 or more seconds!

SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result
FROM xxx WHERE _type=“au” AND src = “blog” AND author like “mm%” and id is not missing ORDER BY id DESC LIMIT 101 OFFSET 0

Could you please tell me the reason and how to fix this?

vsr1 · November 25, 2019, 4:14am

Both queries are same. post the explain.
If you want include src as index key you can move to leading
CREATE INDEX idx_au_src_author_id_DESC ON xxx (src, id DESC, author ) WHERE ( _type = “au”)

Cytown · November 25, 2019, 4:23am

Still the same result:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "OrderedIntersectScan",
            "limit": "101",
            "scans": [
              {
                "#operator": "IndexScan3",
                "index": "idx_au_blog_id",
                "index_id": "9b9b5be931e149c4",
                "index_order": [
                  {
                    "desc": true,
                    "keypos": 0
                  }
                ],
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "xxx",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "inclusion": 1,
                        "low": "null"
                      }
                    ]
                  }
                ],
                "using": "gsi"
              },
              {
                "#operator": "IndexScan3",
                "index": "idx_au_src_author_id_DESC2",
                "index_id": "d5b0436429df213e",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "xxx",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"blog\"",
                        "inclusion": 3,
                        "low": "\"blog\""
                      },
                      {
                        "inclusion": 1,
                        "low": "null"
                      },
                      {
                        "high": "\"qq_44909806\"",
                        "inclusion": 3,
                        "low": "\"qq_44909806\""
                      }
                    ]
                  }
                ],
                "using": "gsi"
              }
            ]
          },
          {
            "#operator": "Fetch",
            "keyspace": "xxx",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "maxParallelism": 1,
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(((((`xxx`.`_type`) = \"au\") and ((`xxx`.`src`) = \"blog\")) and ((`xxx`.`author`) = \"qq_44909806\")) and ((`xxx`.`id`) is not missing))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`xxx`.`id`)"
                    },
                    {
                      "expr": "(`xxx`.`src`)"
                    },
                    {
                      "expr": "(`xxx`.`src_id`)"
                    },
                    {
                      "expr": "(`xxx`.`author`)"
                    },
                    {
                      "expr": "(`xxx`.`status`)"
                    },
                    {
                      "expr": "(`xxx`.`title`)"
                    },
                    {
                      "as": "body",
                      "expr": "substr0((`xxx`.`body`), 0, 500)"
                    },
                    {
                      "expr": "(`xxx`.`create_time`)"
                    },
                    {
                      "expr": "(`xxx`.`up_time`)"
                    },
                    {
                      "expr": "(`xxx`.`memo`)"
                    },
                    {
                      "expr": "(`xxx`.`history`)"
                    },
                    {
                      "expr": "(`xxx`.`result`)"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "101"
      }
    ]
  },
  "text": "SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result\nFROM `xxx`  WHERE _type=\"au\" AND src = \"blog\" AND author = \"qq_44909806\" and id is not missing ORDER BY id DESC LIMIT 101 OFFSET 0"
}

vsr1 · November 25, 2019, 4:25am

drop this index idx_au_blog_id or USE INDEX (idx_au_src_author_id_DESC2).Avoid Intersect Scan

Cytown · November 25, 2019, 4:40am

Still same, 5-7 seconds:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "IndexScan3",
            "index": "idx_au_src_author_id_DESC2",
            "index_id": "d5b0436429df213e",
            "index_order": [
              {
                "keypos": 0
              },
              {
                "desc": true,
                "keypos": 1
              }
            ],
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "xxx",
            "limit": "101",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"blog\"",
                    "inclusion": 3,
                    "low": "\"blog\""
                  },
                  {
                    "inclusion": 1,
                    "low": "null"
                  },
                  {
                    "high": "\"qq_44909806\"",
                    "inclusion": 3,
                    "low": "\"qq_44909806\""
                  }
                ]
              }
            ],
            "using": "gsi"
          },
          {
            "#operator": "Fetch",
            "keyspace": "xxx",
            "namespace": "default"
          },
          {
            "#operator": "Parallel",
            "maxParallelism": 1,
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "Filter",
                  "condition": "(((((`xxx`.`_type`) = \"au\") and ((`xxx`.`src`) = \"blog\")) and ((`xxx`.`author`) = \"qq_44909806\")) and ((`xxx`.`id`) is not missing))"
                },
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "expr": "(`xxx`.`id`)"
                    },
                    {
                      "expr": "(`xxx`.`src`)"
                    },
                    {
                      "expr": "(`xxx`.`src_id`)"
                    },
                    {
                      "expr": "(`xxx`.`author`)"
                    },
                    {
                      "expr": "(`xxx`.`status`)"
                    },
                    {
                      "expr": "(`xxx`.`title`)"
                    },
                    {
                      "as": "body",
                      "expr": "substr0((`xxx`.`body`), 0, 500)"
                    },
                    {
                      "expr": "(`xxx`.`create_time`)"
                    },
                    {
                      "expr": "(`xxx`.`up_time`)"
                    },
                    {
                      "expr": "(`xxx`.`memo`)"
                    },
                    {
                      "expr": "(`xxx`.`history`)"
                    },
                    {
                      "expr": "(`xxx`.`result`)"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Limit",
        "expr": "101"
      }
    ]
  },
  "text": "SELECT id, src, src_id, author, status, title, SUBSTR(body, 0, 500) as body, create_time, up_time, memo, history, result\nFROM `xxx` USE INDEX (idx_au_src_author_id_DESC2) WHERE _type=\"au\" AND src = \"blog\" AND author = \"qq_44909806\" and id is not missing ORDER BY id DESC LIMIT 101 OFFSET 0"
}

vsr1 · November 25, 2019, 4:44am

Plan looks fine. Enable profile and check

Cytown · November 25, 2019, 4:48am

How can I enable profile in Query Workbench?

vsr1 · November 25, 2019, 4:48am

Run the query and check Plan Text

Cytown · November 25, 2019, 4:50am

Please check:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "2.316µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "5.687µs",
        "servTime": "889.951µs"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:xxx",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "2.21µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 2,
              "execTime": "177.932µs",
              "kernTime": "140ns",
              "state": "running"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 7,
                  "#phaseSwitches": 31,
                  "execTime": "45.517µs",
                  "kernTime": "16.74µs",
                  "servTime": "5.468333201s"
                },
                "index": "idx_au_src_author_id_DESC2",
                "index_id": "d5b0436429df213e",
                "index_order": [
                  {
                    "keypos": 0
                  },
                  {
                    "desc": true,
                    "keypos": 1
                  }
                ],
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "xxx",
                "limit": "101",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"blog\"",
                        "inclusion": 3,
                        "low": "\"blog\""
                      },
                      {
                        "inclusion": 1,
                        "low": "null"
                      },
                      {
                        "high": "\"qq_44909806\"",
                        "inclusion": 3,
                        "low": "\"qq_44909806\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:05.4683",
                "#time_absolute": 5.468378718
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 7,
                  "#itemsOut": 7,
                  "#phaseSwitches": 33,
                  "execTime": "89.585µs",
                  "kernTime": "5.468451927s",
                  "servTime": "430.876µs"
                },
                "keyspace": "xxx",
                "namespace": "default",
                "#time_normal": "00:00.0005",
                "#time_absolute": 0.000520461
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 2,
                  "execTime": "311.662µs",
                  "kernTime": "264ns",
                  "state": "running"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 7,
                      "#itemsOut": 7,
                      "#phaseSwitches": 31,
                      "execTime": "259.811µs",
                      "kernTime": "5.468992248s"
                    },
                    "condition": "(((((`xxx`.`_type`) = \"au\") and ((`xxx`.`src`) = \"blog\")) and ((`xxx`.`author`) = \"qq_44909806\")) and ((`xxx`.`id`) is not missing))",
                    "#time_normal": "00:00.0002",
                    "#time_absolute": 0.000259811
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 7,
                      "#itemsOut": 7,
                      "#phaseSwitches": 28,
                      "execTime": "439.128µs",
                      "kernTime": "5.469202053s"
                    },
                    "result_terms": [
                      {
                        "expr": "(`xxx`.`id`)"
                      },
                      {
                        "expr": "(`xxx`.`src`)"
                      },
                      {
                        "expr": "(`xxx`.`src_id`)"
                      },
                      {
                        "expr": "(`xxx`.`author`)"
                      },
                      {
                        "expr": "(`xxx`.`status`)"
                      },
                      {
                        "expr": "(`xxx`.`title`)"
                      },
                      {
                        "as": "body",
                        "expr": "substr0((`xxx`.`body`), 0, 500)"
                      },
                      {
                        "expr": "(`xxx`.`create_time`)"
                      },
                      {
                        "expr": "(`xxx`.`up_time`)"
                      },
                      {
                        "expr": "(`xxx`.`memo`)"
                      },
                      {
                        "expr": "(`xxx`.`history`)"
                      },
                      {
                        "expr": "(`xxx`.`result`)"
                      }
                    ],
                    "#time_normal": "00:00.0004",
                    "#time_absolute": 0.000439128
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 7,
                      "#itemsOut": 7,
                      "#phaseSwitches": 15,
                      "execTime": "7.263µs"
                    },
                    "#time_normal": "00:00.0000",
                    "#time_absolute": 0.000007263
                  }
                ],
                "#time_normal": "00:00.0003",
                "#time_absolute": 0.000311662
              }
            ],
            "#time_normal": "00:00.0001",
            "#time_absolute": 0.00017793199999999998
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 7,
              "#itemsOut": 7,
              "#phaseSwitches": 22,
              "execTime": "6.73µs",
              "kernTime": "106.439µs"
            },
            "expr": "101",
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000006730000000000001
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.00000221
      },
      "#time_normal": "00:00.0008",
      "#time_absolute": 0.000895638
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 7,
        "#itemsOut": 7,
        "#phaseSwitches": 31,
        "execTime": "6.422µs",
        "kernTime": "5.470692709s"
      },
      "#time_normal": "00:00.0000",
      "#time_absolute": 0.000006422
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.1-2037-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.000002316
}

vsr1 · November 25, 2019, 4:56am

IndexSscan took 5secs

Cytown · November 25, 2019, 5:08am

Yes, it’s just 7 records returned and it’s a = operation, but if I use like operation and with “mm%”, it only takes about 500ms or less…

Cytown · November 25, 2019, 6:53am

@vsr1 Did you find the reason and clue?

vsr1 · November 25, 2019, 2:11pm

May be the way index is stored as we are scanning all id values due to leading key(user for order) vs author leading key. It all based on data and size.

Cytown · November 26, 2019, 6:37am

The question is why like works but = not work… The total db is about 6 million documents, I don’t think it’s a big database.

vsr1 · November 26, 2019, 6:55am

Index Scans are range and it must scan all ranges of ids , it takes time. Use following index with USE INDEX

CREATE INDEX ix1 ON xxx ( src, author , id DESC) WHERE _type = “au”;

Cytown · November 26, 2019, 8:14am

Yes, it works with use index hint… I don’t know why couchbase’s index selector always find the wrong index… It’s feature?

Topic		Replies	Views
N1QL queries very slow Couchbase Server query , java , n1ql	6	2272	August 25, 2016
N1QL: Count query sometimes slow, sometimes not with different possible indexes Couchbase Server n1ql	10	1690	November 9, 2017
N1QL query count si so slow SQL++	3	785	January 26, 2022
N1QL query is getting timeout out Couchbase Server n1ql	6	4103	July 17, 2017
Strange "delay" on index, when using simple N1QL query SQL++ query , n1ql	8	2253	June 14, 2016

Slow or timeout n1ql query issue

Related topics