Adding filter causes secondary index timeout

SQL++
1

Hello,

I have an index like so:

Definition: CREATE INDEX index_1 ON bugsentry(time) WHERE (cat = 1027) USING GSI

I have 2 queries:

  • select * from bucket_name where cat = 1027 and time > ‘2016-03-08’
  • select * from bucket_name where cat = 1027 and time > ‘2016-03-08’ and other_field = ‘xxxxx’

The first query completes relatively quickly (it takes some time just to transfer all of the data, but it starts responding immediately)

The second query returns with an Index scan timeout error.
In my opinion, since the second just adds another field to the filter (I checked the explain and confirmed this), nothing like this should be happening. No more or less of the index is scanned - why is the error manifesting as a scan timeout?

Thanks,
Drew Z.

2

Hi @dzagieboylo, sounds like a bug on our part. could you send out the explains for both queries to help me reproduce the issue?
thanks
-cihan

3

This query has an index scan timeout:

explain select * from bugsentry where skuId = 1027 and submitTime > ‘2016-03-08’ and categoryId = ‘0xfffffe’;
{
“requestID”: “eb27c579-33de-45d3-ae5b-e49cc90f2818”,
“signature”: “json”,
“results”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “1027_reports”,
“keyspace”: “bugsentry”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: null,
“Inclusion”: 0,
“Low”: [
“"2016-03-08"”
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“keyspace”: “bugsentry”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “((((bugsentry.skuId) = 1027) and ("2016-03-08" \u003c (bugsentry.submitTime))) and ((bugsentry.categoryId) = "0xfffffe"))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “17.191286ms”,
“executionTime”: “16.652251ms”,
“resultCount”: 1,
“resultSize”: 2066
}
}

This query does not:

cbq> explain select * from bugsentry where skuId = 1027 and submitTime > ‘2016-03-08’;
{
“requestID”: “82afc3b3-d87f-40a8-8313-b792b55c348f”,
“signature”: “json”,
“results”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan”,
“index”: “1027_reports”,
“keyspace”: “bugsentry”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: null,
“Inclusion”: 0,
“Low”: [
“"2016-03-08"”
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Fetch”,
“keyspace”: “bugsentry”,
“namespace”: “default”
},
{
“#operator”: “Filter”,
“condition”: “(((bugsentry.skuId) = 1027) and ("2016-03-08" \u003c (bugsentry.submitTime)))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.70378ms”,
“executionTime”: “3.652419ms”,
“resultCount”: 1,
“resultSize”: 2016
}
}

4

Actually, upon running it again I did get an index scan timeout in both instances - it seems my first test might have just been on the border of working and then more documents were indexed.

Never mind!

1 Like
5

thanks - forgot to ask this. Could you also send me a sample doc and item count in your bucket at the moment so I can populate items on my end.
thanks
-cihan

6

Hi @dzagieboylo,

If you replace SELECT * with more specific SELECTs, you can use composite / covering indexes and get significantly better performance.