Couchbase: N1QL JOIN performance issue

Question

I'm getting familiar with Couchbase (I'm getting started with the Server Community Edition), my goal is to migrate our current SQLite database to Couchbase in order to build an efficient real-time synchronization mechanism with mobile devices.

The first steps have been positive so far, we've created buckets (one bucket per SQLite table) and imported all data (one JSON document per SQLite row). Also, in order to allow complex queries and filtering, we've created indices (both primary and secondary) for all buckets.

To summarize, we have two main buckets:

1) players, which contains documents with the following structure

{
  "name": "xxx",
  "transferred": false,
  "value": n,
  "playmaker": false,
  "role": "y",
  "team": "zzz"
}

2) marks, with the following structure (where the "player" field is a reference to a document ID in the players bucket)

{
  "drawgoal": 0,
  "goal": 0,
  "owngoal": 0,
  "enter": 1,
  "mpenalty": 0,
  "gotgoal": 0,
  "ycard": 0,
  "assist": 0,
  "wingoal": 0,
  "mark": 6,
  "penalty": 0,
  "player": "xxx",
  "exit": 0,
  "fmark": 6,
  "team": "yyy",
  "rcard": 0,
  "source": "zzz",
  "day": 1,
  "spenalty": 0
}

So good so far, however when I try to run complex N1QL queries that require a JOIN, performances are pretty bad compared to SQLite. For instance, this query takes around 3 seconds to be executed:

select mark.*, player.`role` from players player join marks mark on key mark.player for player where mark.type = "xxx" and mark.day = n order by mark.team asc, player.`role` desc;

We currently have 600 documents in players (disk used = 16MB, RAM used = 12MB) and 20K documents in marks (disk used = 70MB, RAM used = 17MB), which should not be much from my point of view.

• Are there any settings I can tune to improve JOIN performance? Any specific index I can create?

• Is this performance degradation the price to pay to have more flexibility and more features compared to SQLite?

• Should I avoid as much as possible using JOIN in Couchbase and instead duplicate data where needed?

Thanks

Answer 1

I found the answer :)

By changing the query to:

select marks.*, players.`role` from marks join players on keys marks.player where marks.day = n and marks.type = "xxx" order by marks.team asc, players.`role` desc;

execution time drops to less than 300 milliseconds. Apparently, inverting the JOIN (from marks to players) dramatically improves the performance.

The reason why this query is much faster than the other one is that Couchbase evaluates the query as follows:

• first retrieves all marks documents matching the filtering conditions
• then tries to join them with players documents

By doing so, the number of documents to join is much lower, hence the execution time drops.

Answer 2

I think you've left some details out, so I'm going to fill in the blanks with my guesses. First, a JSON document can't have a field like "value": n. It needs to be a string like "n" or a number like 1. I have assumed you mean a literal number, so I put 1 in there.

Next, let's look at your query:

select m.*, p.`role`
from players p
join marks m on key m.player for p
where m.type = "xxx"
and m.day = 1
order by m.team asc, p.`role` desc;

Again, you had m.day = n, so I put m.day = 1. This query does not run without an index. I'm going to assume you created a primary index (which will scan the whole bucket, and is not ideal for production):

create primary index on players;
create primary index on marks;

The query still doesn't run, so you must have added an index on the 'players' field in marks:

create index ix_marks_player on marks(player);

The query runs, but returns no results, because your example documents are missing a "type": "xxx" field. So I added that field, and now your query runs.

Look at the Plan Text by just clicking "plan text" (If you were using Enterprise, you would see a visual version of the Plan diagram).

The plan text shows that the query is using a PrimaryScan on the players bucket. Indeed, your query is attempting to join every player document. So as the player bucket grows, the query will get slower.

In your answer here on SO, you say that a different query to get the same data works faster:

select m.*, p.`role`
from marks m
join players p on keys m.player
where m.day = 1
and m.type = "xxx"
order by m.team asc, p.`role` desc;

You swapped the join, but looking at the plan text, you are still running a PrimaryScan. This time it's scanning all the marks documents. I'm assuming you have fewer of those (either fewer total, or since you are filtering on day you have fewer to join).

So my answer is basically: do you always need to join all of the documents? If so, why? If not, I suggest you modify your query to add a LIMIT/OFFSET (perhaps for paging) or some other filter so you aren't querying for everything.

One more point: it looks like you are using buckets for organizational purposes. This isn't strictly wrong, but it's not really going to scale. Buckets are distributed across the cluster, so you are limited in the number of buckets you can reasonably use (there may even be a hard limit at 10 buckets). I don't know your use case, but often it's better to use a "type"/"_type"/"docType"/etc value in your documents for organization instead of relying on buckets.

Answer 3

The first steps have been positive so far, we've created buckets (one bucket per SQLite table) and imported all data (one JSON document per SQLite row)

You have a problem here. You have tried to map a SQL database schema to a document database schema without regard for best practices or even dire warnings in Couchbase's documentation.

First, you should be using one bucket. A bucket is more like a database than a table (although it's more complex than that) and Couchbase recommends using a single bucket per cluster unless you have a very good reason not to. It helps with performance, scaling, and resource utilization. Each of your documents should have a field that indicates the type of data. This is what separates your "tables". I use a field named '_type'. Eg. you will have 'player' and 'mark' document types.

Second, you should rethink importing the data as one row per document. Document databases give you different schema options and some are very useful for improving performance. You certainly can keep it this way, but it's probably not optimal. This is a common pitfall that developers run into when first using a NoSQL database.

One good example is in one to many relationships. Instead of having many mark documents for a single player document, you can embed the marks as an array inside the player document. The document can store arrays of objects!

Eg.

{
  "name": "xxx",
  "transferred": false,
  "value": n,
  "playmaker": false,
  "role": "y",
  "team": "zzz",
  "_type": "player",
  "marks": [
    "mark": {
      "drawgoal": 0,
      "goal": 0,
      "owngoal": 0,
      "enter": 1,
    },
    "mark": {
      "drawgoal": 0,
      "goal": 0,
      "owngoal": 0,
      "enter": 1,
    },
    "mark": {
      "drawgoal": 0,
      "goal": 0,
      "owngoal": 0,
      "enter": 1,
    }
  ]
}

You can do this for team and role as well, but it sounds like that would denormalize things which you may not be ready to deal with and isn't always a good idea.

Couchbase can index inside the JSON, so you can still use N1QL to query the marks from all players. This also lets you pull a player's document and marks in a single key:value call, which is the fastest kind.