Stuck in Index Hell again

makeawish · May 16, 2019, 4:08pm

i have the blow query which works but is extremely slow.

SELECT meta(tr).id as DocId, 
tr.tracking_nbr, tr.subject,tr.click_count, tr.msg_count,tr.notify,tr.send_DateTime, tr.send_to, tr.send_cc, tr.send_bcc, tr.send_from
, MAX(t.time_date) AS last_event_DateTime
FROM Contacts AS tr
JOIN Contacts AS t ON t._type="tracker" AND t.tracking_nbr = tr.tracking_nbr
WHERE tr._type = "track_request"
GROUP BY tr
order by tr.send_DateTime DESC

i have an index on on the time_date on the _type = tracker but it takes forever to group these to return result.

here is the current execution plan

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "5.564µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "24.729µs",
        "servTime": "3.907075ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:Contacts",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "24.311µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "6.409µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 745,
                  "#phaseSwitches": 2983,
                  "execTime": "2.609613ms",
                  "kernTime": "155.187155ms",
                  "servTime": "3.74406ms"
                },
                "as": "tr",
                "index": "Ottoman__type",
                "index_id": "f9d810b05a296595",
                "index_projection": {
                  "primary_key": true
                },
                "keyspace": "Contacts",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"track_request\"",
                        "inclusion": 3,
                        "low": "\"track_request\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.0063",
                "#time_absolute": 0.006353673
              },
              {
                "#operator": "Fetch",
                "#stats": {
                  "#itemsIn": 745,
                  "#itemsOut": 745,
                  "#phaseSwitches": 3077,
                  "execTime": "5.335188ms",
                  "kernTime": "247.307428ms",
                  "servTime": "402.683139ms"
                },
                "as": "tr",
                "keyspace": "Contacts",
                "namespace": "default",
                "#time_normal": "00:00.4080",
                "#time_absolute": 0.40801832699999996
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "3.151µs"
                },
                "~children": [
                  {
                    "#operator": "NestedLoopJoin",
                    "#stats": {
                      "#itemsIn": 745,
                      "#itemsOut": 2511,
                      "#phaseSwitches": 14517,
                      "execTime": "86.010295ms",
                      "kernTime": "1.808681151s"
                    },
                    "alias": "t",
                    "on_clause": "(((`t`.`_type`) = \"tracker\") and ((`t`.`tracking_nbr`) = (`tr`.`tracking_nbr`)))",
                    "~child": {
                      "#operator": "Sequence",
                      "#stats": {
                        "#phaseSwitches": 1490,
                        "execTime": "1.811519395s",
                        "kernTime": "599.67µs",
                        "state": "running"
                      },
                      "~children": [
                        {
                          "#operator": "IndexScan3",
                          "#stats": {
                            "#itemsOut": 2511,
                            "#phaseSwitches": 12279,
                            "execTime": "18.83646ms",
                            "kernTime": "2.239934ms",
                            "servTime": "1.2170539s"
                          },
                          "as": "t",
                          "index": "idx_tracking_nbr",
                          "index_id": "6e990b72952c3d76",
                          "index_projection": {
                            "primary_key": true
                          },
                          "keyspace": "Contacts",
                          "namespace": "default",
                          "nested_loop": true,
                          "spans": [
                            {
                              "exact": true,
                              "range": [
                                {
                                  "high": "(`tr`.`tracking_nbr`)",
                                  "inclusion": 3,
                                  "low": "(`tr`.`tracking_nbr`)"
                                }
                              ]
                            }
                          ],
                          "using": "gsi"
                        },
                        {
                          "#operator": "Fetch",
                          "#stats": {
                            "#itemsIn": 2511,
                            "#itemsOut": 2511,
                            "#phaseSwitches": 13737,
                            "execTime": "45.346087ms",
                            "kernTime": "1.248910662s",
                            "servTime": "506.060629ms"
                          },
                          "as": "t",
                          "keyspace": "Contacts",
                          "namespace": "default",
                          "nested_loop": true
                        }
                      ]
                    },
                    "#time_normal": "00:00.0860",
                    "#time_absolute": 0.086010295
                  },
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 2511,
                      "#itemsOut": 2511,
                      "#phaseSwitches": 10047,
                      "execTime": "24.564601ms",
                      "kernTime": "2.007116548s"
                    },
                    "condition": "((`tr`.`_type`) = \"track_request\")",
                    "#time_normal": "00:00.0245",
                    "#time_absolute": 0.024564601
                  },
                  {
                    "#operator": "InitialGroup",
                    "#stats": {
                      "#itemsIn": 2511,
                      "#itemsOut": 692,
                      "#phaseSwitches": 6409,
                      "execTime": "2.595679176s",
                      "kernTime": "246.70408ms"
                    },
                    "aggregates": [
                      "max((`t`.`time_date`))"
                    ],
                    "group_keys": [
                      "`tr`"
                    ],
                    "#time_normal": "00:02.5956",
                    "#time_absolute": 2.595679176
                  }
                ],
                "#time_normal": "00:00.0000",
                "#time_absolute": 0.000003151
              },
              {
                "#operator": "IntermediateGroup",
                "#stats": {
                  "#itemsIn": 692,
                  "#itemsOut": 692,
                  "#phaseSwitches": 2771,
                  "execTime": "591.08752ms",
                  "kernTime": "2.737726612s"
                },
                "aggregates": [
                  "max((`t`.`time_date`))"
                ],
                "group_keys": [
                  "`tr`"
                ],
                "#time_normal": "00:00.5910",
                "#time_absolute": 0.5910875200000001
              },
              {
                "#operator": "FinalGroup",
                "#stats": {
                  "#itemsIn": 692,
                  "#itemsOut": 692,
                  "#phaseSwitches": 2771,
                  "execTime": "341.441921ms",
                  "kernTime": "3.234417578s"
                },
                "aggregates": [
                  "max((`t`.`time_date`))"
                ],
                "group_keys": [
                  "`tr`"
                ],
                "#time_normal": "00:00.3414",
                "#time_absolute": 0.341441921
              },
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 692,
                  "#itemsOut": 692,
                  "#phaseSwitches": 2771,
                  "execTime": "10.199883ms",
                  "kernTime": "3.573928625s"
                },
                "result_terms": [
                  {
                    "as": "DocId",
                    "expr": "(meta(`tr`).`id`)"
                  },
                  {
                    "expr": "(`tr`.`tracking_nbr`)"
                  },
                  {
                    "expr": "(`tr`.`subject`)"
                  },
                  {
                    "expr": "(`tr`.`click_count`)"
                  },
                  {
                    "expr": "(`tr`.`msg_count`)"
                  },
                  {
                    "expr": "(`tr`.`notify`)"
                  },
                  {
                    "expr": "(`tr`.`send_DateTime`)"
                  },
                  {
                    "expr": "(`tr`.`send_to`)"
                  },
                  {
                    "expr": "(`tr`.`send_cc`)"
                  },
                  {
                    "expr": "(`tr`.`send_bcc`)"
                  },
                  {
                    "expr": "(`tr`.`send_from`)"
                  },
                  {
                    "as": "last_event_DateTime",
                    "expr": "max((`t`.`time_date`))"
                  }
                ],
                "#time_normal": "00:00.0101",
                "#time_absolute": 0.010199883
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000006409
          },
          {
            "#operator": "Order",
            "#stats": {
              "#itemsIn": 692,
              "#itemsOut": 692,
              "#phaseSwitches": 2081,
              "execTime": "3.319223ms",
              "kernTime": "3.584057615s"
            },
            "sort_terms": [
              {
                "desc": true,
                "expr": "(`tr`.`send_DateTime`)"
              }
            ],
            "#time_normal": "00:00.0033",
            "#time_absolute": 0.003319223
          },
          {
            "#operator": "FinalProject",
            "#stats": {
              "#itemsIn": 692,
              "#itemsOut": 692,
              "#phaseSwitches": 2077,
              "execTime": "1.202468ms",
              "kernTime": "35.842761ms"
            },
            "#time_normal": "00:00.0012",
            "#time_absolute": 0.0012024680000000002
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000024311
      },
      "#time_normal": "00:00.0039",
      "#time_absolute": 0.003931804
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 692,
        "#itemsOut": 692,
        "#phaseSwitches": 2771,
        "execTime": "275.9µs",
        "kernTime": "3.6286241s"
      },
      "#time_normal": "00:00.0002",
      "#time_absolute": 0.0002759
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.0-1693-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.0000055640000000000004
}

vsr1 · May 16, 2019, 4:22pm

CREATE INDEX ix1 ON Contacts (tracking_nbr, time_date) WHERE _type=“tracker”;

makeawish · May 16, 2019, 4:28pm

I have this one already, does it make a difference the order of field ?

CREATE INDEX ix9 ON Contacts(time_date,tracking_nbr) WHERE (_type = “tracker”)

vsr1 · May 16, 2019, 4:29pm

Switch the index keys https://blog.couchbase.com/create-right-index-get-right-performance/

Checkout Optimize N1QL Performance Using Request Profiling https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

makeawish · May 16, 2019, 4:30pm

Did that but it still does not change performance

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "6.61µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "10.437µs",
        "servTime": "10.09383ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:Contacts",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "11.375µs"
        },
        "~children": [
          {
            "#operator": "IndexScan3",
            "#stats": {
              "#itemsOut": 745,
              "#phaseSwitches": 2983,
              "execTime": "5.694377ms",
              "kernTime": "258.576115ms",
              "servTime": "6.642275ms"
            },
            "as": "tr",
            "index": "Ottoman__type",
            "index_id": "f9d810b05a296595",
            "index_projection": {
              "primary_key": true
            },
            "keyspace": "Contacts",
            "namespace": "default",
            "spans": [
              {
                "exact": true,
                "range": [
                  {
                    "high": "\"track_request\"",
                    "inclusion": 3,
                    "low": "\"track_request\""
                  }
                ]
              }
            ],
            "using": "gsi",
            "#time_normal": "00:00.0123",
            "#time_absolute": 0.012336652
          },
          {
            "#operator": "Fetch",
            "#stats": {
              "#itemsIn": 745,
              "#itemsOut": 745,
              "#phaseSwitches": 3077,
              "execTime": "5.337699ms",
              "kernTime": "25.608113ms",
              "servTime": "628.799928ms"
            },
            "as": "tr",
            "keyspace": "Contacts",
            "namespace": "default",
            "#time_normal": "00:00.6341",
            "#time_absolute": 0.6341376270000001
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "46.461µs"
            },
            "~children": [
              {
                "#operator": "NestedLoopJoin",
                "#stats": {
                  "#itemsIn": 745,
                  "#itemsOut": 2511,
                  "#phaseSwitches": 14517,
                  "execTime": "53.47909ms",
                  "kernTime": "2.054403619s"
                },
                "alias": "t",
                "on_clause": "((cover ((`t`.`_type`)) = \"tracker\") and (cover ((`t`.`tracking_nbr`)) = (`tr`.`tracking_nbr`)))",
                "~child": {
                  "#operator": "IndexScan3",
                  "#stats": {
                    "#itemsOut": 2511,
                    "#phaseSwitches": 13769,
                    "execTime": "38.783168ms",
                    "kernTime": "4.0602ms",
                    "servTime": "1.405566279s"
                  },
                  "as": "t",
                  "covers": [
                    "cover ((`t`.`tracking_nbr`))",
                    "cover ((`t`.`time_date`))",
                    "cover ((meta(`t`).`id`))"
                  ],
                  "filter_covers": {
                    "cover ((`t`.`_type`))": "tracker"
                  },
                  "index": "ix11",
                  "index_id": "762f3434a930431",
                  "keyspace": "Contacts",
                  "namespace": "default",
                  "nested_loop": true,
                  "spans": [
                    {
                      "exact": true,
                      "range": [
                        {
                          "high": "(`tr`.`tracking_nbr`)",
                          "inclusion": 3,
                          "low": "(`tr`.`tracking_nbr`)"
                        }
                      ]
                    }
                  ],
                  "using": "gsi"
                },
                "#time_normal": "00:00.0534",
                "#time_absolute": 0.05347909
              },
              {
                "#operator": "Filter",
                "#stats": {
                  "#itemsIn": 2511,
                  "#itemsOut": 2511,
                  "#phaseSwitches": 10047,
                  "execTime": "34.794369ms",
                  "kernTime": "2.885676492s"
                },
                "condition": "((`tr`.`_type`) = \"track_request\")",
                "#time_normal": "00:00.0347",
                "#time_absolute": 0.034794369000000006
              },
              {
                "#operator": "InitialGroup",
                "#stats": {
                  "#itemsIn": 2511,
                  "#itemsOut": 692,
                  "#phaseSwitches": 6409,
                  "execTime": "3.37327984s",
                  "kernTime": "275.991938ms"
                },
                "aggregates": [
                  "max(cover ((`t`.`time_date`)))"
                ],
                "group_keys": [
                  "`tr`"
                ],
                "#time_normal": "00:03.3732",
                "#time_absolute": 3.37327984
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.000046461
          },
          {
            "#operator": "IntermediateGroup",
            "#stats": {
              "#itemsIn": 692,
              "#itemsOut": 692,
              "#phaseSwitches": 2771,
              "execTime": "475.278814ms",
              "kernTime": "3.634330754s"
            },
            "aggregates": [
              "max(cover ((`t`.`time_date`)))"
            ],
            "group_keys": [
              "`tr`"
            ],
            "#time_normal": "00:00.4752",
            "#time_absolute": 0.475278814
          },
          {
            "#operator": "FinalGroup",
            "#stats": {
              "#itemsIn": 692,
              "#itemsOut": 692,
              "#phaseSwitches": 2771,
              "execTime": "379.548472ms",
              "kernTime": "3.970555586s"
            },
            "aggregates": [
              "max(cover ((`t`.`time_date`)))"
            ],
            "group_keys": [
              "`tr`"
            ],
            "#time_normal": "00:00.3795",
            "#time_absolute": 0.379548472
          },
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "3.509µs"
            },
            "~children": [
              {
                "#operator": "InitialProject",
                "#stats": {
                  "#itemsIn": 692,
                  "#itemsOut": 692,
                  "#phaseSwitches": 2081,
                  "execTime": "16.971181ms",
                  "kernTime": "4.336977921s"
                },
                "result_terms": [
                  {
                    "as": "DocId",
                    "expr": "(meta(`tr`).`id`)"
                  },
                  {
                    "expr": "(`tr`.`tracking_nbr`)"
                  },
                  {
                    "expr": "(`tr`.`subject`)"
                  },
                  {
                    "expr": "(`tr`.`click_count`)"
                  },
                  {
                    "expr": "(`tr`.`msg_count`)"
                  },
                  {
                    "expr": "(`tr`.`notify`)"
                  },
                  {
                    "expr": "(`tr`.`send_DateTime`)"
                  },
                  {
                    "expr": "(`tr`.`send_to`)"
                  },
                  {
                    "expr": "(`tr`.`send_cc`)"
                  },
                  {
                    "expr": "(`tr`.`send_bcc`)"
                  },
                  {
                    "expr": "(`tr`.`send_from`)"
                  },
                  {
                    "as": "last_event_DateTime",
                    "expr": "max(cover ((`t`.`time_date`)))"
                  }
                ],
                "#time_normal": "00:00.0169",
                "#time_absolute": 0.016971181000000002
              },
              {
                "#operator": "FinalProject",
                "#stats": {
                  "#itemsIn": 692,
                  "#itemsOut": 692,
                  "#phaseSwitches": 2077,
                  "execTime": "877.565µs",
                  "kernTime": "48.94685ms"
                },
                "#time_normal": "00:00.0008",
                "#time_absolute": 0.0008775650000000001
              }
            ],
            "#time_normal": "00:00.0000",
            "#time_absolute": 0.0000035089999999999998
          }
        ],
        "#time_normal": "00:00.0000",
        "#time_absolute": 0.000011375
      },
      "#time_normal": "00:00.0101",
      "#time_absolute": 0.010104267
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 692,
        "#itemsOut": 692,
        "#phaseSwitches": 2771,
        "execTime": "261.477µs",
        "kernTime": "4.413898926s"
      },
      "#time_normal": "00:00.0002",
      "#time_absolute": 0.000261477
    }
  ],
  "~versions": [
    "2.0.0-N1QL",
    "6.0.0-1693-enterprise"
  ],
  "#time_normal": "00:00.0000",
  "#time_absolute": 0.00000661
}

vsr1 · May 16, 2019, 4:33pm

Post the index definition of
idx_tracking_nbr

makeawish · May 16, 2019, 4:34pm

CREATE INDEX idx_tracking_nbr ON Contacts(tracking_nbr) WHERE (_type = “tracker”)

i use that to lookup document by tracking_nbr

vsr1 · May 16, 2019, 4:36pm

Query plan looks fine

makeawish · May 16, 2019, 4:37pm

Yeah but the group part takes up almost 3 Sec’s

vsr1 · May 16, 2019, 4:57pm

Your document might be too bing. Try this

   SELECT tr.*,  MAX(t.time_date) AS last_event_DateTime
    FROM (SELECT META().id AS DocId, tracking_nbr, subject, click_count, msg_count,
                 notify, send_DateTime, send_to, send_cc, send_bcc, send_from
          FROM Contacts
          WHERE _type = "track_request") AS tr
    JOIN Contacts AS t ON t._type="tracker" AND t.tracking_nbr = tr.tracking_nbr
    GROUP BY tr
    ORDER BY tr.send_DateTime DESC;

OR

  SELECT MIN(tr).*,   MIN(tr.send_DateTime) AS send_DateTime,  MAX(t.time_date) AS last_event_DateTime
        FROM (SELECT META().id AS DocId, tracking_nbr, subject, click_count, msg_count,
                     notify, send_DateTime, send_to, send_cc, send_bcc, send_from
              FROM Contacts
              WHERE _type = "track_request") AS tr
        JOIN Contacts AS t ON t._type="tracker" AND t.tracking_nbr = tr.tracking_nbr
        GROUP BY tr.DocId
        ORDER BY send_DateTime DESC;

makeawish · May 16, 2019, 5:06pm

Ok the first one brought the time down to around a sec which is way better, second one doesnt work
“msg”: “Expression must be a group key or aggregate: (tr.send_DateTime)”,
. So basicly i query the track_request and put it into tr and then select all again ? Thats confusing
you mean the original doc might be to big ? yes the track request has a email body in it but i am not selecting it from the doc and also the grouping which based on the plan is taking the time happens on the tracker doc which is not that big and looks like this

{
“_id”: “003bdff9-813b-4e2f-859d-7c39ab4052ea”,
“_type”: “tracker”,
“type”: “email”,
“ip_address”: “69.178.186.74”,
“ip_info”: {
“range”: [
1169340928,
1169341183
],
“country”: “US”,
“region”: “CA”,
“eu”: “0”,
“timezone”: “America/Los_Angeles”,
“city”: “Santa Ana”,
“ll”: [
33.7086,
-117.8701
],
“metro”: 803,
“area”: 10
},
“time_date”: “2019-05-09T23:29:10.724Z”,
“tracking_nbr”: “Dgfz-URQC”,
“orig_url”: “Microsoft Learn: Build skills that open doors in your career”,
“user_agent”: “Mozilla/4.0 (compatible; ms-office; MSOffice 16)”,
“req_header”: {
“connection”: “Keep-Alive”,
“user-agent”: “Mozilla/4.0 (compatible; ms-office; MSOffice 16)”,
“host”: “docs.com”
}
}

makeawish · May 16, 2019, 5:53pm

So is there a magic size limit once this becomes an issue due to document size ? If so i would asume the recommended approach would be to move the actual message body into a separate doc to make the doc smaller and then just query for the body if it is really required ?

vsr1 · May 16, 2019, 6:03pm

When you group the group size is bigger for whole document you need to limit so that computation makes faster.

makeawish · May 16, 2019, 6:06pm

So it seems the group part uses the whole doc not only the fields selected for the group. So do you think there is a significant performance gain if i move the message body outside the track_request vs using your aproach of selecting * from a sub selection and then group ?

vsr1 · May 16, 2019, 6:08pm

When u say GROUP tr means whole tr document. If you specify GROUP tr.x, tr.y It is grouped on those fields

makeawish · May 16, 2019, 7:01pm

In this case it all comes down that i cant use a sub query which does not use a KEY. I guess i will just have to hope that this limitation goes away rather sooner then later. The same query in SQL db using sub query’s is in the 100 ms that’s a huge difference

vsr1 · May 16, 2019, 7:35pm

If data is small try this this

SELECT META(tr).id AS DocId, tr.tracking_nbr, tr.subject, tr.click_count, tr.msg_count,
        tr.notify, tr.send_DateTime, tr.send_to, tr.send_cc, tr.send_bcc, tr.send_from
FROM Contacts AS tr
UNNEST (SELECT t1.tracking_nbr, MAX(t1.time_date) AS last_event_DateTime
        FROM Contacts AS t1
        WHERE t1._type="tracker"
        GROUP BY t1.tracking_nbr) AS t
WHERE tr._type = "track_request" AND t.tracking_nbr = tr.tracking_nbr
ORDER BY tr.send_DateTime DESC;

makeawish · May 17, 2019, 8:17pm

Ok, i moved my body out of the Doc into a separate one since i don’t need it most of the time and to make the doc smaller.
At this point my original Query is anywhere from 800 ms to a bit over a sec. Your Query solution which did select fields to group is about the same with around a sec. Not great but i guess thats where we are at this point untill none key based querys are avail.
As for the last solution just ran it and it came in at close to 14 Sec.

Topic		Replies	Views
Very slow query with index based on variations SQL++	1	1028	June 8, 2017
How to improve Index on a join with Condition SQL++	7	783	April 4, 2022
Slow N1QL query Performance with a join SQL++	2	1530	January 14, 2022
Show query when fetching higher indices of an array of documents SQL++	2	628	May 10, 2019
Complex query performance optimization SQL++ query , n1ql , index	22	1521	February 7, 2022

Stuck in Index Hell again

Related topics