Slow N1QL query Performance with a join

SQL++
1

I have a query which uses a joint which is taking way to long. In my case i get all some tracking docs and these are joint by the events to get the last time someone accessed the tracked item. My N1Ql looks like this. It takes currently it takes over 1 min for that query, if i do it without join its ms

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 LEFT
    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
LIMIT 25
OFFSET 0

On the Avice page i get this

##### Indexes Currently Used
CREATE INDEX adv_type ON `Contacts`(`_type`)
CREATE INDEX ix11 ON `Contacts`(`tracking_nbr`,`time_date`) WHERE (`_type` = 'tracker')
Existing indexes are sufficient.

and here is my plan text

{
    "#operator": "Sequence",
    "~children": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "IndexScan3",
                    "as": "tr",
                    "index": "Ottoman__type",
                    "index_id": "ab04d8be5f77c6ca",
                    "index_projection": {
                        "primary_key": true
                    },
                    "keyspace": "Contacts",
                    "namespace": "default",
                    "spans": [
                        {
                            "exact": true,
                            "range": [
                                {
                                    "high": "\"track_request\"",
                                    "inclusion": 3,
                                    "low": "\"track_request\""
                                }
                            ]
                        }
                    ],
                    "using": "gsi"
                },
                {
                    "#operator": "Fetch",
                    "as": "tr",
                    "keyspace": "Contacts",
                    "namespace": "default"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "NestedLoopJoin",
                                "alias": "t",
                                "on_clause": "((cover ((`t`.`_type`)) = \"tracker\") and (cover ((`t`.`tracking_nbr`)) = (`tr`.`tracking_nbr`)))",
                                "outer": true,
                                "~child": {
                                    "#operator": "Sequence",
                                    "~children": [
                                        {
                                            "#operator": "IndexScan3",
                                            "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": "73e0deb4d1da235f",
                                            "keyspace": "Contacts",
                                            "namespace": "default",
                                            "nested_loop": true,
                                            "spans": [
                                                {
                                                    "exact": true,
                                                    "range": [
                                                        {
                                                            "high": "(`tr`.`tracking_nbr`)",
                                                            "inclusion": 3,
                                                            "low": "(`tr`.`tracking_nbr`)"
                                                        }
                                                    ]
                                                }
                                            ],
                                            "using": "gsi"
                                        }
                                    ]
                                }
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((`tr`.`_type`) = \"track_request\")"
                            },
                            {
                                "#operator": "InitialGroup",
                                "aggregates": [
                                    "max(cover ((`t`.`time_date`)))"
                                ],
                                "group_keys": [
                                    "`tr`"
                                ]
                            }
                        ]
                    }
                },
                {
                    "#operator": "IntermediateGroup",
                    "aggregates": [
                        "max(cover ((`t`.`time_date`)))"
                    ],
                    "group_keys": [
                        "`tr`"
                    ]
                },
                {
                    "#operator": "FinalGroup",
                    "aggregates": [
                        "max(cover ((`t`.`time_date`)))"
                    ],
                    "group_keys": [
                        "`tr`"
                    ]
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "InitialProject",
                                "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`)))"
                                    }
                                ]
                            }
                        ]
                    }
                }
            ]
        },
        {
            "#operator": "Order",
            "limit": "25",
            "sort_terms": [
                {
                    "desc": true,
                    "expr": "(`tr`.`send_DateTime`)"
                }
            ]
        },
        {
            "#operator": "Limit",
            "expr": "25"
        },
        {
            "#operator": "FinalProject"
        }
    ]
}
2

Query is JOIN, GROUP, Pagination it must produce all possible values.

Based on your query due to LEFT JOIN , GROUP on LEFT document, you can rewrite following way
Use Index Order produce 25 left documents. Then do LEFT JOIN and GROUP, sort.

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

CREATE INDEX ixtype1 ON `Contacts`(_type, send_DateTime DESC) WHERE _type = "track_request";
CREATE INDEX ix11 ON `Contacts`(`tracking_nbr`,`time_date`) WHERE (`_type` = 'tracker');

Do EXPLAIN and see FROM clause subquery using right index ixtype1 and avoiding sort

CB 7.0 onwards try correlated subquery without USE KEYS

SELECT META(c).id AS DocId, c.tracking_nbr, c.subject, c.click_count, c.msg_count, c.notify,
             c.send_DateTime, c.send_to, c.send_cc, c.send_bcc, c.send_from,
       (SELECT RAW MAX(t.time_date)
        FROM Contacts AS t
        WHERE t._type = "tracker" AND t.tracking_nbr = tr.tracking_nbr)[0] AS last_event_DateTime
FROM Contacts AS c
WHERE c._type = "track_request"
ORDER BY c.send_DateTime DESC
LIMIT 25
OFFSET 0
3

Thanks, that changed the Time dramatically…

I am still stuck on 6.6 as the api uses the 2.5.X SDK for node JS and till i can change the SDK to 3.2.x i cant upgrade. But i hope to get there in a month or so

1 Like