Index on joined field

SQL++
,
1

I have a query like this one:

SELECT ARRAY_SORT(ARRAY_AGG(event.dateTime))[-1] as dateTime, event.visitId FROM bucket event
JOIN bucket visit ON KEYS (event.visitId) 
WHERE event.visitId IS NOT MISSING
AND event.`type` = "EVENT"
AND visit.visitType = "VT_PATIENT_EXTERNE"
AND SPLIT(META(visit).`id`,':')[0] != "_sync" 
AND (visit.isVoided = false OR visit.isVoided IS MISSING)
GROUP BY event.visitId

And I tried to create an index on visitType but this doesn’t seem to work on joined fields. The index is working when it’s based on the ‘main’ field but not on a joined one.

Here is the index:
CREATE INDEX idx_visit_type ON bucket(visitType)

Thank’s!

2

The Join is Lookup JOIN. Left side of LOOKUP JOIN only uses index, right side does Data fetch and no index is required.
The following article might help to understand better.

In 5.5 You can use ANSI JOIN which uses index on right side too https://blog.couchbase.com/ansi-join-support-n1ql/

3

Index JOIN might perform better.

Check following indexes and query

CREATE INDEX ix1 ON default(visitType,IFMISSING(isVoided,""), visitId) WHERE SPLIT(META().`id`,':')[0] != "_sync"
CREATE INDEX ix2 ON default(visitId, dateTime) WHERE type = "EVENT";

SELECT MAX(event.dateTime) as dateTime, event.visitId
FROM default visit
JOIN default event ON KEY event.visitId FOR visit
WHERE visit.visitType = "VT_PATIENT_EXTERNE"
AND SPLIT(META(visit).`id`,':')[0] != "_sync"
AND IFMISSING(visit.isVoided,"") IN  [false , ""] AND
event.`type` = "EVENT"
GROUP BY event.visitId;

Instead of SPLIT(META(visit).id,’:’)[0] != “_sync” you can use SUBSTR(META(visit).id,0,5) != “_sync”

4

Thank you for your answer. In fact I didn’t know about index JOIN. It perform a bit better (~20sec instead of ~35sec). But I don’t see the ix2 being used when I hit the EXPLAIN statement, do you know why?

5

It should be index join section post the explain explain

6

Here is the explain (‘idx_visit_type_id’ = ‘ix1’):

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "DistinctScan",
          "scan": {
            "#operator": "IndexScan",
            "covers": [
              "cover ((`visit`.`visitType`))",
              "cover (ifmissing((`visit`.`isVoided`), \"\"))",
              "cover ((`visit`.`visitId`))",
              "cover ((meta(`visit`).`id`))"
            ],
            "index": "idx_visit_type_id",
            "index_id": "77d2fec4de56c10d",
            "keyspace": "default",
            "namespace": "default",
            "spans": [
              {
                "Range": {
                  "High": [
                    "\"VT_PATIENT_EXTERNE\"",
                    "successor(false)"
                  ],
                  "Inclusion": 1,
                  "Low": [
                    "\"VT_PATIENT_EXTERNE\"",
                    "false"
                  ]
                }
              },
              {
                "Range": {
                  "High": [
                    "\"VT_PATIENT_EXTERNE\"",
                    "successor(\"\")"
                  ],
                  "Inclusion": 1,
                  "Low": [
                    "\"VT_PATIENT_EXTERNE\"",
                    "\"\""
                  ]
                }
              }
            ],
            "using": "gsi"
          }
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "IndexJoin",
                "as": "event",
                "for": "visit",
                "keyspace": "default",
                "namespace": "default",
                "on_key": "(`event`.`visitId`)",
                "scan": {
                  "index": "idx_visitId",
                  "index_id": "b6498c75045de40a",
                  "using": "gsi"
                }
              },
              {
                "#operator": "Filter",
                "condition": "(((((cover ((`visit`.`visitType`)) = \"VT_PATIENT_EXTERNE\") and ((`event`.`dateTime`) is valued)) and (not ((split(cover ((meta(`visit`).`id`)), \":\")[0]) = \"_sync\"))) and (cover (ifmissing((`visit`.`isVoided`), \"\")) in [false, \"\"])) and ((`event`.`type`) = \"EVENT\"))"
              },
              {
                "#operator": "InitialGroup",
                "aggregates": [
                  "max((`event`.`dateTime`))"
                ],
                "group_keys": [
                  "(`event`.`visitId`)"
                ]
              }
            ]
          }
        },
        {
          "#operator": "IntermediateGroup",
          "aggregates": [
            "max((`event`.`dateTime`))"
          ],
          "group_keys": [
            "(`event`.`visitId`)"
          ]
        },
        {
          "#operator": "FinalGroup",
          "aggregates": [
            "max((`event`.`dateTime`))"
          ],
          "group_keys": [
            "(`event`.`visitId`)"
          ]
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((`event`.`visitId`) is not missing)"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "as": "dateTime",
                    "expr": "max((`event`.`dateTime`))"
                  },
                  {
                    "expr": "(`event`.`visitId`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "SELECT MAX(event.dateTime) as dateTime, event.visitId FROM default visit\nJOIN default event ON KEY event.visitId FOR visit\nWHERE visit.visitType = \"VT_PATIENT_EXTERNE\"\nAND event.dateTime IS VALUED\nAND SPLIT(META(visit).`id`,':')[0] != \"_sync\"\nAND IFMISSING(visit.isVoided,\"\") IN  [false , \"\"] AND\nevent.`type` = \"EVENT\"\nGROUP BY event.visitId HAVING event.visitId IS NOT MISSING"
  }
]
7

What is definition of idx_visitId, This is second index, 4.6.4 it should have used covered index

8

idx_visitId: CREATE INDEX idx_visitId ON default(visitId) WHERE (type = "EVENT")

9

both qualifies any index can be used. U can keep idx_visitId. In 4.6.4 ix2 makes query covered