Slow MAX() query with predicate that might be true or false against bucket with tens of millions of documents

SQL++
1

We’re using Couchbase Enterprise and attempting to find a configuration that will fit our needs, but are running into some trouble with creating the right GSI for our N1QL queries.

We test our configurations by replicating some of our production load into a single couchbase bucket until it reaches about 50 million documents.

There are several different kinds of documents, but the important properties common to them all that we need to build an index on would be “Type” and “UUID”, with “Type” always being a string and “UUID” either being a string or an integer.

The query we need to build the index for would be:

SELECT MAX(UUID) as max FROM production_statedata WHERE IS_NUMBER(UUID) AND Type = $type_name

And say we try executing two queries, one with $type_name = “Foo” and another with $type_name = “Bar”, and there are millions of documents with “Type”:“Foo”, but no documents with “Type”:“Bar”.

Thus, we have tried creating the index like this:

CREATE INDEX uuidcountermax ON production_statedata(UUID DESC, Type) WHERE IS_NUMBER(UUID)

And when we use the query with $type_name = “Foo”, the query completes in less than 10ms like we’d expect. But when we attempt to run the query for $type_name = “Bar”, the query takes minutes to complete.

If we try building the index like this, instead:

CREATE INDEX uuidcountermax ON production_statedata(Type, UUID DESC) WHERE IS_NUMBER(UUID)

Then the timings of the query swap and querying for $type_name = “Bar” is fast, but querying for $type_name = “Foo” is slow.

Is there an optimization we’re missing that would help our query execute performantly in either case? Is there an intelligent way for the query to early-out if there are no documents with “Type”:“Bar”?

2

CREATE INDEX uuidcountermax ON production_statedata(Type, UUID DESC) WHERE IS_NUMBER(UUID);

Chceck out Rule #11 in Create Right Index

Also check out Index Aggregation in special edition

https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/

3

I’m sorry, I can’t tell the difference between the CREATE INDEX statement you provided and the second one we had already attempted.

The only optimization I can see that we can do according to Rule #11 is to create two indexes, one with WHERE Type = "Foo and one with WHERE Type = “Bar”. But that doesn’t seem like a scalable solution when we have such an enormous keyspace and the number of different "Type"s we’ll have throughout our product’s lifetime is unknown. Is creating dozens of indexes performant, even with tens of millions of documents?

4

Did not realize MAX argument is not leading index key.
You can use 5.5 or Use the following equivalent query.

CREATE INDEX uuidcountermax ON production_statedata(Type, UUID DESC) WHERE IS_NUMBER(UUID);
SELECT UUID as max 
FROM production_statedata
 WHERE IS_NUMBER(UUID) AND Type = $type_name
ORDER BY UUID DESC LIMIT 1;
5

That seemed to do the trick! Getting satisfactory timings with that query. Thank you very much!

It is a little odd though; we are using 5.5.0, and the query plan for our previous query is using IndexScan3, and I see “index_group_aggs” with “MAX” being recognized as an aggregate:

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “2.6µs”
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“execTime”: “3.7µs”,
“servTime”: “688.392µs”
},
“privileges”: {
“List”: [
{
“Target”: “default:production_statedata”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “4.2µs”
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 8,
“#phaseSwitches”: 35,
“execTime”: “40.499µs”,
“kernTime”: “5.6µs”,
“servTime”: “4.395556157s”
},
“covers”: [
“cover ((production_statedata.Type))”,
“cover ((production_statedata.UUID))”,
“cover ((meta(production_statedata).id))”,
“cover (max(cover ((production_statedata.UUID))))”
],
“filter_covers”: {
“cover (((production_statedata.UUID) < ""))”: true,
“cover ((true < (production_statedata.UUID)))”: true,
“cover (is_number((production_statedata.UUID)))”: true
},
“index”: “production_statedata_uuicountermax”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “MAX”,
“depends”: [
1
],
“expr”: “cover ((production_statedata.UUID))”,
“id”: 3,
“keypos”: 1
}
],
“depends”: [
1
],
“partial”: true
},
“index_id”: “6642c0c9ac59277d”,
“index_projection”: {
“entry_keys”: [
3
]
},
“keyspace”: “production_statedata”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"Foo"”,
“inclusion”: 3,
“low”: “"Foo"”
},
{
“high”: “""”,
“inclusion”: 0,
“low”: “true”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:04.3955”,
“#time_absolute”: 4.3955966559999995
},
{
“#operator”: “InitialGroup”,
“#stats”: {
“#itemsIn”: 8,
“#itemsOut”: 1,
“#phaseSwitches”: 21,
“execTime”: “80.199µs”,
“kernTime”: “4.395625855s”
},
“aggregates”: [
“max(cover (max(cover ((production_statedata.UUID)))))”
],
“group_keys”: ,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.000080199
},
{
“#operator”: “IntermediateGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“execTime”: “5.5µs”,
“kernTime”: “4.395712954s”
},
“aggregates”: [
“max(cover (max(cover ((production_statedata.UUID)))))”
],
“group_keys”: ,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000055
},
{
“#operator”: “FinalGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“execTime”: “20.9µs”,
“kernTime”: “4.395723954s”
},
“aggregates”: [
“max(cover (max(cover ((production_statedata.UUID)))))”
],
“group_keys”: ,
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000209
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1,
“execTime”: “1.8µs”
},
“~children”: [
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 8,
“execTime”: “21.6µs”,
“kernTime”: “4.395749454s”
},
“result_terms”: [
{
“as”: “max”,
“expr”: “max(cover (max(cover ((production_statedata.UUID)))))”
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000216
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 4,
“execTime”: “2.9µs”,
“kernTime”: “1.5µs”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000028999999999999998
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000018000000000000001
}
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000042000000000000004
},
“#time_normal”: “00:00.0006”,
“#time_absolute”: 0.0006920920000000001
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“execTime”: “5.2µs”,
“kernTime”: “4.396517445s”
},
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000052
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.0-2958-enterprise”
],
“#time_normal”: “00:00.0000”,
“#time_absolute”: 0.0000026
}

6

5.5 Using Index aggregates. It looks like need further optimization in this use case MB-30871. cc @deepkaran.salooja