USE INDEX does not find the index

SQL++
1

I have a bucket called ‘stats’ with about 75,000 documents that contain the field ‘day_utc’ which is the number of days since the epoch.

I have created indexes on the bucket using:

   create primary index on stats;
   create index day_utc_index on stats(day_utc);

When I do a group by query it takes about 8 seconds to complete:

   select day_utc, count(*) as count from stats  group by day_utc;

When I did an explain on the query it was using the primary index instead of day_utc_index. So I tried to hint the index to use with a query like:

  select day_utc, count(*) as count from stats use index(day_utc_index)  group by day_utc;

and

  select day_utc, count(*) as count from stats use index(day_utc_index using view)  group by day_utc;

but it gives the error: “View Index not found day_utc_index”.

I know the index is there because if I try this:

  create index day_utc_index on stats(day_utc);

I get the error: “View index exists Non usuable index day_utc_index”

I know the index has been created and is usable because when I use the Couchbase web GUI I can filter and show results from it and it runs very fast.

Any idea what I’m missing.

1 Like
2

Hi @smarq,

For now, please add the following to your query:

WHERE day_utc IS NOT NULL

3

Thanks @geraldss, but it did not seem to make any difference.

This:

select day_utc, count(*) as count from stats use index(day_utc_index) where day_utc is not null  group by day_utc;

still gives the error: “View Index not found day_utc_index”

and this:

select day_utc, count(*) as count from stats where day_utc is not null  group by day_utc;

now takes about 9 seconds to execute instead of 8 on the same 75,000 documents.

Here is the output of explain:

    n1ql> explain select day_utc, count(*) as count from stats where day_utc is not null  group by day_utc;
{
    "requestID": "7dc52a76-b87f-4848-a5a6-713b33a4b27a",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "PrimaryScan",
                    "index": "#primary",
                    "keyspace": "stats",
                    "namespace": "default",
                    "using": "view"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "keyspace": "stats",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((`stats`.`day_utc`) is not null)"
                            },
                            {
                                "#operator": "InitialGroup",
                                "aggregates": [
                                    "count(*)"
                                ],
                                "group_keys": [
                                    "(`stats`.`day_utc`)"
                                ]
                            }
                        ]
                    }
                },
                {
                    "#operator": "IntermediateGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": [
                        "(`stats`.`day_utc`)"
                    ]
                },
                {
                    "#operator": "FinalGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": [
                        "(`stats`.`day_utc`)"
                    ]
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "(`stats`.`day_utc`)"
                                    },
                                    {
                                        "as": "count",
                                        "expr": "count(*)"
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "20.874533ms",
        "executionTime": "20.694588ms",
        "resultCount": 1,
        "resultSize": 2882
    }
}

Another data point:

select * from system:indexes;

does not show day_utc_index as one of the indexes.

4

Ok, you need to re-create day_utc_index until it shows up in system:indexes. If necessary, use a different name for the index.

5

I don’t think changing the name should make any difference.

I deleted the primary index on ‘stats’ and also the ‘day_utc_index’ on ‘stats’ using the Web UI. When I dropped the primary index on ‘stats’ from N1QL it seemed to succeed, but the Web UI still showed it. I then used the delete button to also delete it in the Web UI. I was not able to use DROP INDEX to delete the ‘day_utc_index’ from N1QL. It gave the error that the index did not exist. However, I was able to use the delete button in the Web UI to delete it.

I then created only the ‘day_utc_index’ from N1QL:

create index day_utc_index on stats (day_utc) using view;

without creating the primary index. When I select system:indexes it shows up now. The explain now shows it is using the ‘day_utc_index’

n1ql> explain select day_utc, count(*) as count from stats where day_utc is not null group by day_utc;
{
    "requestID": "406f7f9b-7d6c-4c3e-9cb7-fdfaa2da3e93",
    "signature": "json",
    "results": [
        {
            "#operator": "Sequence",
            "~children": [
                {
                    "#operator": "IndexScan",
                    "index": "day_utc_index",
                    "keyspace": "stats",
                    "limit": 9.223372036854776e+18,
                    "namespace": "default",
                    "spans": [
                        {
                            "Range": {
                                "High": null,
                                "Inclusion": 0,
                                "Low": [
                                    "null"
                                ]
                            },
                            "Seek": null
                        }
                    ],
                    "using": "view"
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "Fetch",
                                "keyspace": "stats",
                                "namespace": "default"
                            },
                            {
                                "#operator": "Filter",
                                "condition": "((`stats`.`day_utc`) is not null)"
                            },
                            {
                                "#operator": "InitialGroup",
                                "aggregates": [
                                    "count(*)"
                                ],
                                "group_keys": [
                                    "(`stats`.`day_utc`)"
                                ]
                            }
                        ]
                    }
                },
                {
                    "#operator": "IntermediateGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": [
                        "(`stats`.`day_utc`)"
                    ]
                },
                {
                    "#operator": "FinalGroup",
                    "aggregates": [
                        "count(*)"
                    ],
                    "group_keys": [
                        "(`stats`.`day_utc`)"
                    ]
                },
                {
                    "#operator": "Parallel",
                    "~child": {
                        "#operator": "Sequence",
                        "~children": [
                            {
                                "#operator": "InitialProject",
                                "result_terms": [
                                    {
                                        "expr": "(`stats`.`day_utc`)"
                                    },
                                    {
                                        "as": "count",
                                        "expr": "count(*)"
                                    }
                                ]
                            },
                            {
                                "#operator": "FinalProject"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "19.233442ms",
        "executionTime": "18.911526ms",
        "resultCount": 1,
        "resultSize": 3366
    }
}

but it still takes 8 seconds to complete the query, while being very fast (seemingly under a second) when I use the index through the Web UI.

BTW, if I don’t have the WHERE clause in the SELECT query I get the message: “No primary index on keyspace stats. Use CREATE PRIMARY INDEX to create one.”. So it seems that the WHERE clause somehow causes it to use the ‘day_utc_index’ and not complain.

6

The query must scan every entry in the index. Presumably, you are not doing the same thing through the web UI.

For now, your query is using the index. The upcoming GA release will have performance improvements over the beta, and includes specific perf settings you can use based on your query.

7

To get the query to run fast through the Web UI I had to use the “Copy to Dev” button to copy the ‘day_utc_index’ view from Production to Development. In development I edited the view to add the predefined _count function as a Reduce function and then use the “Publish” button to copy the view to production again over-writing the existing view. Then when I setup a filter and click the “Show Results” button the query which produces the same results as the N1QL query runs within a second. However,

select * from system:indexes where keyspace_id='stats';

no longer shows the ‘day_utc_index’ in the results. Also I can no longer do a SELECT query with USE INDEX specifying the ‘day_utc_index’. Also cannot drop the index because it complains the index does not exist and I am back to my original problem. At least now I know how to reproduce it. Any idea why editing and publishing the view through the Web UI is causing it to be removed from system:indexes and no longer accessible from N1QL.

8

Yes. N1QL does not allow you to manually create or alter a view index. After you create the view index using N1QL, you cannot modify it and still use it as a N1QL index.

In the future, we will add support for pre-computed materialized views in N1QL. For now, you can use map-reduce views directly for pre-computed aggregates, and you can use N1QL for ad-hoc queries, joins, etc.

9

Any idea when N1QL will have this support.

10

It’s too soon to say. This would be a new feature, and not an incremental bug fix or performance tuning.

11

Thank u very much.

Construction IS NOT NULL + GROUP BY instead of just GROUP BY is work as expected.