Help with index creation

SQL++
,
1

Hello,

I am trying to create an index that I can use for a count and for a list of meta().id. Here is an example document that we have in our bucket:

{
  "id": "/some/path",
  "subscribers": ["f1::abc", "abc::123"  ],
  "type": "foo"
}

Here is my query:

select count(m.id) from mybucket m
where m.type = "foo"
AND ARRAY_COUNT(m.subscribers) > 200

Here is the index:

create index `subscribers`
on mobile(`id`, (meta().`id`))
where type = "foo"
AND ARRAY_COUNT(subscribers) > 200

The index is covered for the query above but when I try to get a list of the id or even meta().id it not covered. Here are the explain plans:

explain select count(m.id) from mybucket m
where m.type = "foo"
AND ARRAY_COUNT(m.subscribers) > 200

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexCountScan",
          "covers": [
            "cover ((`m`.`id`))",
            "cover ((meta(`m`).`id`))",
            "cover ((meta(`m`).`id`))"
          ],
          "index": "subscribers",
          "index_id": "76204e26d41740a7",
          "keyspace": "mybucket",
          "namespace": "default",
          "spans": [
            {
              "Range": {
                "Inclusion": 0,
                "Low": [
                  "null"
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "IndexCountProject",
          "result_terms": [
            {
              "expr": "count((`m`.`id`))"
            }
          ]
        }
      ]
    },
    "text": "select count(m.id) from mybucket m\nwhere m.type = \"foo\"\nAND ARRAY_COUNT(m.subscribers) > 200"
  }
]

And here is the one to get the list of ids:

explain select m.id from mybucket m
where m.type = "foo"
AND ARRAY_COUNT(m.subscribers) > 200

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "mobile",
          "namespace": "default",
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "as": "m",
          "keyspace": "mybucket",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "(((`m`.`type`) = \"foo\") and (200 < array_count((`m`.`subscribers`))))"
              },
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "(`m`.`id`)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select m.id from mybucket m\nwhere m.type = \"foo\"\nAND ARRAY_COUNT(m.subscribers) > 200"
  }
]

Any idea how to get the index to be covered in both queries?

Thanks,

K

2

LEADING Index key must be present in as part of query predicate. In case of count query count(id) means id IS NOT NULL. So it is implicit.

You can add the id IS NOT NULL in your second query. Also remove index META().id in the index because it is available.

create index `subscribers` on mobile(`id`) where type = "foo" AND ARRAY_COUNT(subscribers) > 200;
select count(m.id) from mybucket m where m.type = "foo" AND ARRAY_COUNT(m.subscribers) > 200
select RAW m.id from mybucket m where m.type = "foo" AND ARRAY_COUNT(m.subscribers) > 200 AND m.id IS NOT NULL;

If you want query on varying ARRAY_COUNT use the following index with your original queries.

create index subscribers on mobile(ARRAY_COUNT(subscribers),id) where type = "foo";

Querying varying ARRAY_COUNT always > 2oo

create index subscribers on mobile(ARRAY_COUNT(subscribers),id) where type = "foo" AND ARRAY_COUNT(subscribers) > 200;

3

Thanks @vsr1. That worked.