SELECT as subselect doesn't use indexes

SQL++
1

I have the following query:

select ticketnumbertext from ticketingsystem ti where ti.form = "frmFehlerbild" and parentdocunid in (select `$_meta`.unid from ticketingsystem where ticketownergroup = "124" and ticketstatusalias <= "10")

which only uses the indexes “ticketingsystem.parentdocunid” and “ticketingsystem.form”, but not the indexes “ticketingsystem.ticketingsystem_ticketownergroup_alias10” and “ticketingsystem.ticketstatusalias” which I created for the ticketownergroup and ticketstatusalias fields.

When I execute the subselect only

select `$_meta`.unid from ticketingsystem where ticketownergroup = "124" and ticketstatusalias <= "10"

the indexes are used as they should. So it seems that for the subselect simply no index is used.

Indexes:

parentdocunid:

CREATE INDEX parentdocunid ON ticketingsystem(parentdocunid)

form:

CREATE INDEX `form` ON `ticketingsystem`(`form`)

ticketownergroup:

CREATE INDEX `ticketingsystem_ticketownergroup_alias10` ON `ticketingsystem`(`ticketownergroup`) WHERE (`ticketstatusalias` <= "10")

ticketstatusalias:

CREATE INDEX `ticketstatusalias` ON `ticketingsystem`(`ticketstatusalias`)

Plan:

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 2,
    "kernTime": "14.1145329s"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 4,
        "kernTime": "14.1135345s",
        "servTime": "998.4µs"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:ticketingsystem",
            "Priv": 7
          },
          {
            "Target": ":ticketingsystem",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 3,
          "kernTime": "14.1135345s"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 2,
              "kernTime": "14.1125342s"
            },
            "~children": [
              {
                "#operator": "IntersectScan",
                "#stats": {
                  "#itemsIn": 156868,
                  "#itemsOut": 80743,
                  "#phaseSwitches": 475223,
                  "execTime": "328.0307ms",
                  "kernTime": "13.5194737s"
                },
                "scans": [
                  {
                    "#operator": "IndexScan2",
                    "#stats": {
                      "#itemsOut": 80743,
                      "#phaseSwitches": 322977,
                      "execTime": "433.0675ms",
                      "kernTime": "12.596363s",
                      "servTime": "124.9988ms"
                    },
                    "index": "form",
                    "index_id": "58a00a41517cdea4",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "ticketingsystem",
                    "namespace": "default",
                    "spans": [
                      {
                        "exact": true,
                        "range": [
                          {
                            "high": "\"frmFehlerbild\"",
                            "inclusion": 3,
                            "low": "\"frmFehlerbild\""
                          }
                        ]
                      }
                    ],
                    "using": "gsi",
                    "#time_normal": "00:00.5580",
                    "#time_absolute": 0.5580663
                  },
                  {
                    "#operator": "IndexScan2",
                    "#stats": {
                      "#itemsOut": 76125,
                      "#phaseSwitches": 304504,
                      "execTime": "342.0447ms",
                      "kernTime": "12.7153483s",
                      "servTime": "97.0363ms"
                    },
                    "index": "parentdocunid",
                    "index_id": "2e28bc33a174f434",
                    "index_projection": {
                      "primary_key": true
                    },
                    "keyspace": "ticketingsystem",
                    "namespace": "default",
                    "spans": [
                      {
                        "range": [
                          {
                            "inclusion": 0,
                            "low": "null"
                          }
                        ]
                      }
                    ],
                    "using": "gsi",
                    "#time_normal": "00:00.4390",
                    "#time_absolute": 0.43908099999999994
                  }
                ],
                "#time_normal": "00:00.3280",
                "#time_absolute": 0.3280307
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 80743,
                  "#itemsOut": 80743,
                  "#phaseSwitches": 323295,
                  "execTime": "237.9284ms",
                  "kernTime": "565.0202ms",
                  "servTime": "13.2875838s"
                },
                "as": "ti",
                "keyspace": "ticketingsystem",
                "namespace": "default",
                "#time_normal": "00:13.5255",
                "#time_absolute": 13.5255122
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 5,
                  "kernTime": "14.1125342s"
                },
                "~children": [
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 80743,
                      "#phaseSwitches": 161491,
                      "execTime": "6.8986236s",
                      "kernTime": "7.2139106s"
                    },
                    "condition": "(((`ti`.`form`) = \"frmFehlerbild\") and ((`ti`.`parentdocunid`) in (select ((`ticketingsystem`.`$_meta`).`unid`) from `default`:`ticketingsystem` where (((`ticketingsystem`.`ticketownergroup`) = \"124\") and ((`ticketingsystem`.`ticketstatusalias`) <= \"10\")))))",
                    "#time_normal": "00:06.8986",
                    "#time_absolute": 6.8986236
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#phaseSwitches": 5,
                      "kernTime": "14.1125342s"
                    },
                    "result_terms": [
                      {
                        "expr": "(`ti`.`ticketnumbertext`)"
                      }
                    ]
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#phaseSwitches": 7,
                      "kernTime": "14.1125342s"
                    }
                  }
                ]
              }
            ]
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#phaseSwitches": 7,
              "execTime": "1.0003ms",
              "kernTime": "14.1125342s"
            },
            "expr": "10",
            "#time_normal": "00:00.0010",
            "#time_absolute": 0.0010003
          }
        ]
      },
      "#time_normal": "00:00.0009",
      "#time_absolute": 0.0009984
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#phaseSwitches": 7,
        "kernTime": "14.1145329s"
      }
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "5.0.0-3519-enterprise"
  ]
}
2

Hi @jbuss,

You may want to post N1QL questions into https://www.couchbase.com/forums/c/n1ql in the future.

I’m going to tag @vsr1 to see if they’ll take a look.

1 Like
3

You should use composite indexes. Try the following indexes and also modified query to work correctly
(Sub query requires RAW other wise it produces array of OBJECTS vs ARRAY of values), parentdocunid is value it will not match objects.

CREATE INDEX idx_ticketownergroup ticketingsystem(ticketownergroup, ticketstatusalias, `$_meta`.unid);
CREATE INDEX idx_ticketfrom ticketingsystem(form, parentdocunid, ticketnumbertext);

SELECT ticketnumbertext FROM ticketingsystem ti WHERE ti.form = "frmFehlerbild" AND parentdocunid IN
(SELECT RAW `$_meta`.unid FROM ticketingsystem WHERE ticketownergroup = "124" AND ticketstatusalias <= "10");

EXPLAIN only showed for subqueries in FROM clause. Not other places there is open feature enhancement on this. Workaround, In this case subquery is non correlated (Incase of correlated change correlated variables to $somename) so you can do EXPLAIN that will be correct plan

1 Like
4

thank you very much!