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 ?