CODEWITHSUNDEEP
couchbasesql++
Sreerag
Sreerag

Reputation: 1401

Couchbase array index not getting used in the query

I have the following document structure: 


{    
  "customerId": "",
  "schemeId": "scheme-a",  
  "type": "account",
  "events": [
    {
      "dateTime": "2019-03-14T02:23:58.573Z",
      "id": "72998bbf-94a6-4031-823b-6c304707ad49",
      "type": "DebitDisabled",
      "authorisedId": ""
    },
    {
      "dateTime": "2018-05-04T12:40:15.439Z",
      "transactionReference": "005171-15-1054-7571-60990-20180503165536",      
      "id": "005171-15-1054-7571-60990-20180503165536-1",
      "type": "Credit",
      "authorisedId": ",
      "value": 34,
      "funder": "funder-a"
    },
    {
      "dateTime": "2019-03-06T04:14:54.564Z",
      "transactionReference": "000000922331",
      "eventDescription": {
        "language": "en-gb",
        "text": "
      },
      "id": "000000922331",
      "type": "Credit",
      "authorisedId": "",
      "value": 16,
      "funder": "funder-b"
    },
    {
      "dateTime": "2019-03-10T04:24:17.903Z",
      "transactionReference": "000001510154",
      "eventDescription": {
        "language": "en-gb",
        "text": ""
      },
      "id": "000001510154",
      "type": "Credit",
      "authorisedId": "",
      "value": 10,
      "funder": "funder-c"
    }
  ]
}

And the following indexes : 

CREATE INDEX `scheme-a_customers_index` 
ON `default`(`type`,`schemeId`,`customerId`) 
WHERE ((`schemeId` = "scheme-a") and (`type` = "account")) 
WITH { "num_replica":1 }

CREATE INDEX `scheme-a_credits_index` 
ON `default`(
`type`,
`schemeId`,
`customerId`,
(distinct (array (`e`.`funder`) for `e` in `events` when ((`e`.`type`) = "Credit") end))
) 
WHERE ((`type` = "scheme") and (`schemeId` = "scheme-a")) 
WITH { "num_replica":1 }

I am trying to query all the customerIds and events for each where type="credit" and funder like "funder%"

below is my query : 

SELECT 
    customerId, 
    (ARRAY v.`value` FOR v IN p.events WHEN v.type = "Credit" AND v.funder like "funder%" END) AS credits 
FROM default AS p 
WHERE p.type = "account" AND p.schemeId = "scheme-a" 
AND (ANY e IN p.events SATISFIES e.funder = "funder-a" END)

I am expecting the query to use the index scheme-a_credits_index, instead it is using scheme-a_customers_index. Can't understand why ! isn't the query supposed to use scheme-a_credits_index ?

Upvotes: 0

Views: 53

Answers (1)

vsr
vsr

Reputation: 7414

Your query doesn't have predicate on customerId. So query can only push two predicates to indexers and both indexes are qualify. scheme-a_customers_index is more efficient because of number of entries in the index due to non array index.

You should try the following.

CREATE INDEX `ix1` ON `default`
(DISTINCT ARRAY e.funder FOR e IN events WHEN e.type = "Credit" END, `customerId`)
WHERE ((`schemeId` = "scheme-a") and (`type` = "account")) ;

SELECT
    customerId,
    (ARRAY v.`value` FOR v IN p.events WHEN v.type = "Credit" AND v.funder like "funder%" END) AS credits
FROM default AS p
WHERE p.type = "account" AND p.schemeId = "scheme-a"
AND (ANY e IN p.events SATISFIES e.funder LIKE "funder%" AND e.type = "Credit" END);

Upvotes: 1

Related Questions