JOIN optimisations

SQL++
,
1

I have document types (one is referring other):

{“id”:“parent_1”,
“type”:“Parent”,
“child”:“child_1”,
“name”:“John”
}

{“id”:“child_1”,
“type”:“Child”,
“name”:“Mike”}

having M-1 one relationship. Also to point out that type Parent has much bigger cardinality. When doing classic JOIN having restriction for child (o.e.):

SELECT *
FROM bucket parent
LEFT OUTER JOIN bucket child
WHERE parent.type = “Parent” AND child.type = “Child” AND child.name = "Mike"
LIMIT 1000

I can never trigger restriction before actual join (no matter what I do, I tried with sub-selects, commutative JOIN, even with LET - defining Child subset), although Child criteria is static and doesn’t depend on Parent field.

Any idea how (and if) this can be done?

Thanks,
Vladimir.

2

try this one:

SELECT * 
FROM bucket parent
 JOIN bucket child ON KEYS parent.child
WHERE parent.type = "Parent" AND child.type = "Child" AND child.name = "Mike"
LIMIT 1000
3

That is the same as upper (just forgot to include USE KEYS). It will still take lot of time!

4

You can join from CHILD to PARENT… You’ll have to add appropriate indexes.
We don’t have RIGHT OUTER JOIN. But, you can INNER to find the matches or LEFT OUTER JOIN to find all of the children without matching parents.

SELECT *
FROM bucket child
JOIN bucket parent ON KEY parent.childid FOR child
WHERE parent.type = “Parent” AND child.type = “Child” AND child.name = "Mike"
LIMIT 1000

See the article for more details:
https://dzone.com/articles/join-faster-with-couchbase-index-joins

5

Yeah, I tried that earlier and it works in similar way.

  1. It will do indexed restrictions
  2. join
  3. apply rest of the criteria

It is obviously how N1QL renders it’s plans, but the problem is that is very unexpected way (or maybe we should say bug), there is no logic on applying isolated criteria after join on set of much bigger cardinality.

Offcourse, I noticed this can be controlled by indexes but creating indexes just to change priority of operations in N1QL is a little bit sloppy.

6

Work in progress :slight_smile: