Slow or timeout n1ql query issue

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.

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”));


@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.

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

@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?

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”)

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"
}

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

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"
}

Plan looks fine. Enable profile and check

How can I enable profile in Query Workbench?

Run the query and check Plan Text

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
}

IndexSscan took 5secs

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…

@vsr1 Did you find the reason and clue?

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.

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.

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”;

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