INDEX JOIN... "LEFT OUTER NEST" vs "LEFT OUTER JOIN"

SQL++
1

The only difference between “N1QL 1” and “N1QL 2” (below) is the 3rd line of each. But, when I look at the plan, “N1QL 1” starts out with 400,000 docs (as expected) and “N1QL 2” starts out with about 2,000 docs. What would explain this difference?

N1QL 1…
SELECT bt.keyProperty, bt.someDate, bt1
FROM myBucket bt
LEFT OUTER NEST bucket1 bt1 ON KEY bt1.docIdFromMyBucket FOR bt
WHERE bt.type = “someDocType”
AND bt.keyProperty = “value1”
AND bt.someDate = “2020-12-01”
ORDER BY bt.someDate
LIMIT 1000

N1QL 2…
SELECT bt.keyProperty, bt.someDate, bt1
FROM myBucket bt
LEFT OUTER JOIN bucket1 bt1 ON KEY bt1.docIdFromMyBucket FOR bt
WHERE bt.type = “someDocType”
AND bt.keyProperty = “value1”
AND bt.someDate = “2020-12-01”
ORDER BY bt.someDate
LIMIT 1000

2

What do u mean start with?

NQL1 is NEST all matching documents care combined into ARRAY bt1
NQL2 is JOIN it can expand n join m produces n*m

3

By “starts with” I mean… the Plan for the query shows the steps with the number of documents In and the number of documents Out at each step. For query “N1QL 1” the first step has 400,000 documents In (which I would expect), while query “N1QL 2” the first step has 2,000 documents In. Why the difference with documents In?

4

First one Should be Index Scan from bt . It should be same for both queries.
Will you able to post output from the Plan Text Tab of UI for both queries.

CREATE INDEX ix1 ON myBucket(keyProperty, someDate ) WHERE type = “someDocType”

https://index-advisor.couchbase.com/indexadvisor/#1

5

LEFT OUTER NEST…

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "1.5µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "2.8µs",
        "servTime": "791.742µs"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:myBucket",
            "Priv": 7
          },
          {
            "Target": "default:app",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "2.4µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 1,
              "execTime": "1.4µs"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 576111,
                  "#phaseSwitches": 2304447,
                  "execTime": "3.493969328s",
                  "kernTime": "4m56.536045694s",
                  "servTime": "1.087806274s"
                },
                "as": "vis",
                "covers": [
                  "cover ((`vis`.`type`))",
                  "cover ((`vis`.`tenantName`))",
                  "cover ((`vis`.`dischargeDate`))",
                  "cover ((`vis`.`visitNumber`))",
                  "cover ((meta(`vis`).`id`))"
                ],
                "filter_covers": {
                  "cover ((`vis`.`type`))": "visit"
                },
                "index": "cm_idx_visit_tenantName_dischargeDate_visitNumber",
                "index_id": "f159f580af37c32c",
                "index_projection": {
                  "entry_keys": [
                    0,
                    1,
                    2
                  ],
                  "primary_key": true
                },
                "keyspace": "myBucket",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"visit\"",
                        "inclusion": 3,
                        "low": "\"visit\""
                      },
                      {
                        "high": "\"value1\"",
                        "inclusion": 3,
                        "low": "\"value1\""
                      },
                      {
                        "high": "\"2020-12-08\"",
                        "inclusion": 3,
                        "low": "\"2020-11-08\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:04.581",
                "#time_absolute": 4.5817756020000004
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 1,
                  "execTime": "1.401µs"
                },
                "~children": [
                  {
                    "#operator": "IndexNest",
                    "#stats": {
                      "#itemsIn": 576111,
                      "#itemsOut": 576111,
                      "#phaseSwitches": 3549243,
                      "execTime": "4.148986642s",
                      "kernTime": "749.638041ms",
                      "servTime": "4m56.490803387s"
                    },
                    "as": "opps",
                    "for": "vis",
                    "keyspace": "app",
                    "namespace": "default",
                    "on_key": "(`opps`.`visitDocId`)",
                    "outer": true,
                    "scan": {
                      "index": "app_join_idx_visitDocId_for_opp",
                      "index_id": "898fa3018356c930",
                      "using": "gsi"
                    },
                    "#time_normal": "05:00.639",
                    "#time_absolute": 300.63979002900004
                  },
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 576111,
                      "#itemsOut": 576111,
                      "#phaseSwitches": 2304447,
                      "execTime": "1.552647172s",
                      "kernTime": "4m59.836839101s"
                    },
                    "condition": "((((cover ((`vis`.`type`)) = \"visit\") and (cover ((`vis`.`tenantName`)) = \"value1\")) and (\"2020-11-08\" <= cover ((`vis`.`dischargeDate`)))) and (cover ((`vis`.`dischargeDate`)) <= \"2020-12-08\"))",
                    "#time_normal": "00:01.552",
                    "#time_absolute": 1.552647172
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 576111,
                      "#itemsOut": 576111,
                      "#phaseSwitches": 2304447,
                      "execTime": "1.037476515s",
                      "kernTime": "5m0.35204136s"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((meta(`vis`).`id`))"
                      }
                    ],
                    "#time_normal": "00:01.037",
                    "#time_absolute": 1.037476515
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.000001401
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.0000014
          },
          {
            "#operator": "Order",
            "#stats": {
              "#itemsIn": 576111,
              "#itemsOut": 1000,
              "#phaseSwitches": 1153227,
              "execTime": "275.31215ms",
              "kernTime": "5m1.115320688s"
            },
            "limit": "1000",
            "sort_terms": [
              {
                "expr": "cover ((`vis`.`dischargeDate`))"
              }
            ],
            "#time_normal": "00:00.275",
            "#time_absolute": 0.27531215
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 1000,
              "#itemsOut": 1000,
              "#phaseSwitches": 2001,
              "execTime": "294.811µs"
            },
            "expr": "1000",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.00029481099999999997
          },
          {
            "#operator": "FinalProject",
            "#stats": {
              "#itemsIn": 1000,
              "#itemsOut": 1000,
              "#phaseSwitches": 3001,
              "execTime": "531.327µs",
              "kernTime": "3.817701ms"
            },
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000531327
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.0000024
      },
      "#time_normal": "00:00.000",
      "#time_absolute": 0.000794542
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1000,
        "#itemsOut": 1000,
        "#phaseSwitches": 2003,
        "execTime": "2.737145ms",
        "kernTime": "5m1.3938662s"
      },
      "#time_normal": "00:00.002",
      "#time_absolute": 0.002737145
    }
  ],
  "~versions": [
    "6.5.0-N1QL",
    "6.5.1-6299-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.0000015
}

LEFT OUTER JOIN

{
  "#operator": "Sequence",
  "#stats": {
    "#phaseSwitches": 1,
    "execTime": "1.6µs"
  },
  "~children": [
    {
      "#operator": "Authorize",
      "#stats": {
        "#phaseSwitches": 3,
        "execTime": "6.601µs",
        "servTime": "1.208263ms"
      },
      "privileges": {
        "List": [
          {
            "Target": "default:myBucket",
            "Priv": 7
          },
          {
            "Target": "default:app",
            "Priv": 7
          }
        ]
      },
      "~child": {
        "#operator": "Sequence",
        "#stats": {
          "#phaseSwitches": 1,
          "execTime": "1.7µs"
        },
        "~children": [
          {
            "#operator": "Sequence",
            "#stats": {
              "#phaseSwitches": 2,
              "execTime": "104.305µs",
              "kernTime": "300ns",
              "state": "running"
            },
            "~children": [
              {
                "#operator": "IndexScan3",
                "#stats": {
                  "#itemsOut": 1513,
                  "#phaseSwitches": 6058,
                  "execTime": "6.293626ms",
                  "kernTime": "647.408016ms",
                  "servTime": "2.828245ms"
                },
                "as": "vis",
                "covers": [
                  "cover ((`vis`.`type`))",
                  "cover ((`vis`.`tenantName`))",
                  "cover ((`vis`.`dischargeDate`))",
                  "cover ((`vis`.`patientClass`))",
                  "cover ((meta(`vis`).`id`))"
                ],
                "filter_covers": {
                  "cover ((`vis`.`type`))": "visit"
                },
                "index": "cm_rpt_visit_tenantName_dischargeDate_patientClass",
                "index_id": "78edf660dc806f62",
                "index_order": [
                  {
                    "keypos": 0
                  },
                  {
                    "keypos": 1
                  },
                  {
                    "keypos": 2
                  }
                ],
                "index_projection": {
                  "entry_keys": [
                    0,
                    1,
                    2
                  ],
                  "primary_key": true
                },
                "keyspace": "myBucket",
                "namespace": "default",
                "spans": [
                  {
                    "exact": true,
                    "range": [
                      {
                        "high": "\"visit\"",
                        "inclusion": 3,
                        "low": "\"visit\""
                      },
                      {
                        "high": "\"value1\"",
                        "inclusion": 3,
                        "low": "\"value1\""
                      },
                      {
                        "high": "\"2020-12-08\"",
                        "inclusion": 3,
                        "low": "\"2020-11-08\""
                      }
                    ]
                  }
                ],
                "using": "gsi",
                "#time_normal": "00:00.009",
                "#time_absolute": 0.009121871
              },
              {
                "#operator": "Sequence",
                "#stats": {
                  "#phaseSwitches": 2,
                  "execTime": "210.611µs",
                  "kernTime": "200ns",
                  "state": "running"
                },
                "~children": [
                  {
                    "#operator": "IndexJoin",
                    "#stats": {
                      "#itemsIn": 1001,
                      "#itemsOut": 1001,
                      "#phaseSwitches": 6045,
                      "execTime": "7.640902ms",
                      "kernTime": "3.243763ms",
                      "servTime": "645.845032ms",
                      "state": "running"
                    },
                    "as": "opps",
                    "for": "vis",
                    "keyspace": "app",
                    "namespace": "default",
                    "on_key": "cover ((`opps`.`visitDocId`))",
                    "outer": true,
                    "scan": {
                      "covers": [
                        "cover ((`opps`.`visitDocId`))",
                        "cover ((`opps`.`state`))",
                        "cover ((meta(`opps`).`id`))"
                      ],
                      "index": "app_idx_visitDocId_state",
                      "index_id": "59b43d393908ce6f",
                      "using": "gsi"
                    },
                    "#time_normal": "00:00.653",
                    "#time_absolute": 0.653485934
                  },
                  {
                    "#operator": "Filter",
                    "#stats": {
                      "#itemsIn": 1001,
                      "#itemsOut": 1001,
                      "#phaseSwitches": 4007,
                      "execTime": "4.998292ms",
                      "kernTime": "651.558096ms"
                    },
                    "condition": "((((cover ((`vis`.`type`)) = \"visit\") and (cover ((`vis`.`tenantName`)) = \"value1\")) and (\"2020-11-08\" <= cover ((`vis`.`dischargeDate`)))) and (cover ((`vis`.`dischargeDate`)) <= \"2020-12-08\"))",
                    "#time_normal": "00:00.004",
                    "#time_absolute": 0.0049982920000000005
                  },
                  {
                    "#operator": "InitialProject",
                    "#stats": {
                      "#itemsIn": 1001,
                      "#itemsOut": 1000,
                      "#phaseSwitches": 3005,
                      "execTime": "3.204777ms",
                      "kernTime": "650.597468ms"
                    },
                    "result_terms": [
                      {
                        "expr": "cover ((meta(`vis`).`id`))"
                      }
                    ],
                    "#time_normal": "00:00.003",
                    "#time_absolute": 0.003204777
                  },
                  {
                    "#operator": "FinalProject",
                    "#stats": {
                      "#itemsIn": 1000,
                      "#itemsOut": 1000,
                      "#phaseSwitches": 2004,
                      "execTime": "931.959µs",
                      "kernTime": "200ns"
                    },
                    "#time_normal": "00:00.000",
                    "#time_absolute": 0.000931959
                  }
                ],
                "#time_normal": "00:00.000",
                "#time_absolute": 0.00021061099999999998
              }
            ],
            "#time_normal": "00:00.000",
            "#time_absolute": 0.00010430500000000001
          },
          {
            "#operator": "Limit",
            "#stats": {
              "#itemsIn": 1000,
              "#itemsOut": 1000,
              "#phaseSwitches": 3002,
              "execTime": "337.618µs",
              "kernTime": "767.639µs"
            },
            "expr": "1000",
            "#time_normal": "00:00.000",
            "#time_absolute": 0.000337618
          }
        ],
        "#time_normal": "00:00.000",
        "#time_absolute": 0.0000017
      },
      "#time_normal": "00:00.001",
      "#time_absolute": 0.001214864
    },
    {
      "#operator": "Stream",
      "#stats": {
        "#itemsIn": 1000,
        "#itemsOut": 1000,
        "#phaseSwitches": 2003,
        "execTime": "6.06271ms",
        "kernTime": "651.721143ms"
      },
      "#time_normal": "00:00.006",
      "#time_absolute": 0.00606271
    }
  ],
  "~versions": [
    "6.5.0-N1QL",
    "6.5.1-6299-enterprise"
  ],
  "#time_normal": "00:00.000",
  "#time_absolute": 0.0000016000000000000001
}
6

Hi @dsullivan,

Both Seems right. Queries are pagination queries, LEFT OUTER, and ORDER BY following left side.

N1QL1: is LEFT OUTER NEST. NEST query can’t use index order it produces all the possible results and does NEST. Does ORDER BY and LIMIT. Can this use Index Order. May be some further optimization in query planner needed (MB-43592).

N1QL2: is LEFT OUTER JOIN. As ORDER BY follows LEFT indexScan and it exposes index order and avoid sort. As it avoided sort it the results are streamed because there is no blocking operations. Once LIMIT reached query stopped. In mean time IndexScan produced 2000 items before stopping.

NOTE: Looks like type is already in index WHERE clause and it is equality. Not required in index key. You can remove from index key. It makes index small.

Might be useful : OFFSET and Keyset Pagination in N1QL Query | Couchbase