couchdb - performance improve in n1ql,couchbase using index

Question

I have below query

explain SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,
ROUND(sum(CASE WHEN  DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,
ROUND(sum(CASe WHEN  DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue 
from Inheritx DailyCampaignUsage  use index(dailyCampaignUsage_type_day_clicksCost)
JOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId)) 
JOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`)) 
WHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__    ORDER BY `created` DESC

I have below index

CREATE INDEX dailyCampaignUsage_type_day_clicksCost  ON Inheritx 
(_type,day,`statistics`[*].clicksCost) WHERE _type='DailyCampaignUsage'

which I have use in query.

my explain plan is blow.

{
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Sequence",
              "~children": [
                {
                  "#operator": "IndexScan",
                  "index": "dailyCampaignUsage_type_day_clicksCost",
                  "index_id": "37387d27d560354b",
                  "keyspace": "Inheritx",
                  "namespace": "default",
                  "spans": [
                    {
                      "Range": {
                        "High": [
                          "successor("DailyCampaignUsage")"
                        ],
                        "Inclusion": 1,
                        "Low": [
                          ""DailyCampaignUsage""
                        ]
                      }
                    }
                  ],
                  "using": "gsi"
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "Fetch",
                        "as": "DailyCampaignUsage",
                        "keyspace": "Inheritx",
                        "namespace": "default"
                      },
                      {
                        "#operator": "Join",
                        "as": "Campaign",
                        "keyspace": "Inheritx",
                        "namespace": "default",
                        "on_keys": "("Campaign|" || to_string((`DailyCampaignUsage`.`campaignId`)))"
                      },
                      {
                        "#operator": "Join",
                        "as": "Users",
                        "keyspace": "Inheritx",
                        "namespace": "default",
                        "on_keys": "("User|" || to_string((`Campaign`.`user`)))"
                      },
                      {
                        "#operator": "Filter",
                        "condition": "(((`DailyCampaignUsage`.`_type`) = "DailyCampaignUsage") and case when false then ((`Users`.`user`) in false) else true end)"
                      },
                      {
                        "#operator": "InitialGroup",
                        "aggregates": [
                          "sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
                          "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-04-01" and "2016-04-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
                          "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-05-01" and "2016-05-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
                        ],
                        "group_keys": []
                      }
                    ]
                  }
                },
                {
                  "#operator": "IntermediateGroup",
                  "aggregates": [
                    "sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
                    "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-04-01" and "2016-04-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
                    "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-05-01" and "2016-05-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
                  ],
                  "group_keys": []
                },
                {
                  "#operator": "FinalGroup",
                  "aggregates": [
                    "sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)))",
                    "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-04-01" and "2016-04-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)",
                    "sum(case when ((`DailyCampaignUsage`.`day`) between "2016-05-01" and "2016-05-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end)"
                  ],
                  "group_keys": []
                },
                {
                  "#operator": "Parallel",
                  "~child": {
                    "#operator": "Sequence",
                    "~children": [
                      {
                        "#operator": "InitialProject",
                        "result_terms": [
                          {
                            "as": "total_revenue",
                            "expr": "round(sum(array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`))), 2)"
                          },
                          {
                            "as": "period_revenue",
                            "expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between "2016-05-01" and "2016-05-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
                          },
                          {
                            "as": "period_prev_revenue",
                            "expr": "round(sum(case when ((`DailyCampaignUsage`.`day`) between "2016-04-01" and "2016-04-23") then array_sum((array_star((`DailyCampaignUsage`.`statistics`)).`clicksCost`)) else 0 end), 2)"
                          }
                        ]
                      },
                      {
                        "#operator": "FinalProject"
                      }
                    ]
                  }
                }
              ]
            },
            {
              "#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"
        }
      ]
    },
    "text": "SELECT * FROM (select ROUND(sum(ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost)),2) total_revenue,
ROUND(sum(CASE WHEN  DailyCampaignUsage.day between '2016-05-01' and '2016-05-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_revenue,
ROUND(sum(CASe WHEN  DailyCampaignUsage.day between '2016-04-01' and '2016-04-23' THEN ARRAY_SUM(DailyCampaignUsage.`statistics`[*].clicksCost) ELSE 0 END),2) period_prev_revenue 
from Inheritx DailyCampaignUsage  use index(dailyCampaignUsage_type_day_clicksCost)
JOIN Inheritx Campaign ON KEYS ('Campaign|'||TOSTRING(DailyCampaignUsage.campaignId)) 
JOIN Inheritx Users on keys('User|'|| TOSTRING(Campaign.`user`)) 
WHERE DailyCampaignUsage._type='DailyCampaignUsage' and CASE WHEN FALSE THEN Users.`user` in FALSE ELSE TRUE END ) AS __viewdef__    ORDER BY `created` DESC"
  }

even index using I can not reduce it execution. it is 13s how I can make it around 300 to 500ms ?? my json like below I have 50k+ json

DailyCampaignUsage|006657c0-c696-11e6-b6f2-7f0166ec7527{
      "_id": "006657c0-c696-11e6-b6f2-7f0166ec7527",
      "_type": "DailyCampaignUsage",
      "campaignId": 249,
      "day": "2015-11-19T00:00:00Z",
      "statistics": [
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {},
        {
          "clicks": 1741,
          "clicksCost": 48.748
        }
      ]
    }

Answer 1

1) can you first try to optimize the inner query. Make it use covering indexes by including all fields of the first keyspace used in where/projections/join-on-keys in the index definition. See https://developer.couchbase.com/documentation/server/4.5/indexes/covering-indexes.html. Last example in the documentation applies to you. Something like:

CREATE INDEX dailyCampaignUsage_type_day_clicksCost  ON Inheritx 
(_type,day, campaignId, `statistics`[*].clicksCost) WHERE _type='DailyCampaignUsage'

2) You can try Memory Optimized Indexes (MOI) to tremendously increase the perf. This needs Enterprise edition. See https://developer.couchbase.com/documentation/server/4.5/architecture/global-secondary-indexes.html#story-h2-2

3) The outer query is only doing order by on 'created' which is not projected by the inner query. If it is in the first keyspace, include that in index.

4) Also check the WHERE condition with CASE on Users.user. It always evaluates to true. Not sure if you need the 3rd Join.

hth, -Prasad