Index Pushdown Optimization not Applied on OrderedIntersectScan not working?

SQL++
1

---- INDEX: ----
CREATE INDEX activity_by_collection_id on test_db(base.data.collection_id, base.type, base.data.creation DESC) where type = ‘activity’ using gsi;

CREATE INDEX activity_by_account_id on test_db(base.data.account_id, base.type, base.data.creation DESC) where type = ‘activity’ using gsi;

---- QUERY: ----
SELECT test_db.* FROM test_db WHERE
type = “activity”
AND base.data.collection_id in [“36cc5e8a-c094-47da-b8f7-8e01a2dc12aa”]
AND base.data.account_id in [“4e47e0ec-b0a0-40a6-9fc3-5cff0cbc61ba”]
AND base.type = “insight added”
ORDER BY base.data.creation DESC OFFSET 10 LIMIT 3;

---- Execution Path: ----
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “OrderedIntersectScan”,
“scans”: [
{
“#operator”: “IndexScan3”,
“index”: “activity_by_account_id”,
“index_id”: “26e0d048d5148cde”,
“index_order”: [
{
“keypos”: 0
},
{
“keypos”: 1
},
{
“keypos”: 2
}
],
“index_projection”: {
“primary_key”: true
},
“keyspace”: “test_db”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““4e47e0ec-b0a0-40a6-9fc3-5cff0cbc61ba””,
“inclusion”: 3,
“low”: ““4e47e0ec-b0a0-40a6-9fc3-5cff0cbc61ba””
},
{
“high”: ““insight added””,
“inclusion”: 3,
“low”: ““insight added””
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan3”,
“index”: “activity_by_collection_id”,
“index_id”: “9bea38cd378d965d”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “test_db”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““36cc5e8a-c094-47da-b8f7-8e01a2dc12aa””,
“inclusion”: 3,
“low”: ““36cc5e8a-c094-47da-b8f7-8e01a2dc12aa””
},
{
“high”: ““insight added””,
“inclusion”: 3,
“low”: ““insight added””
}
]
}
],
“using”: “gsi”
}
]
},
{
“#operator”: “Fetch”,
“keyspace”: “test_db”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“maxParallelism”: 1,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “(((((test_db.type) = “activity”) and ((((test_db.base).data).collection_id) in [“36cc5e8a-c094-47da-b8f7-8e01a2dc12aa”])) and ((((test_db.base).data).account_id) in [“4e47e0ec-b0a0-40a6-9fc3-5cff0cbc61ba”])) and (((test_db.base).type) = “insight added”))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “test_db”,
“star”: true
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Offset”,
“expr”: “10”
},
{
“#operator”: “Limit”,
“expr”: “3”
}
]
},
“text”: “SELECT test_db.* FROM test_db WHERE \n type = “activity” \n AND base.data.collection_id in [“36cc5e8a-c094-47da-b8f7-8e01a2dc12aa”]\n AND base.data.account_id in [“4e47e0ec-b0a0-40a6-9fc3-5cff0cbc61ba”]\n AND base.type = “insight added”\n ORDER BY base.data.creation OFFSET 10 LIMIT 3;”
}

As you see all to the query matching documents are queried and the Index Pushdown Optimization is not applied. Is this a bug or did I create the indexes wrong?

Regards,

Eric

2

The Plan is right and working as expected. What is not working?
If you don’t want IntersectScan use USE INDEX (activity_by_account_id)

OR

CREATE INDEX ix1 on test_db(base.data.collection_id, base.data.account_id , base.type, base.data.creation DESC) where type = ‘activity’ using gsi;

3

As I understood it here (https://docs.couchbase.com/server/5.5/performance/index_pushdowns.html) the limit and offset should be handled using the sorted index and not all data matching the where condition should be queried. The number of activities will get really big in the near future and is used to generate user feeds, so the performance should not be impacted by the amount of activities in the database.

Did I understand something wrong ?

4

Also all predicates must have pushed to indexer, query order must have followed leading index key order, no false positives possible and query must not change order or further reduce rows then only push the offset and limit.

5

So this is a bug in Couchbase then?

6

No It is not bug. The optimization can’t be done here. There are certain rules must followed to pushdown optimization. Any optimization must always give right results. If optimization result in wrong results it should never use that optimization.

https://blog.couchbase.com/n1ql-practical-guide-second-edition/ Check out Designing Index For Query In Couchbase N1QL