Perfomance issue with JOIN on same Bucket

SQL++
1

Hello,
I like to make a JOIN with two kinds of documents within same bucket.
The key is the event.story which contains the id of the story.

Story
{
"_id": “9BF5BDC7-C195-4135-8DB8-6E98E586D6ED”,
“editor”: “7c1955e9-d044-4f17-bfb3-e319686a4a83”,
“model_type”: “Story”,
“title”: “Bach”
},

Event
{
"_id": “00000026-4644-42ac-854d-40e3762959ce”,
“epochDate”: “2015-12-10T13:16:33.449Z”,
“model_type”: “Event”,
“story”: “9BF5BDC7-C195-4135-8DB8-6E98E586D6ED”,
“type”: 1
},

I use WHERE to specify an editor and type.

Query

SELECT event._id AS eventId, event.epochDate AS eventDate, event.type AS eventType, story._id AS storyId, story.editor AS editor, story.type AS storyType
FROM storyplayer-api event INNER JOIN storyplayer-api story ON KEYS event.story
WHERE (story.editor = “ECC82E13-4050-4B27-A639-76293F5F27C8” and (event.type = 1 or event.type = 2))

I create 3 INDEX but only event_type is use.
CREATE INDEX event_story ON storyplayer-api(story) ;
CREATE INDEX event_editor ON storyplayer-api(editor) ;
CREATE INDEX event_type ON storyplayer-api(type) ;

I am getting output in sever minutes like 7 or 8 minutes.
I think i don’t use Index in the good way…
How can i improve the performance?

Thanks
Franck

2

If i don’have my event_type INDEX , the primary INDEX is use.

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “DistinctScan”,
“scan”: {
"#operator": “IndexScan”,
“index”: “event_type”,
“index_id”: “900d181942306200”,
“keyspace”: “storyplayer-api”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“1”
],
“Inclusion”: 3,
“Low”: [
“1”
]
}
},
{
“Range”: {
“High”: [
“2”
],
“Inclusion”: 3,
“Low”: [
“2”
]
}
}
],
“using”: “gsi”
}
},
{
"#operator": “Fetch”,
“as”: “event”,
“keyspace”: “storyplayer-api”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Join”,
“as”: “story”,
“keyspace”: “storyplayer-api”,
“namespace”: “default”,
“on_keys”: “(event.story)”
},
{
"#operator": “Filter”,
“condition”: “(((story.editor) = “ECC82E13-4050-4B27-A639-76293F5F27C8”) and (((event.type) = 1) or ((event.type) = 2)))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “eventId”,
“expr”: “(event._id)”
},
{
“as”: “eventDate”,
“expr”: “(event.epochDate)”
},
{
“as”: “eventType”,
“expr”: “(event.type)”
},
{
“as”: “storyId”,
“expr”: “(story._id)”
},
{
“as”: “editor”,
“expr”: “(story.editor)”
},
{
“as”: “storyType”,
“expr”: “(story.type)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT event._id AS eventId, event.epochDate AS eventDate, event.type AS eventType, story._id AS storyId, story.editor AS editor, story.type AS storyType\nFROM storyplayer-api event INNER JOIN storyplayer-api story ON KEYS event.story WHERE (story.editor = “ECC82E13-4050-4B27-A639-76293F5F27C8” and (event.type = 1 or event.type = 2))”
}
]

3

Try the following index.

CREATE INDEX event_type2 ON storyplayer-api(type, story, epochDate,_id) ;

Can you post the document keys for both the documents.

4

Thanks a lot for your response,
What do you mean by document keys, is it meta like these?

{
"_id": “00265b1f-6911-49e2-8109-a91dbb7f1285”,
“editor”: “7c1955e9-d044-4f17-bfb3-e319686a4a83”,
“meta”: {
“cas”: 1508943609056460800,
“flags”: 33554432,
“id”: “00265b1f-6911-49e2-8109-a91dbb7f1285”,
“type”: “json”
},
“model_type”: “Story”,
“title”: “Bach”
},

{
"_id": “00000026-4644-42ac-854d-40e3762959ce”,
“epochDate”: “2015-12-10T13:16:33.449Z”,
“meta”: {
“cas”: 1503481147068317700,
“flags”: 33554432,
“id”: “00000026-4644-42ac-854d-40e3762959ce”,
“type”: “json”
},
“model_type”: “Event”,
“story”: “9BF5BDC7-C195-4135-8DB8-6E98E586D6ED”,
“type”: 1
},

I create the INDEX and it’s working. Time response is 2 time faster (4min).

But i’m still very confuse with INDEX use…

I’m trying to create INDEX with WHERE conditions lke this
CREATE INDEX event_type ON storyplayer-api(type,story,epochDate,_id,pageIndex,editor) WHERE (((type = 1) or (type = 2)) and (pageIndex = 1))

Query use the primary INDEX not the one i create
SELECT story.pageIndex AS pageIndex, event._id AS eventId, event.epochDate AS eventDate, event.type AS eventType, story._id AS storyId, story.editor AS editor, story.type AS storyType
FROM storyplayer-api event USE INDEX (event_type) INNER JOIN storyplayer-api story ON KEYS event.story
WHERE (story.editor = “ECC82E13-4050-4B27-A639-76293F5F27C8”)

Thanks,
Franck

5

Document key means META().id of the document
event_type index doesn’t qualify the query because it using partial index and query predicate doesn’t contain index where clause predicate, so it uses primary index. Also don’t use OR clause in Index condition.

CREATE INDEX event_type ON storyplayer-api(type,story,epochDate,_id,pageIndex,editor) ;

Please take look https://dzone.com/articles/designing-index-for-query-in-couchbase-n1ql

1 Like