Query Execution taking more time then expected

Couchbase Server
1

I have a query that is taking more time then required for execution. Below is my query (Its taking about 27 to 30 seconds)

SELECT
s.platform,
COUNT(DISTINCT s.channel) AS Channels,
COUNT(DISTINCT s.advertisement) AS Advertisements,
SUM(CASE WHEN s.click = 1 THEN 1 ELSE 0 END) AS TotalClicks,
SUM(CASE WHEN s.impression == 1 THEN 1 ELSE 0 END) AS TotalImpressions,
COUNT (DISTINCT (CASE WHEN s.click = 1 THEN ip ELSE NULL END)) As UniqueClicks,
COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN ip ELSE NULL END)) As UniqueImpressions,
COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionImpressions,
COUNT(DISTINCT (CASE WHEN s.click = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionClicks
FROM
Advertisement AS s
WHERE
s.date >= ‘2017-01-27’ AND s.date <= ‘2017-01-31’ GROUP BY s.platform ORDER BY platform;

Addition to this i have created the following indexes

CREATE PRIMARY INDEX #primary ON Advertisement
CREATE INDEX date_idx_advertisement ON Advertisement(date)
CREATE INDEX id_ix_advertisment ON Advertisement((meta().id))
CREATE INDEX platform_idx_advertisement ON Advertisement(platform)

And below is the explain of my query

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “date_idx_advertisement”,
“index_id”: “1ea8091a22342352”,
“keyspace”: “Advertisement”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“2017-01-31"”
],
“Inclusion”: 3,
“Low”: [
"“2017-01-27"”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “s”,
“keyspace”: “Advertisement”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((“2017-01-27” <= (s.date)) and ((s.date) <= “2017-01-31”))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“count(distinct (s.advertisement))”,
“count(distinct (s.channel))”,
“count(distinct case when ((s.click) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.click) = 1) then (s.ip) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.ip) else null end)”,
“sum(case when ((s.click) = 1) then 1 else 0 end)”,
“sum(case when ((s.impression) = 1) then 1 else 0 end)”
],
“group_keys”: [
"(s.platform)"
]
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“count(distinct (s.advertisement))”,
“count(distinct (s.channel))”,
“count(distinct case when ((s.click) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.click) = 1) then (s.ip) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.ip) else null end)”,
“sum(case when ((s.click) = 1) then 1 else 0 end)”,
“sum(case when ((s.impression) = 1) then 1 else 0 end)”
],
“group_keys”: [
"(s.platform)"
]
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“count(distinct (s.advertisement))”,
“count(distinct (s.channel))”,
“count(distinct case when ((s.click) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.click) = 1) then (s.ip) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.f2fsession) else null end)”,
“count(distinct case when ((s.impression) = 1) then (s.ip) else null end)”,
“sum(case when ((s.click) = 1) then 1 else 0 end)”,
“sum(case when ((s.impression) = 1) then 1 else 0 end)”
],
“group_keys”: [
"(s.platform)"
]
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“expr”: “(s.platform)”
},
{
“as”: “Channels”,
“expr”: “count(distinct (s.channel))”
},
{
“as”: “Advertisements”,
“expr”: “count(distinct (s.advertisement))”
},
{
“as”: “TotalClicks”,
“expr”: “sum(case when ((s.click) = 1) then 1 else 0 end)”
},
{
“as”: “TotalImpressions”,
“expr”: “sum(case when ((s.impression) = 1) then 1 else 0 end)”
},
{
“as”: “UniqueClicks”,
“expr”: “count(distinct case when ((s.click) = 1) then (s.ip) else null end)”
},
{
“as”: “UniqueImpressions”,
“expr”: “count(distinct case when ((s.impression) = 1) then (s.ip) else null end)”
},
{
“as”: “UniqueSessionImpressions”,
“expr”: “count(distinct case when ((s.impression) = 1) then (s.f2fsession) else null end)”
},
{
“as”: “UniqueSessionClicks”,
“expr”: “count(distinct case when ((s.click) = 1) then (s.f2fsession) else null end)”
}
]
}
]
}
}
]
},
{
"#operator": “Order”,
“sort_terms”: [
{
“expr”: “(s.platform)”
}
]
},
{
"#operator": “FinalProject”
}
]
},
“text”: “SELECT \n s.platform, \n COUNT(DISTINCT s.channel) AS Channels, \n COUNT(DISTINCT s.advertisement) AS Advertisements, \n SUM(CASE WHEN s.click = 1 THEN 1 ELSE 0 END) AS TotalClicks, \n SUM(CASE WHEN s.impression == 1 THEN 1 ELSE 0 END) AS TotalImpressions, \n COUNT (DISTINCT (CASE WHEN s.click = 1 THEN ip ELSE NULL END)) As UniqueClicks, \n COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN ip ELSE NULL END)) As UniqueImpressions,\n COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionImpressions, \n COUNT(DISTINCT (CASE WHEN s.click = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionClicks \nFROM \n Advertisement AS s \nWHERE \n s.date >= ‘2017-01-27’ AND s.date <= ‘2017-01-31’ GROUP BY s.platform ORDER BY platform;”
}
]

CouchBase Server Version used : 4.5.0-2601 Community Edition (build-2601)

Can some one please help me to know what index i am missing or why it is taking too long.

Your kind co-operation is highly appreciated.

2

try to add this index:

CREATE INDEX idx_dpcaciif_adv ON Advertisement(date,platform,channel,advertisement,click,impression,ip,f2fsession) USING GSI;
1 Like
3

hello @atom_yang

Thanks for the reply i tried with the suggested index, it worked but still it takes more time. below is the query and explain of the same (In addition to that i have 1,70,000 documents in my bucket, current execution time is near to 15 seconds).

SELECT
s.platform,
s.channel,
COUNT(DISTINCT s.advertisement) AS Advertisements,
SUM(CASE WHEN s.click = 1 THEN 1 ELSE 0 END) AS TotalClicks,
SUM(CASE WHEN s.impression == 1 THEN 1 ELSE 0 END) AS TotalImpressions,
COUNT (DISTINCT (CASE WHEN s.click = 1 THEN ip ELSE NULL END)) As UniqueClicks,
COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN ip ELSE NULL END)) As UniqueImpressions,
COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionImpressions,
COUNT(DISTINCT (CASE WHEN s.click = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionClicks
FROM
Advertisement AS s
WHERE
s.date >= ‘2017-01-27’ AND s.date <= ‘2017-01-31’ GROUP BY s.platform, s.channel ORDER BY platform;

[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“covers”: [
“cover ((s.date))”,
“cover ((s.platform))”,
“cover ((s.channel))”,
“cover ((s.advertisement))”,
“cover ((s.click))”,
“cover ((s.impression))”,
“cover ((s.ip))”,
“cover ((s.f2fsession))”,
“cover ((meta(s).id))”
],
“index”: “idx_dpcaciif_adv”,
“index_id”: “4b40baeefedb03ed”,
“keyspace”: “Advertisement”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor("2017-01-31")”
],
“Inclusion”: 1,
“Low”: [
“"2017-01-27"”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(("2017-01-27" <= cover ((s.date))) and (cover ((s.date)) <= "2017-01-31"))”
},
{
“#operator”: “InitialGroup”,
“aggregates”: [
“count(distinct cover ((s.advertisement)))”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.ip)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.ip)) else null end)”,
“sum(case when (cover ((s.click)) = 1) then 1 else 0 end)”,
“sum(case when (cover ((s.impression)) = 1) then 1 else 0 end)”
],
“group_keys”: [
“cover ((s.platform))”,
“cover ((s.channel))”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“count(distinct cover ((s.advertisement)))”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.ip)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.ip)) else null end)”,
“sum(case when (cover ((s.click)) = 1) then 1 else 0 end)”,
“sum(case when (cover ((s.impression)) = 1) then 1 else 0 end)”
],
“group_keys”: [
“cover ((s.platform))”,
“cover ((s.channel))”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“count(distinct cover ((s.advertisement)))”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.click)) = 1) then cover ((s.ip)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.f2fsession)) else null end)”,
“count(distinct case when (cover ((s.impression)) = 1) then cover ((s.ip)) else null end)”,
“sum(case when (cover ((s.click)) = 1) then 1 else 0 end)”,
“sum(case when (cover ((s.impression)) = 1) then 1 else 0 end)”
],
“group_keys”: [
“cover ((s.platform))”,
“cover ((s.channel))”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover ((s.platform))”
},
{
“expr”: “cover ((s.channel))”
},
{
“as”: “Advertisements”,
“expr”: “count(distinct cover ((s.advertisement)))”
},
{
“as”: “TotalClicks”,
“expr”: “sum(case when (cover ((s.click)) = 1) then 1 else 0 end)”
},
{
“as”: “TotalImpressions”,
“expr”: “sum(case when (cover ((s.impression)) = 1) then 1 else 0 end)”
},
{
“as”: “UniqueClicks”,
“expr”: “count(distinct case when (cover ((s.click)) = 1) then cover ((s.ip)) else null end)”
},
{
“as”: “UniqueImpressions”,
“expr”: “count(distinct case when (cover ((s.impression)) = 1) then cover ((s.ip)) else null end)”
},
{
“as”: “UniqueSessionImpressions”,
“expr”: “count(distinct case when (cover ((s.impression)) = 1) then cover ((s.f2fsession)) else null end)”
},
{
“as”: “UniqueSessionClicks”,
“expr”: “count(distinct case when (cover ((s.click)) = 1) then cover ((s.f2fsession)) else null end)”
}
]
}
]
}
}
]
},
{
“#operator”: “Order”,
“sort_terms”: [
{
“expr”: “cover ((s.platform))”
}
]
},
{
“#operator”: “FinalProject”
}
]
},
“text”: “SELECT \n s.platform, \n s.channel, \n COUNT(DISTINCT s.advertisement) AS Advertisements, \n SUM(CASE WHEN s.click = 1 THEN 1 ELSE 0 END) AS TotalClicks, \n SUM(CASE WHEN s.impression == 1 THEN 1 ELSE 0 END) AS TotalImpressions, \n COUNT (DISTINCT (CASE WHEN s.click = 1 THEN ip ELSE NULL END)) As UniqueClicks, \n COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN ip ELSE NULL END)) As UniqueImpressions,\n COUNT (DISTINCT (CASE WHEN s.impression = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionImpressions, \n COUNT(DISTINCT (CASE WHEN s.click = 1 THEN f2fsession ELSE NULL END)) As UniqueSessionClicks \nFROM \n Advertisement AS s \nWHERE \n s.date >= ‘2017-01-27’ AND s.date <= ‘2017-01-31’ GROUP BY s.platform, s.channel ORDER BY platform;”
}
]

Please help me on the same.

Regards
Siddharth

4

Set max parallelism to 2x number of cores. cc @keshav_m