Need to use index with multiple where condition

SQL++
,
1

I am run below query. it is taking 30s

 SELECT prevRevenue AS prevRevenue, TOSTRING(companyId) AS companyId, companyName AS companyName,  COUNT(*) AS nOfCampaigns, SUM(clicks) AS clicks,SUM(revenue)
AS revenue, SUM(conversions) AS conversions, AVG(conversions / clicks) AS conversionsRate, AVG(revenue / conversions) AS conversionAvgCost 
FROM (SELECT companyId AS companyId, companyName AS companyName, campaignId, SUM(clicks) AS clicks ,SUM(revenue) AS revenue, SUM(conversions) AS conversions, 
SUM(prevRevenue) AS prevRevenue FROM (SELECT dateMatch AS dateMatch, intervalMillis AS intervalMillis, companyId AS companyId, companyName AS companyName,
campaignId AS campaignId, CASE WHEN dateMatch THEN clicks ELSE 0 END AS clicks, CASE WHEN dateMatch THEN revenue ELSE 0 END AS revenue,
CASE WHEN dateMatch THEN conversions ELSE 0 END AS conversions, day AS day,
CASE WHEN (MILLIS(day) >=  MILLIS('2016-05-01') - intervalMillis AND MILLIS(day) <=  MILLIS('2016-05-22') - intervalMillis)  THEN revenue ELSE 0 END  AS prevRevenue 
FROM (SELECT (du.day >= '2016-05-01' AND du.day <= '2016-05-22') AS dateMatch, MILLIS('2016-05-22') - MILLIS('2016-05-01') as intervalMillis, 
c._id AS companyId, c.name AS companyName, ca._id AS campaignId, ARRAY_SUM(du.`statistics`[*].clicks) as clicks,
ARRAY_SUM(du.`statistics`[*].clicksCost) as revenue,     ARRAY_SUM(du.`statistics`[*].conversions) as conversions, du.day As day 
FROM `Inheritx` u  use INDEX(DailyCampaignUsage_Day) JOIN `Inheritx` c ON KEYS ['Company|' || TOSTRING(u.company) ] 
LEFT OUTER JOIN `Inheritx` ca ON KEY 'User|' || TOSTRING(ca.`user`)  FOR u 
LEFT OUTER JOIN `Inheritx` du ON KEY 'Campaign|' ||  TOSTRING(du.campaignId) 
FOR ca WHERE u._type = 'User' AND (c._type = 'Company' AND 2 in c.companyTypes)
AND (ca._type = 'Campaign' OR ca._type IS missing) AND (du._type = 'DailyCampaignUsage' OR du._type IS missing)) T) T1 
GROUP BY T1.campaignId) T2 GROUP BY T2.companyId

my explain plan is below

    [
  {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "Sequence",
        "~children": [
          {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Sequence",
                "~children": [
                  {
                    "#operator": "PrimaryScan",
                    "index": "InheritxPI",
                    "keyspace": "Inheritx",
                    "namespace": "default",
                    "using": "gsi"
                  },
                  {
                    "#operator": "Parallel",
                    "~child": {
                      "#operator": "Sequence",
                      "~children": [
                        {
                          "#operator": "Fetch",
                          "as": "u",
                          "keyspace": "Inheritx",
                          "namespace": "default"
                        },
                        {
                          "#operator": "Join",
                          "as": "c",
                          "keyspace": "Inheritx",
                          "namespace": "default",
                          "on_keys": "[(\"Company|\" || to_string((`u`.`company`)))]"
                        },
                        {
                          "#operator": "IndexJoin",
                          "as": "ca",
                          "for": "u",
                          "keyspace": "Inheritx",
                          "namespace": "default",
                          "on_key": "(\"User|\" || to_string((`ca`.`user`)))",
                          "outer": true,
                          "scan": {
                            "index": "campaign_user",
                            "using": "gsi"
                          }
                        },
                        {
                          "#operator": "IndexJoin",
                          "as": "du",
                          "for": "ca",
                          "keyspace": "Inheritx",
                          "namespace": "default",
                          "on_key": "(\"Campaign|\" || to_string((`du`.`campaignId`)))",
                          "outer": true,
                          "scan": {
                            "index": "dailyCampaignUsage_campaign",
                            "using": "gsi"
                          }
                        },
                        {
                          "#operator": "Filter",
                          "condition": "(((((`u`.`_type`) = \"User\") and (((`c`.`_type`) = \"Company\") and (2 in (`c`.`companyTypes`)))) and (((`ca`.`_type`) = \"Campaign\") or ((`ca`.`_type`) is missing))) and (((`du`.`_type`) = \"DailyCampaignUsage\") or ((`du`.`_type`) is missing)))"
                        },
                        {
                          "#operator": "InitialProject",
                          "result_terms": [
                            {
                              "as": "dateMatch",
                              "expr": "((\"2016-05-01\" <= (`du`.`day`)) and ((`du`.`day`) <= \"2016-05-22\"))"
                            },
                            {
                              "as": "intervalMillis",
                              "expr": "(str_to_millis(\"2016-05-22\") - str_to_millis(\"2016-05-01\"))"
                            },
                            {
                              "as": "companyId",
                              "expr": "(`c`.`_id`)"
                            },
                            {
                              "as": "companyName",
                              "expr": "(`c`.`name`)"
                            },
                            {
                              "as": "campaignId",
                              "expr": "(`ca`.`_id`)"
                            },
                            {
                              "as": "clicks",
                              "expr": "array_sum((array_star((`du`.`statistics`)).`clicks`))"
                            },
                            {
                              "as": "revenue",
                              "expr": "array_sum((array_star((`du`.`statistics`)).`clicksCost`))"
                            },
                            {
                              "as": "conversions",
                              "expr": "array_sum((array_star((`du`.`statistics`)).`conversions`))"
                            },
                            {
                              "as": "day",
                              "expr": "(`du`.`day`)"
                            }
                          ]
                        },
                        {
                          "#operator": "FinalProject"
                        }
                      ]
                    }
                  }
                ]
              },
              {
                "#operator": "Alias",
                "as": "T"
              },
              {
                "#operator": "Parallel",
                "~child": {
                  "#operator": "Sequence",
                  "~children": [
                    {
                      "#operator": "InitialProject",
                      "result_terms": [
                        {
                          "as": "dateMatch",
                          "expr": "(`T`.`dateMatch`)"
                        },
                        {
                          "as": "intervalMillis",
                          "expr": "(`T`.`intervalMillis`)"
                        },
                        {
                          "as": "companyId",
                          "expr": "(`T`.`companyId`)"
                        },
                        {
                          "as": "companyName",
                          "expr": "(`T`.`companyName`)"
                        },
                        {
                          "as": "campaignId",
                          "expr": "(`T`.`campaignId`)"
                        },
                        {
                          "as": "clicks",
                          "expr": "case when (`T`.`dateMatch`) then (`T`.`clicks`) else 0 end"
                        },
                        {
                          "as": "revenue",
                          "expr": "case when (`T`.`dateMatch`) then (`T`.`revenue`) else 0 end"
                        },
                        {
                          "as": "conversions",
                          "expr": "case when (`T`.`dateMatch`) then (`T`.`conversions`) else 0 end"
                        },
                        {
                          "as": "day",
                          "expr": "(`T`.`day`)"
                        },
                        {
                          "as": "prevRevenue",
                          "expr": "case when (((str_to_millis(\"2016-05-01\") - (`T`.`intervalMillis`)) <= str_to_millis((`T`.`day`))) and (str_to_millis((`T`.`day`)) <= (str_to_millis(\"2016-05-22\") - (`T`.`intervalMillis`)))) then (`T`.`revenue`) else 0 end"
                        }
                      ]
                    },
                    {
                      "#operator": "FinalProject"
                    }
                  ]
                }
              }
            ]
          },
          {
            "#operator": "Alias",
            "as": "T1"
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "InitialGroup",
                  "aggregates": [
                    "sum((`T1`.`clicks`))",
                    "sum((`T1`.`conversions`))",
                    "sum((`T1`.`prevRevenue`))",
                    "sum((`T1`.`revenue`))"
                  ],
                  "group_keys": [
                    "(`T1`.`campaignId`)"
                  ]
                }
              ]
            }
          },
          {
            "#operator": "IntermediateGroup",
            "aggregates": [
              "sum((`T1`.`clicks`))",
              "sum((`T1`.`conversions`))",
              "sum((`T1`.`prevRevenue`))",
              "sum((`T1`.`revenue`))"
            ],
            "group_keys": [
              "(`T1`.`campaignId`)"
            ]
          },
          {
            "#operator": "FinalGroup",
            "aggregates": [
              "sum((`T1`.`clicks`))",
              "sum((`T1`.`conversions`))",
              "sum((`T1`.`prevRevenue`))",
              "sum((`T1`.`revenue`))"
            ],
            "group_keys": [
              "(`T1`.`campaignId`)"
            ]
          },
          {
            "#operator": "Parallel",
            "~child": {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "InitialProject",
                  "result_terms": [
                    {
                      "as": "companyId",
                      "expr": "(`T1`.`companyId`)"
                    },
                    {
                      "as": "companyName",
                      "expr": "(`T1`.`companyName`)"
                    },
                    {
                      "expr": "(`T1`.`campaignId`)"
                    },
                    {
                      "as": "clicks",
                      "expr": "sum((`T1`.`clicks`))"
                    },
                    {
                      "as": "revenue",
                      "expr": "sum((`T1`.`revenue`))"
                    },
                    {
                      "as": "conversions",
                      "expr": "sum((`T1`.`conversions`))"
                    },
                    {
                      "as": "prevRevenue",
                      "expr": "sum((`T1`.`prevRevenue`))"
                    }
                  ]
                },
                {
                  "#operator": "FinalProject"
                }
              ]
            }
          }
        ]
      },
      {
        "#operator": "Alias",
        "as": "T2"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "avg(((`T2`.`conversions`) / (`T2`.`clicks`)))",
                "avg(((`T2`.`revenue`) / (`T2`.`conversions`)))",
                "count(*)",
                "sum((`T2`.`clicks`))",
                "sum((`T2`.`conversions`))",
                "sum((`T2`.`revenue`))"
              ],
              "group_keys": [
                "(`T2`.`companyId`)"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "avg(((`T2`.`conversions`) / (`T2`.`clicks`)))",
          "avg(((`T2`.`revenue`) / (`T2`.`conversions`)))",
          "count(*)",
          "sum((`T2`.`clicks`))",
          "sum((`T2`.`conversions`))",
          "sum((`T2`.`revenue`))"
        ],
        "group_keys": [
          "(`T2`.`companyId`)"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "avg(((`T2`.`conversions`) / (`T2`.`clicks`)))",
          "avg(((`T2`.`revenue`) / (`T2`.`conversions`)))",
          "count(*)",
          "sum((`T2`.`clicks`))",
          "sum((`T2`.`conversions`))",
          "sum((`T2`.`revenue`))"
        ],
        "group_keys": [
          "(`T2`.`companyId`)"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "prevRevenue",
                  "expr": "(`T2`.`prevRevenue`)"
                },
                {
                  "as": "companyId",
                  "expr": "to_string((`T2`.`companyId`))"
                },
                {
                  "as": "companyName",
                  "expr": "(`T2`.`companyName`)"
                },
                {
                  "as": "nOfCampaigns",
                  "expr": "count(*)"
                },
                {
                  "as": "clicks",
                  "expr": "sum((`T2`.`clicks`))"
                },
                {
                  "as": "revenue",
                  "expr": "sum((`T2`.`revenue`))"
                },
                {
                  "as": "conversions",
                  "expr": "sum((`T2`.`conversions`))"
                },
                {
                  "as": "conversionsRate",
                  "expr": "avg(((`T2`.`conversions`) / (`T2`.`clicks`)))"
                },
                {
                  "as": "conversionAvgCost",
                  "expr": "avg(((`T2`.`revenue`) / (`T2`.`conversions`)))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  }
]

I have below indexes
CREATE INDEXOttoman__typeONreachEffect(_type) USING GSI

CREATE INDEX `DailyCampaignUsage_Day` ON `Inheritx`(`day`) WHERE 
((`_type` = "DailyCampaignUsage") or (`_type` is missing)) USING GSI
 CREATE INDEX `join_User_company` ON `Inheritx`(("Company|" || `company`)) WHERE (`_type` = "User") USING GSI

CREATE INDEX `join_DailyCampaignUsage_campaign` ON 
`Inheritx`(("Campaign|" || `campaignId`)) WHERE (`_type` = 
"DailyCampaignUsage") USING GSI
CREATE INDEX `User_companyTypes` ON `Inheritx`(`companyTypes`) WHERE (`_type` = "User") USING GSI

CREATE INDEX `User_company` ON `Inheritx`(("Company|" || 
to_string(`company`))) WHERE ((`_type` = "User") or (`_type` is 
missing)) USING GSI

 CREATE INDEX `Ottoman__type` ON `reachEffect`(`_type`) USING GSI

how can I improve performance ??
how to use all index ??

2

How about add this index

CREATE INDEX `DailyCampaignUsage_campaignId_Day` ON `Inheritx`('Campaign|' ||  TOSTRING(campaignId),day) WHERE (_type = 'DailyCampaignUsage' OR _type IS missing) USING GSI;
3

it is taking more time now 36s

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “InheritxPI”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
“#operator”: “Join”,
“as”: “c”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_keys”: “[("Company|" || to_string((u.company)))]”
},
{
“#operator”: “IndexJoin”,
“as”: “ca”,
“for”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “("User|" || to_string((ca.user)))”,
“outer”: true,
“scan”: {
“index”: “campaign_user”,
“using”: “gsi”
}
},
{
“#operator”: “IndexJoin”,
“as”: “du”,
“for”: “ca”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “("Campaign|" || to_string((du.campaignId)))”,
“outer”: true,
“scan”: {
“index”: “dailyCampaignUsage_campaign”,
“using”: “gsi”
}
},
{
“#operator”: “Filter”,
“condition”: “(((((u._type) = "User") and (((c._type) = "Company") and (2 in (c.companyTypes)))) and (((ca._type) = "Campaign") or ((ca._type) is missing))) and (((du._type) = "DailyCampaignUsage") or ((du._type) is missing)))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “(("2016-05-01" <= (du.day)) and ((du.day) <= "2016-05-22"))”
},
{
“as”: “intervalMillis”,
“expr”: “(str_to_millis("2016-05-22") - str_to_millis("2016-05-01"))”
},
{
“as”: “companyId”,
“expr”: “(c._id)”
},
{
“as”: “companyName”,
“expr”: “(c.name)”
},
{
“as”: “campaignId”,
“expr”: “(ca._id)”
},
{
“as”: “clicks”,
“expr”: “array_sum((array_star((du.statistics)).clicks))”
},
{
“as”: “revenue”,
“expr”: “array_sum((array_star((du.statistics)).clicksCost))”
},
{
“as”: “conversions”,
“expr”: “array_sum((array_star((du.statistics)).conversions))”
},
{
“as”: “day”,
“expr”: “(du.day)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Alias”,
“as”: “T”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “(T.dateMatch)”
},
{
“as”: “intervalMillis”,
“expr”: “(T.intervalMillis)”
},
{
“as”: “companyId”,
“expr”: “(T.companyId)”
},
{
“as”: “companyName”,
“expr”: “(T.companyName)”
},
{
“as”: “campaignId”,
“expr”: “(T.campaignId)”
},
{
“as”: “clicks”,
“expr”: “case when (T.dateMatch) then (T.clicks) else 0 end”
},
{
“as”: “revenue”,
“expr”: “case when (T.dateMatch) then (T.revenue) else 0 end”
},
{
“as”: “conversions”,
“expr”: “case when (T.dateMatch) then (T.conversions) else 0 end”
},
{
“as”: “day”,
“expr”: “(T.day)”
},
{
“as”: “prevRevenue”,
“expr”: “case when (((str_to_millis("2016-05-01") - (T.intervalMillis)) <= str_to_millis((T.day))) and (str_to_millis((T.day)) <= (str_to_millis("2016-05-22") - (T.intervalMillis)))) then (T.revenue) else 0 end”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Alias”,
“as”: “T1”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
“(T1.campaignId)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
“(T1.campaignId)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
“(T1.campaignId)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “companyId”,
“expr”: “(T1.companyId)”
},
{
“as”: “companyName”,
“expr”: “(T1.companyName)”
},
{
“expr”: “(T1.campaignId)”
},
{
“as”: “clicks”,
“expr”: “sum((T1.clicks))”
},
{
“as”: “revenue”,
“expr”: “sum((T1.revenue))”
},
{
“as”: “conversions”,
“expr”: “sum((T1.conversions))”
},
{
“as”: “prevRevenue”,
“expr”: “sum((T1.prevRevenue))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Alias”,
“as”: “T2”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
“count()",
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
“(T2.companyId)”
]
}
]
}
},
{
“#operator”: “IntermediateGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
"count()”,
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
“(T2.companyId)”
]
},
{
“#operator”: “FinalGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
“count()",
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
“(T2.companyId)”
]
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “prevRevenue”,
“expr”: “(T2.prevRevenue)”
},
{
“as”: “companyId”,
“expr”: “to_string((T2.companyId))”
},
{
“as”: “companyName”,
“expr”: “(T2.companyName)”
},
{
“as”: “nOfCampaigns”,
“expr”: "count()”
},
{
“as”: “clicks”,
“expr”: “sum((T2.clicks))”
},
{
“as”: “revenue”,
“expr”: “sum((T2.revenue))”
},
{
“as”: “conversions”,
“expr”: “sum((T2.conversions))”
},
{
“as”: “conversionsRate”,
“expr”: “avg(((T2.conversions) / (T2.clicks)))”
},
{
“as”: “conversionAvgCost”,
“expr”: “avg(((T2.revenue) / (T2.conversions)))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

4

How about the following query:

		              SELECT (du.day >= '2016-05-01' AND du.day <= '2016-05-22') AS dateMatch, MILLIS('2016-05-22') - MILLIS('2016-05-01') as intervalMillis, 
		                      ARRAY_SUM(du.`statistics`[*].clicks) as clicks,
		                      ARRAY_SUM(du.`statistics`[*].clicksCost) as revenue,     ARRAY_SUM(du.`statistics`[*].conversions) as conversions, du.day As day 
		                      c._id AS companyId, c.name AS companyName, 
		                      ca._id AS campaignId, 
		               FROM `Inheritx` u  use INDEX(DailyCampaignUsage_campaignId_Day) JOIN `Inheritx` c ON KEYS ['Company|' || TOSTRING(u.company) ] 
		               LEFT OUTER JOIN `Inheritx` ca ON KEY 'User|' || TOSTRING(ca.`user`)  FOR u 
		               LEFT OUTER JOIN `Inheritx` du ON KEY 'Campaign|' ||  TOSTRING(du.campaignId) FOR ca 
		               WHERE u._type = 'User' AND (c._type = 'Company' AND 2 in c.companyTypes)
		               AND (ca._type = 'Campaign' OR ca._type IS missing) AND (du._type = 'DailyCampaignUsage' OR du._type IS missing)

and the output of explain.

5

explain plan

{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “PrimaryScan”,
“index”: “InheritxPI”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“as”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
“#operator”: “Join”,
“as”: “c”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_keys”: “[("Company|" || to_string((u.company)))]”
},
{
“#operator”: “IndexJoin”,
“as”: “ca”,
“for”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “("User|" || to_string((ca.user)))”,
“outer”: true,
“scan”: {
“index”: “campaign_user”,
“using”: “gsi”
}
},
{
“#operator”: “IndexJoin”,
“as”: “du”,
“for”: “ca”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “("Campaign|" || to_string((du.campaignId)))”,
“outer”: true,
“scan”: {
“index”: “dailyCampaignUsage_campaign”,
“using”: “gsi”
}
},
{
“#operator”: “Filter”,
“condition”: “(((((u._type) = "User") and (((c._type) = "Company") and (2 in (c.companyTypes)))) and (((ca._type) = "Campaign") or ((ca._type) is missing))) and (((du._type) = "DailyCampaignUsage") or ((du._type) is missing)))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “(("2016-05-01" <= (du.day)) and ((du.day) <= "2016-05-22"))”
},
{
“as”: “intervalMillis”,
“expr”: “(str_to_millis("2016-05-22") - str_to_millis("2016-05-01"))”
},
{
“as”: “clicks”,
“expr”: “array_sum((array_star((du.statistics)).clicks))”
},
{
“as”: “revenue”,
“expr”: “array_sum((array_star((du.statistics)).clicksCost))”
},
{
“as”: “conversions”,
“expr”: “array_sum((array_star((du.statistics)).conversions))”
},
{
“as”: “day”,
“expr”: “(du.day)”
},
{
“as”: “companyId”,
“expr”: “(c._id)”
},
{
“as”: “companyName”,
“expr”: “(c.name)”
},
{
“as”: “campaignId”,
“expr”: “(ca._id)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}

6

Following redefine index

CREATE INDEX `join_DailyCampaignUsage_campaign` ON `Inheritx`("Campaign|" || `campaignId`) WHERE (`_type` = 'DailyCampaignUsage' OR `_type` IS missing) USING GSI;
CREATE INDEX `join_campaign_User` ON `Inheritx`('User|' || TOSTRING(`user`) ) WHERE (`_type` = 'Campaign' OR `_type` IS missing) USING GSI;
CREATE INDEX `join_User_company` ON `Inheritx`("Company|" || `company`) WHERE (`_type` = 'User') USING GSI;
CREATE INDEX `DailyCampaignUsage_day_statistics` ON `Inheritx`(day,statistics) WHERE (`_type` = 'DailyCampaignUsage' OR `_type` IS missing) USING GSI;

works?

7

it is not working

it takes 39s

main query explain plan

{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “InheritxPI”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “c”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_keys”: “[(“Company|” || to_string((u.company)))]”
},
{
"#operator": “IndexJoin”,
“as”: “ca”,
“for”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “(“User|” || to_string((ca.user)))”,
“outer”: true,
“scan”: {
“index”: “campaign_user”,
“using”: “gsi”
}
},
{
"#operator": “IndexJoin”,
“as”: “du”,
“for”: “ca”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “(“Campaign|” || to_string((du.campaignId)))”,
“outer”: true,
“scan”: {
“index”: “dailyCampaignUsage_campaign”,
“using”: “gsi”
}
},
{
"#operator": “Filter”,
“condition”: “(((((u._type) = “User”) and (((c._type) = “Company”) and (2 in (c.companyTypes)))) and (((ca._type) = “Campaign”) or ((ca._type) is missing))) and (((du._type) = “DailyCampaignUsage”) or ((du._type) is missing)))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “((“2016-05-01” <= (du.day)) and ((du.day) <= “2016-05-22”))”
},
{
“as”: “intervalMillis”,
“expr”: “(str_to_millis(“2016-05-22”) - str_to_millis(“2016-05-01”))”
},
{
“as”: “companyId”,
“expr”: “(c._id)”
},
{
“as”: “companyName”,
“expr”: “(c.name)”
},
{
“as”: “campaignId”,
“expr”: “(ca._id)”
},
{
“as”: “clicks”,
“expr”: “array_sum((array_star((du.statistics)).clicks))”
},
{
“as”: “revenue”,
“expr”: “array_sum((array_star((du.statistics)).clicksCost))”
},
{
“as”: “conversions”,
“expr”: “array_sum((array_star((du.statistics)).conversions))”
},
{
“as”: “day”,
“expr”: “(du.day)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Alias”,
“as”: “T”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “(T.dateMatch)”
},
{
“as”: “intervalMillis”,
“expr”: “(T.intervalMillis)”
},
{
“as”: “companyId”,
“expr”: “(T.companyId)”
},
{
“as”: “companyName”,
“expr”: “(T.companyName)”
},
{
“as”: “campaignId”,
“expr”: “(T.campaignId)”
},
{
“as”: “clicks”,
“expr”: “case when (T.dateMatch) then (T.clicks) else 0 end”
},
{
“as”: “revenue”,
“expr”: “case when (T.dateMatch) then (T.revenue) else 0 end”
},
{
“as”: “conversions”,
“expr”: “case when (T.dateMatch) then (T.conversions) else 0 end”
},
{
“as”: “day”,
“expr”: “(T.day)”
},
{
“as”: “prevRevenue”,
“expr”: “case when (((str_to_millis(“2016-05-01”) - (T.intervalMillis)) <= str_to_millis((T.day))) and (str_to_millis((T.day)) <= (str_to_millis(“2016-05-22”) - (T.intervalMillis)))) then (T.revenue) else 0 end”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Alias”,
“as”: “T1”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
"(T1.campaignId)"
]
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
"(T1.campaignId)"
]
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“sum((T1.clicks))”,
“sum((T1.conversions))”,
“sum((T1.prevRevenue))”,
“sum((T1.revenue))”
],
“group_keys”: [
"(T1.campaignId)"
]
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “companyId”,
“expr”: “(T1.companyId)”
},
{
“as”: “companyName”,
“expr”: “(T1.companyName)”
},
{
“expr”: “(T1.campaignId)”
},
{
“as”: “clicks”,
“expr”: “sum((T1.clicks))”
},
{
“as”: “revenue”,
“expr”: “sum((T1.revenue))”
},
{
“as”: “conversions”,
“expr”: “sum((T1.conversions))”
},
{
“as”: “prevRevenue”,
“expr”: “sum((T1.prevRevenue))”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Alias”,
“as”: “T2”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
“count()",
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
"(T2.companyId)"
]
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
"count()”,
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
"(T2.companyId)"
]
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“avg(((T2.conversions) / (T2.clicks)))”,
“avg(((T2.revenue) / (T2.conversions)))”,
“count()",
“sum((T2.clicks))”,
“sum((T2.conversions))”,
“sum((T2.revenue))”
],
“group_keys”: [
"(T2.companyId)"
]
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “prevRevenue”,
“expr”: “(T2.prevRevenue)”
},
{
“as”: “companyId”,
“expr”: “to_string((T2.companyId))”
},
{
“as”: “companyName”,
“expr”: “(T2.companyName)”
},
{
“as”: “nOfCampaigns”,
“expr”: "count()”
},
{
“as”: “clicks”,
“expr”: “sum((T2.clicks))”
},
{
“as”: “revenue”,
“expr”: “sum((T2.revenue))”
},
{
“as”: “conversions”,
“expr”: “sum((T2.conversions))”
},
{
“as”: “conversionsRate”,
“expr”: “avg(((T2.conversions) / (T2.clicks)))”
},
{
“as”: “conversionAvgCost”,
“expr”: “avg(((T2.revenue) / (T2.conversions)))”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}

8

you given inner query it is takes 37s

it is explain plan

{
"#operator": “Sequence”,
"~children": [
{
"#operator": “PrimaryScan”,
“index”: “InheritxPI”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“as”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “c”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_keys”: “[(“Company|” || to_string((u.company)))]”
},
{
"#operator": “IndexJoin”,
“as”: “ca”,
“for”: “u”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “(“User|” || to_string((ca.user)))”,
“outer”: true,
“scan”: {
“index”: “campaign_user”,
“using”: “gsi”
}
},
{
"#operator": “IndexJoin”,
“as”: “du”,
“for”: “ca”,
“keyspace”: “Inheritx”,
“namespace”: “default”,
“on_key”: “(“Campaign|” || to_string((du.campaignId)))”,
“outer”: true,
“scan”: {
“index”: “dailyCampaignUsage_campaign”,
“using”: “gsi”
}
},
{
"#operator": “Filter”,
“condition”: “(((((u._type) = “User”) and (((c._type) = “Company”) and (2 in (c.companyTypes)))) and (((ca._type) = “Campaign”) or ((ca._type) is missing))) and (((du._type) = “DailyCampaignUsage”) or ((du._type) is missing)))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “dateMatch”,
“expr”: “((“2016-05-01” <= (du.day)) and ((du.day) <= “2016-05-22”))”
},
{
“as”: “intervalMillis”,
“expr”: “(str_to_millis(“2016-05-22”) - str_to_millis(“2016-05-01”))”
},
{
“as”: “clicks”,
“expr”: “array_sum((array_star((du.statistics)).clicks))”
},
{
“as”: “revenue”,
“expr”: “array_sum((array_star((du.statistics)).clicksCost))”
},
{
“as”: “conversions”,
“expr”: “array_sum((array_star((du.statistics)).conversions))”
},
{
“as”: “day”,
“expr”: “(du.day)”
},
{
“as”: “companyId”,
“expr”: “(c._id)”
},
{
“as”: “companyName”,
“expr”: “(c.name)”
},
{
“as”: “campaignId”,
“expr”: “(ca._id)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}

9

can you give your index define about

campaign_user

and

dailyCampaignUsage_campaign
10

CREATE INDEX campaign_user ON Inheritx((“User|” ||
to_string(user))) WHERE ((_type = “Campaign”) or (_type is
missing)) USING GSI

CREATE INDEX dailyCampaignUsage_campaign ON
Inheritx((“Campaign|” || to_string(campaignId))) WHERE ((_type =
“DailyCampaignUsage”) or (_type is missing)) USING GSI

11

This index should be

CREATE INDEX `join_User_company` ON `Inheritx`("Company|" || TOSTRING(`company`)) WHERE (`_type` = 'User') USING GSI;

and maybe you can open debug log to locate which part took long time.

12

it is not working.
I have inserted data in a same bucket. should I created different bucket ??

13

How many documents do you have in your bucket?
and how many node(DS/IS/QS) of CB do you have?

14

around 89K and counting
1 node * 500 mb
DS/IS/QS ??

15

you can add more Service by analyzing your query log to improve your query.

16

but I don’t know how to do it. Can you provide some video link ??

17

it is easy to add node into CB cluster. FYI