Slow covering query performance

SQL++
1

I am trying out couchbase 4.5 with just about 200,000 records. I did a covering index that should properly cover the query that i am running however i am getting extremely slow response. I tried 4.5 on centos and on windows and i am getting the same response speeds of over a min at times. there are even times when it says index scan timeout. Note that i have a second covering index for a different document type which works fine.
this is the index CREATE INDEX card_cover ON tis_card_batch(type, deleted, age, date_of_entry, batch) WHERE type=“card”;
this is the query SELECT meta().id AS id,deleted,type FROM tis_card_batch WHERE type=‘card’ AND batch=‘batch-79fb307c-7546-4d07-8726-8ce06891f16c’ AND (deleted = false OR deleted IS MISSING); and this is my query execution plan when i use EXPLAIN with my query
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“covers”: [
“cover ((tis_card_batch.type))”,
“cover ((tis_card_batch.deleted))”,
“cover ((tis_card_batch.age))”,
“cover ((tis_card_batch.date_of_entry))”,
“cover ((tis_card_batch.batch))”,
“cover ((meta(tis_card_batch).id))”
],
“filter_covers”: {
“cover ((tis_card_batch.type))”: “card”
},
“index”: “card_cover”,
“index_id”: “63ac9b5a47a4b99f”,
“keyspace”: “tis_card_batch”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“successor(“card”)”
],
“Inclusion”: 1,
“Low”: [
"“card”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “(((cover ((tis_card_batch.type)) = “card”) and (cover ((tis_card_batch.batch)) = “batch-79fb307c-7546-4d07-8726-8ce06891f16c”)) and ((cover ((tis_card_batch.deleted)) = false) or (cover ((tis_card_batch.deleted)) is missing)))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “id”,
“expr”: “cover ((meta(tis_card_batch).id))”
},
{
“expr”: “cover ((tis_card_batch.deleted))”
},
{
“expr”: “cover ((tis_card_batch.type))”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT meta().id AS id,deleted,type FROM tis_card_batch WHERE type=‘card’ AND batch=‘batch-79fb307c-7546-4d07-8726-8ce06891f16c’ AND (deleted = false OR deleted IS MISSING);”
}
]

I have been trying to get past this issue for several weeks now so any advise will be appreciated.

2

Hi @chaddy012,

Change index definition to

CREATE INDEX card_cover ON tis_card_batch(batch, deleted) WHERE type=“card”;

1 Like