Tried again and my machine crashed (cannot connect using ssh anymore).
Anyway, EXPLAIN shows that we use the GSIs, however the amount of documents which satisfy the WHERE clause is very large, i guess something like 500k documents.
Explain:
cbq> EXPLAIN SELECT
meta(usr).id user_id,
IFMISSINGORNULL(usr.sso_providers.FACEBOOK.email,usr.sso_providers.GOOGLE.email,usr.emails[0]) as email,
IFMISSINGORNULL(usr.sso_providers.FACEBOOK.type, usr.sso_providers.GOOGLE.type, ‘EMAIL’) as source
FROM users dvc JOIN users usr ON KEYS dvc.user_id
WHERE
dvc.last_run BETWEEN 1452067057 AND 1452097057
GROUP BY meta(usr).id
EXCEPT
SELECT
meta(usr).id user_id,
IFMISSINGORNULL(usr.sso_providers.FACEBOOK.email,usr.sso_providers.GOOGLE.email,usr.emails[0]) as email,
IFMISSINGORNULL(usr.sso_providers.FACEBOOK.type, usr.sso_providers.GOOGLE.type, ‘EMAIL’) as source
FROM users dvc JOIN users usr ON KEYS dvc.user_id
WHERE
dvc.last_run > 1452097057
GROUP BY meta(usr).id;
{
“requestID”: “dc253169-3b40-42e5-b0d8-6a96cf0c73e4”,
“signature”: “json”,
“results”: [
{
“#operator”: “ExceptAll”,
“first”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “SX_DEVICES_LAST_RUN”,
“keyspace”: “users”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“1.452097057e+09”
],
“Inclusion”: 3,
“Low”: [
“1.452067057e+09”
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “dvc”,
“keyspace”: “users”,
“namespace”: “default”
},
{
“#operator”: “Join”,
“as”: “usr”,
“keyspace”: “users”,
“namespace”: “default”,
“on_keys”: “(dvc.user_id)”
},
{
“#operator”: “Filter”,
“condition”: “((dvc.last_run) between 1.452067057e+09 and 1.452097057e+09)”
},
{
“#operator”: “InitialGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “user_id”,
“expr”: “(meta(usr).id)”
},
{
“as”: “email”,
“expr”: “ifmissingornull((((usr.sso_providers).FACEBOOK).email), (((usr.sso_providers).GOOGLE).email), ((usr.emails)[0]))”
},
{
“as”: “source”,
“expr”: “ifmissingornull((((usr.sso_providers).FACEBOOK).type), (((usr.sso_providers).GOOGLE).type), "EMAIL")”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
},
“second”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “SX_DEVICES_LAST_RUN”,
“keyspace”: “users”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: null,
“Inclusion”: 0,
“Low”: [
“1.452097057e+09”
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “dvc”,
“keyspace”: “users”,
“namespace”: “default”
},
{
“#operator”: “Join”,
“as”: “usr”,
“keyspace”: “users”,
“namespace”: “default”,
“on_keys”: “(dvc.user_id)”
},
{
“#operator”: “Filter”,
“condition”: “(1.452097057e+09 \u003c (dvc.last_run))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: ,
“group_keys”: [
“(meta(usr).id)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “user_id”,
“expr”: “(meta(usr).id)”
},
{
“as”: “email”,
“expr”: “ifmissingornull((((usr.sso_providers).FACEBOOK).email), (((usr.sso_providers).GOOGLE).email), ((usr.emails)[0]))”
},
{
“as”: “source”,
“expr”: “ifmissingornull((((usr.sso_providers).FACEBOOK).type), (((usr.sso_providers).GOOGLE).type), "EMAIL")”
}
]
},
{
“#operator”: “Distinct”
},
{
“#operator”: “FinalProject”
}
]
}
},
{
“#operator”: “Distinct”
}
]
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “148.145007ms”,
“executionTime”: “145.411779ms”,
“resultCount”: 1,
“resultSize”: 9547
}
}