I have below query
explain SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,
ROUND(sum(CASE WHEN DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,
ROUND(sum(CASe WHEN DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue
from Inheritx DailyCampaignUsage use index(dailyCampaignUsage_type_day_clicksCost)
JOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId))
JOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`))
WHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__ ORDER BY `created` DESC
I have below index
CREATE INDEX dailyCampaignUsage_type_day_clicksCost ON Inheritx
(_type,day,`statistics`[*].clicksCost) WHERE _type='DailyCampaignUsage'
which I have use in query.
my explain plan is blow.
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "dailyCampaignUsage_type_day_clicksCost",
"index_id": "37387d27d560354b",
"keyspace": "Inheritx",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"DailyCampaignUsage\")"
],
"Inclusion": 1,
"Low": [
"\"DailyCampaignUsage\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "DailyCampaignUsage",
"keyspace": "Inheritx",
"namespace": "default"
},
{
"#operator": "Join",
"as": "Campaign",
"keyspace": "Inheritx",
"namespace": "default",
"on_keys": "(\"Campaign|\" || to_string((`DailyCampaignUsage`.`campaignId`)))"
},
{
"#operator": "Join",
"as": "Users",
"keyspace": "Inheritx",
"namespace": "default",
"on_keys": "(\"User|\" || to_string((`Campaign`.`user`)))"
},
{
"#operator": "Filter",
"condition": "(((`DailyCampaignUsage`.`_type`) = \"DailyCampaignUsage\") and case when false then ((`Users`.`user`) in false) else true end)"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
"sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
],
"group_keys": []
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "total_revenue",
"expr": "round(sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`))), 2)"
},
{
"as": "period_revenue",
"expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-05-01\" and \"2016-05-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
},
{
"as": "period_prev_revenue",
"expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between \"2016-04-01\" and \"2016-04-23\") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Alias",
"as": "__viewdef__"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"desc": true,
"expr": "(`__viewdef__`.`created`)"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,\nROUND(sum(CASE WHEN DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,\nROUND(sum(CASe WHEN DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue \nfrom Inheritx DailyCampaignUsage use index(dailyCampaignUsage_type_day_clicksCost)\nJOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId)) \nJOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`)) \nWHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__ ORDER BY `created` DESC"
}
even index using I can not reduce it execution. it is 13s how I can make it around 300 to 500ms ?? my json like below I have 50k+ json
DailyCampaignUsage|006657c0-c696-11e6-b6f2-7f0166ec7527{
"_id": "006657c0-c696-11e6-b6f2-7f0166ec7527",
"_type": "DailyCampaignUsage",
"campaignId": 249,
"day": "2015-11-19T00:00:00Z",
"statistics": [
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
"clicks": 1741,
"clicksCost": 48.748
}
]
}
1) can you first try to optimize the inner query. Make it use covering indexes by including all fields of the first keyspace used in where/projections/join-on-keys in the index definition. See https://developer.couchbase.com/documentation/server/4.5/indexes/covering-indexes.html. Last example in the documentation applies to you. Something like:
2) You can try Memory Optimized Indexes (MOI) to tremendously increase the perf. This needs Enterprise edition. See https://developer.couchbase.com/documentation/server/4.5/architecture/global-secondary-indexes.html#story-h2-2
3) The outer query is only doing order by on 'created' which is not projected by the inner query. If it is in the first keyspace, include that in index.
4) Also check the WHERE condition with CASE on Users.user. It always evaluates to true. Not sure if you need the 3rd Join.
hth, -Prasad