Stuck in Index Hell again

#1

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
}
#2

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

#3

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

#4

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

#5

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
}
#6

Post the index definition of
idx_tracking_nbr

#7

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

i use that to lookup document by tracking_nbr

#8

Query plan looks fine

#9

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

#10

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;
#11

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”: “http://docs.com/campaign/Dgfz-URQC/tracker.png”,
“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
}
}

#12

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 ?

#13

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

#14

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 ?

#15

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

#16

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

#17

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;
#18

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.