Array index with multiple ALL expressions?

SQL++
1

CB 5.0: I am trying to create an array index with elements from 2 different nested arrays (tags & journalist) but I get this error:

GSI CreateIndex() - cause: Fails to create index. Multiple expressions with ALL are found. Only one array expression is supported per index."

The index I intend to create:

CREATE INDEX `eccache-index-editorial-tags-jorunalist` ON `ECCache`
(`edi_typ_id`,`edi_visible`,`edi_sec_id`,`edi_publication_date`,`edi_id`,`edi_weight`,`entitytype`,`edi_published`,`edi_sit_id`,`edi_short_publication_date`,
`tags`,
(DISTINCT (array [`t`.`tag_id`, `t`.`editag_weight` ] for `t` in `tags` end)),
`journalist`,
(DISTINCT ARRAY j.ali_jou_id FOR j IN journalist END) )
WHERE (`entitytype` = "editorial")
WITH { "defer_build":true, "num_replica":1 }

My Query

select editorial.edi_publication_date,editorial.edi_id, editorial.edi_weight, jou.ali_jou_id, jou.ali_name
from ECCache as editorial
UNNEST editorial.journalist AS jou
UNNEST editorial.tag AS tag
where editorial.entitytype="editorial" 
and editorial.edi_typ_id=1 
and editorial.edi_published=1
and editorial.edi_visible=1 
and editorial.edi_sit_id =1
and jou.ali_jou_id in [90]
and tag.ditag_tag_id = 3253
order by DATE_FORMAT_STR(editorial.edi_publication_date,"1111-11-11") DESC, editorial.edi_weight ASC, editorial.edi_id DESC

I have no problem if I create the index only with elements of an array (tag):

CREATE INDEX `eccache-index-editorial-tags` ON `ECCache`
(`edi_typ_id`,`edi_visible`,`edi_sec_id`,`edi_publication_date`,`edi_id`,`edi_weight`,`entitytype`,`edi_published`,`edi_sit_id`,`edi_short_publication_date`,
`tags`,
(DISTINCT (array [`t`.`tag_id`, `t`.`editag_weight` ] for `t` in `tags` end))
)
WHERE (`entitytype` = "editorial")

I have tried to create 3 independent indexes (like in this post) but the queries are slow :frowning:

  • Index1
    CREATE INDEX eccache-index-editorial ON ECCache(edi_typ_id,edi_visible,edi_sec_id,edi_publication_date,
    edi_id,edi_weight,entitytype,edi_published,edi_sit_id,edi_short_publication_date)
    WHERE (entitytype = “editorial”)

  • Index2
    CREATE INDEX eccache-index-editorial-journalist ON ECCache
    (edi_id,
    journalist,
    (distinct (array (j.ali_jou_id) for j in journalist end)))
    WHERE (entitytype = “editorial”)

  • Index3
    CREATE INDEX eccache-index-editorial-tags-tag_id ON ECCache
    (
    edi_id,
    tags,
    (distinct (array (t1.tag_id) for t1 in tags end))
    )
    WHERE (entitytype = “editorial”)

In the Explain i see a “IndexScan2” “eccache-index-editorial” but not a “IntersectScan” with eccache-index-editorial-journalist & eccache-index-editorial-tags-tag_id

"#operator": "IndexScan2",
"index": "eccache-index-editorial"

Example of muy data:

{
  "edi_id": 13841,
  "edi_title": "Lorem ipsum",
  "edi_leadin": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt",
  "edi_subtitle": "",
  "edi_caption": "",
  "edi_body": "<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.</p>",
  "edi_publication_date": "2013-07-29 06:00:00",
  "edi_last_update": "2013-07-29 06:00:00",
  "edi_end_date": "2013-07-29 06:00:00",
  "edi_status": 0,
  "edi_visible": 1,
  "edi_published": 0,
  "edi_commentable": 1,
  "edi_advertising": 0,
  "edi_latest_content": 0,
  "edi_short_url": "",
  "edi_front_suggests": 0,
  "edi_lan_id": "es",
  "edi_sec_id": 1,
  "edi_update_jou_id": 189,
  "edi_video_id": "",
  "edi_video_type": 0,
  "edi_weight": 1000,
  "edi_revised": 1,
  "edi_presentation": 0,
  "entitytype": "editorial",
  "edi_sit_id": 1,
  "journalist": [
    {
      "aliedi_ali_id": 178,
      "aliedi_edi_id": 13841,
      "aliedi_order": 0,
      "ali_id": 178,
      "ali_name": "AAAAAAAAAAA",
      "ali_jou_id": 90
    },
    {
      "aliedi_ali_id": 371,
      "aliedi_edi_id": 13841,
      "aliedi_order": 1,
      "ali_id": 371,
      "ali_name": "BBBBBBBB",
      "ali_jou_id": 189
    },
  ].
  "tags": [
    {
      "edi_id": 13841,
      "editag_tag_id": 3253,
      "edi_publication_date": "2013-07-29 06:00:00",
      "edi_sec_id": 1,
      "tag_id": 3253,
      "editag_weight": 1,
      "editag_order": 1
    },
    {
      "edi_id": 13841,
      "editag_tag_id": 6165,
      "edi_publication_date": "2013-07-29 06:00:00",
      "edi_sec_id": 1,
      "tag_id": 6165,
      "editag_weight": 1,
      "editag_order": 2
    },
  ],
  "relations": [],
  "editorial_data": {
    "2": {
      "edidat_edi_id": 13841,
      "edidat_dat_id": 2,
      "edidat_data": 1
    }
  }
  ]
}
2

Currently, array indexing is limited to using only one index key with array_expr. Furthermore, the array_expr must be the first index-key for the index to be leverages for a SELECT statement with an UNNEST clause on the array, it needs to be ALL keyword (no DISTINCT modifier) .

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

CREATE INDEX `ix1` ON `ECCache` (`edi_typ_id`,`edi_published`, `edi_visible`,`edi_sit_id`,
   `edi_publication_date` DESC, `edi_weight`, `edi_id` DESC,
    DISTINCT ARRAY j.ali_jou_id FOR j IN journalist END,
    journalist, tags) WHERE (`entitytype` = "editorial") WITH { "defer_build":true, "num_replica":1 };


SELECT e.edi_publication_date,e.edi_id, e.edi_weight,
ARRAY {jou.ali_jou_id, jou.ali_name} FOR jou IN e.journalist WHEN jou.ali_jou_id IN [90] END
FROM ECCache AS e
WHERE e.entitytype = "editorial"
      AND e.edi_typ_id = 1 AND e.edi_published = 1 AND e.edi_visible = 1 AND e.edi_sit_id = 1
      AND e.edi_publication_date IS NOT MISSING AND e.edi_weight IS NOT MISSING AND e.edi_weight IS NOT MISSING
      AND ANY j IN e.journalist SATISFIES j.ali_jou_id IN [90] END
      AND ANY t IN e.tags SATISFIES t.tag_id = 3253 END
ORDER BY e.edi_publication_date DESC, e.edi_weight, e.edi_id DESC;

Above query uses index order (added more predicates)

OR

CREATE INDEX `ix1` ON `ECCache` (`edi_typ_id`,`edi_published`, `edi_visible`,`edi_sit_id`,
    DISTINCT ARRAY j.ali_jou_id FOR j IN journalist END,
   `edi_publication_date` DESC, `edi_weight`, `edi_id` DESC,
    journalist, tags) WHERE (`entitytype` = "editorial") WITH { "defer_build":true, "num_replica":1 };

SELECT e.edi_publication_date,e.edi_id, e.edi_weight,
ARRAY {jou.ali_jou_id, jou.ali_name} FOR jou IN e.journalist WHEN jou.ali_jou_id IN [90] END
FROM ECCache AS e
WHERE e.entitytype = "editorial"
      AND e.edi_typ_id = 1 AND e.edi_published = 1 AND e.edi_visible = 1 AND e.edi_sit_id = 1
      AND ANY j IN e.journalist SATISFIES j.ali_jou_id IN [90] END
      AND ANY t IN e.tags SATISFIES t.tag_id = 3253 END
ORDER BY e.edi_publication_date DESC, e.edi_weight, e.edi_id DESC;

Original query. Query requires sort

ARRAY {jou.ali_jou_id, jou.ali_name} FOR jou IN e.journalist WHEN jou.ali_jou_id IN [90] END
result in single element you can do (FIRST {jou.ali_jou_id, jou.ali_name} FOR jou IN e.journalist WHEN jou.ali_jou_id IN [90] END).*

1 Like