Urgent - Extremely Slow Queries

SQL++
1

I have a mixture of 8 million documents, on couchbase, with 3 nodes of 16GB memory.
This bucket has been allocated 30GB memory.

I have primary_key, index on product_key on these documents.

Simple statements like
select max(product_key) from products
are taking around 30 seconds or more.

Other statements like group by are way more than 5 minutes and being killed.
Can anyone please help.

2

This is where you should start:

3

I appreciate your generic help. I am looking for more specific help.

4

// You are running version 4.6.0-3453 Enterprise Edition (build-3453). //

Select count(*) from closet where product_key>100000

Take around 45 seconds to run.

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexCountScan”,
“covers”: [
“cover ((closet.product_key))”,
“cover ((meta(closet).id))”
],
“index”: “product_key”,
“index_id”: “961a8f71a1a8f6f5”,
“keyspace”: “closet”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“Inclusion”: 0,
“Low”: [
“100000”
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexCountProject”,
“result_terms”: [
{
“expr”: “count()"
}
]
}
]
},
{
"#operator": “Limit”,
“expr”: “10”
}
]
},
“text”: "select count() from closet where product_key>100000 limit 10”
}
]

5

Thank you for the more specific question :slight_smile:

You can create partial indexes on product_key.

CREATE INDEX ... WHERE product_key > 100000;

CREATE INDEX ... WHERE product_key > 1000000;

If you provide more queries and EXPLAIN plans, we can offer other suggestions. cc @vsr1