performance in 4.5 create gsi index

Question

I have below query.

SELECT DailyCampaignUsage.day date, sum (ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicks)) clicks,   
sum (ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)) revenue   
FROM Inheritx DailyCampaignUsage 
JOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId))  
JOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`))     
WHERE DailyCampaignUsage._type='DailyCampaignUsage'  and      
DATE_PART_MILLIS(STR_TO_MILLIS(DailyCampaignUsage.day),'year')=2016       
and DATE_PART_MILLIS(STR_TO_MILLIS(DailyCampaignUsage.day),'month')=5  
group by DailyCampaignUsage.day
order by DailyCampaignUsage.day

I have no only index on _type like

CREATE INDEX `Ottoman__type` ON `Inheritx`(`_type`)

when I run above query it is taking 10s

When I try to create some index like

  CREATE INDEX `dailyCampaignUsage_type_clicks_cost` ON  
 `Inheritx`(_type,day,`statistics`[*].clicks,`statistics`[*].clicksCost) WHERE
 `_type` = "DailyCampaignUsage"  USING GSI

But It is not working it taking more time 13s. I have also use use index (dailyCampaignUsage_type_clicks_cost) But do not work.

which Index should I create ?

Answer 1

can you post the sample document, EXPLAIN output, and how many documents you have, couchbase version, cluster setup (all services on same node, or using MDS-multi dimensional scaling) etc.

You may want to try including the year, month as index keys..

CREATE INDEX dailyCampaignUsage_type_clicks_cost ON
Inheritx(_type, DATE_PART_MILLIS(STR_TO_MILLIS(DailyCampaignUsage.day),'year'), DATE_PART_MILLIS(STR_TO_MILLIS(DailyCampaignUsage.day),'month'), day...) WHERE ...