Need to create proper Index

I have below query
here DATE is dynamically

select count(Campaign._id)total_campaigns,
SUM(CASE WHEN Campaign.Campaign.status=3 then 1 ELSE 0 END ) total_pending_campaigns,
SUM(CASE WHEN Campaign.status=0 THEN 1 ELSE 0 END) total_active_campaigns,
SUM(CASE WHEN Campaign.status=1 THEN 1 ELSE 0 END) total_paused_campaigns,
SUM(CASE WHEN Campaign.status=5 THEN 1 ELSE 0 END) total_decline_campaigns,
SUM(CASE WHEN Campaign.status=3 then 1 ELSE 0 END ) total_pending_activcation_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ THEN 1 ELSE 0 END) period_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ AND Campaign.status=3 THEN 1 ELSE 0 END) period_pending_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ AND Campaign.status=0 THEN 1 ELSE 0 END) period_active_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ AND Campaign.status=1 THEN 1 ELSE 0 END) period_paused_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ AND Campaign.status=5 THEN 1 ELSE 0 END) period_decline_campaigns,
SUM(CASE WHEN Campaign.created between ‘2016-12-01’ and ‘2016-12-24’ AND Campaign.status=3 THEN 1 ELSE 0 END) period_pending_activcation_campaigns
from Inheritx Campaign join Inheritx Users on keys(‘User|’|| TOSTRING(Campaign.user)) where Campaign._type=‘Campaign’

my explain is below

[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “Campaign_type”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“Campaign”"
],
“Inclusion”: 3,
“Low”: [
"“Campaign”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “Campaign”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “Users”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_keys”: “(“User|” || to_string((Campaign.user)))”
},
{
"#operator": “Filter”,
“condition”: “(((Campaign._type) = “Campaign”) and case when false then ((Users.user) in false) else true end)”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“count((Campaign._id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between “2016-12-01” and “2016-12-24”) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“count((Campaign._id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between “2016-12-01” and “2016-12-24”) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“count((Campaign._id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between “2016-12-01” and “2016-12-24”) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “total_campaigns”,
“expr”: “count((Campaign._id))”
},
{
“as”: “total_pending_campaigns”,
“expr”: “sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”
},
{
“as”: “total_active_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 0) then 1 else 0 end)”
},
{
“as”: “total_paused_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 1) then 1 else 0 end)”
},
{
“as”: “total_decline_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
},
{
“as”: “total_pending_activcation_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 3) then 1 else 0 end)”
},
{
“as”: “period_campaigns”,
“expr”: “sum(case when ((Campaign.created) between “2016-12-01” and “2016-12-24”) then 1 else 0 end)”
},
{
“as”: “period_pending_campaigns”,
“expr”: “sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 3)) then 1 else 0 end)”
},
{
“as”: “period_active_campaigns”,
“expr”: “sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 0)) then 1 else 0 end)”
},
{
“as”: “period_paused_campaigns”,
“expr”: “sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 1)) then 1 else 0 end)”
},
{
“as”: “period_decline_campaigns”,
“expr”: “sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 5)) then 1 else 0 end)”
},
{
“as”: “period_pending_activcation_campaigns”,
“expr”: “sum(case when (((Campaign.created) between “2016-12-01” and “2016-12-24”) and ((Campaign.status) = 3)) then 1 else 0 end)”
}
]
}
]
}
}
]
},
{
"#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”
}
]
}
]

It is taking 2.49s
I have created below indexes
CREATE INDEX CampaignCreated ON Inheritx(created) WHERE (_type = “Campaign”) USING GSI
CREATE INDEX Campaign_status ON Inheritx(status) WHERE ((_type = “Campaign”) or (_type is missing)) USING GSI
CREATE INDEX Campaign_created_status ON
Inheritx(created,status) WHERE ((_type = “Campaign”) or (_type
is missing)) USING GSI

How can I improve it’s performance ?

Why you add this condition, but query don’t have this condition?

How you define index? how about remove all index, and create this index:

CREATE INDEX Campaign_status_Created_user ON Inheritx(Campaign.status,status,created,user) WHERE (_type = "Campaign") USING GSI
1 Like

I have change query also by USE INDEX
I have done below Index even it is taking 9.19s

CREATE INDEX Campaign_status_Created_user ON Inheritx(status,created,user) WHERE (_type = “Campaign”) USING GSI

my explain plan is below

{
#operator”: “Sequence”,
“~children”: [
{
#operator”: “PrimaryScan”,
“index”: “InheritxPI”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“using”: “gsi”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Fetch”,
“as”: “Campaign”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
#operator”: “Filter”,
“condition”: “((Campaign._type) = "Campaign")”
},
{
#operator”: “InitialGroup”,
“aggregates”: [
“count((Campaign.id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between "2016-12-01" and "2016-12-24") then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”:
}
]
}
},
{
#operator”: “IntermediateGroup”,
“aggregates”: [
“count((Campaign.id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between "2016-12-01" and "2016-12-24") then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”:
},
{
#operator”: “FinalGroup”,
“aggregates”: [
“count((Campaign.id))”,
“sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 0)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 1)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 3)) then 1 else 0 end)”,
“sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 5)) then 1 else 0 end)”,
“sum(case when ((Campaign.created) between "2016-12-01" and "2016-12-24") then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 0) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 1) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 3) then 1 else 0 end)”,
“sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
],
“group_keys”:
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “totalcampaigns”,
“expr”: “count((Campaign.id))”
},
{
“as”: “total_pending_campaigns”,
“expr”: “sum(case when (((Campaign.Campaign).status) = 3) then 1 else 0 end)”
},
{
“as”: “total_active_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 0) then 1 else 0 end)”
},
{
“as”: “total_paused_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 1) then 1 else 0 end)”
},
{
“as”: “total_decline_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 5) then 1 else 0 end)”
},
{
“as”: “total_pending_activcation_campaigns”,
“expr”: “sum(case when ((Campaign.status) = 3) then 1 else 0 end)”
},
{
“as”: “period_campaigns”,
“expr”: “sum(case when ((Campaign.created) between "2016-12-01" and "2016-12-24") then 1 else 0 end)”
},
{
“as”: “period_pending_campaigns”,
“expr”: “sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 3)) then 1 else 0 end)”
},
{
“as”: “period_active_campaigns”,
“expr”: “sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 0)) then 1 else 0 end)”
},
{
“as”: “period_paused_campaigns”,
“expr”: “sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 1)) then 1 else 0 end)”
},
{
“as”: “period_decline_campaigns”,
“expr”: “sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 5)) then 1 else 0 end)”
},
{
“as”: “period_pending_activcation_campaigns”,
“expr”: “sum(case when (((Campaign.created) between "2016-12-01" and "2016-12-24") and ((Campaign.status) = 3)) then 1 else 0 end)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
}

USE INDEX don’t works, because the query don’t match index : user
and

you should also add Campaign.status into index if you want to use CoverIndex

maybe you can remove all your index, and redefine the index and try again.