ORDER BY with LIMIT very slow

SQL++
1

select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type=‘asset’ ORDER BY t.createdDateTicks ASC LIMIT 10 OFFSET 0

The above query takes 6 seconds to return result and when I remove ORDER BY clause it take only 18 MS

select t.createdDate, t.createdDateTicks from ic_v10_mammoet t where t.type=‘asset’ LIMIT 10 OFFSET 0

Index I created is: CREATE INDEX asset_createdDateTicks ON ic_v10_mammoet (createdDateTicks) WHERE type = ‘asset’

createdDateTicks field is negative number generated out of date object since I cannot specify DESC order during creation of Index. I need latest records first so had to use this workaround

Plan with ORDER BY and Plan without ORDER BY attached.
https://dl.dropboxusercontent.com/u/2781659/planwithorderby.json
https://dl.dropboxusercontent.com/u/2781659/planwithoutorderby.json

2

Its seems like it is not using the index I created on dateCreatedTicks at all, since I don’t find any reference to this index in execution plan.

I tried adding covering index as follows but that did not help in time either

create index asset_status_search on ic_v10_mammoet (type, tenantId, status, createdDateTicks) WHERE type = ‘asset’

3

Try this.

select t.createdDate, t.createdDateTicks
from ic_v10_mammoet t
where t.type='asset' and t,createdDateTicks is not null
ORDER BY t.createdDateTicks ASC LIMIT 10 OFFSET 0
4

Thank you so much geraldss. This helped to reduce my query to execute in 30 ms from earlier 6 seconds.

My original query is “select meta(t).id _id, t._sync.rev as _rev,t.*,o.tenantName ownerName from ic_v10_bucket t USE INDEX(asset_createdDateTicks_idx) inner join ic_v10_bucket o on keys ‘tenant::’||TOSTRING(t.ownerId) where t.createdDateTicks IS NOT NULL and t.type=‘asset’ and t._sync.rev is not null and t.tenantId = ‘439’ and t.status= ‘Active’ ORDER BY t.createdDateTicks LIMIT 10 OFFSET 0”

which used to take 30 seconds is now taking 4 seconds, however 4 seconds is too high for me. Can you please advise?

I see that if I remove the JOIN it gets me result in 30 ms!

5

Use this index:

( tenantId, createDateTicks ) WHERE type = ‘asset’ and status = ‘Active’;

And then ORDER BY tenantId, createDateTicks.

6

This did not help

Index is
CREATE INDEX asset_createdDateTicks_idx ON ic_v10_bucket (tenantId, createdDateTicks) WHERE type = ‘asset’ and status = ‘active’

Query is
explain select meta(t).id id, t.sync.rev as rev,t.*,o.tenantName ownerName from ic_v10_bucket t USE INDEX(asset_createdDateTicks_idx) inner join ic_v10_bucket o on keys ‘tenant::’||TOSTRING(t.ownerId) where t.createdDateTicks IS NOT NULL and t.type=‘asset’ and t._sync.rev is not null and t.tenantId = ‘439’ and t.status= ‘Active’ ORDER BY t.tenantId, t.createdDateTicks LIMIT 10 OFFSET 0

and EXPLAIN is
{
“requestID”: “daeb26b3-581b-489e-b2d4-71f26f657034”,
“clientContextID”: “604d7558-56cd-4d11-a66a-73c9994b9fec”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “asset_status_idx”,
“index_id”: “f9d793eb39bf8f03”,
“keyspace”: “ic_v10_bucket”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“Active”"
],
“Inclusion”: 3,
“Low”: [
"“Active”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “asset_tenantId_idx”,
“index_id”: “bcd6a586715af7b9”,
“keyspace”: “ic_v10_bucket”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“439"”
],
“Inclusion”: 3,
“Low”: [
"“439"”
]
}
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Fetch”,
“as”: “t”,
“keyspace”: “ic_v10_bucket”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “o”,
“keyspace”: “ic_v10_bucket”,
“namespace”: “default”,
“on_keys”: “(“tenant::” || to_string((t.ownerId)))”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((((((t.createdDateTicks) is not null) and ((t.type) = “asset”)) and (((t._sync).rev) is not null)) and ((t.tenantId) = “439”)) and ((t.status) = “Active”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “id”,
“expr”: “(meta(t).id)”
},
{
“as”: “rev”,
“expr”: “((t.sync).rev)”
},
{
“expr”: “t”,
“star”: true
},
{
“as”: “ownerName”,
“expr”: “(o.tenantName)”
}
]
}
]
}
}
]
},
{
"#operator": “Order”,
“limit”: “10”,
“offset”: “0”,
“sort_terms”: [
{
“expr”: “(t.tenantId)”
},
{
“expr”: “(t.createdDateTicks)”
}
]
},
{
"#operator": “Offset”,
“expr”: “0”
},
{
"#operator": “Limit”,
“expr”: “10”
},
{
"#operator": “FinalProject”
}
]
},
“text”: “select meta(t).id id, t.sync.rev as rev,t.*,o.tenantName ownerName from ic_v10_bucket t USE INDEX(asset_createdDateTicks_idx) inner join ic_v10_bucket o on keys ‘tenant::’||TOSTRING(t.ownerId) where t.createdDateTicks IS NOT NULL and t.type=‘asset’ and t._sync.rev is not null and t.tenantId = ‘439’ and t.status= ‘Active’ ORDER BY t.tenantId, t.createdDateTicks LIMIT 10 OFFSET 0”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “29.625124ms”,
“executionTime”: “29.582622ms”,
“resultCount”: 1,
“resultSize”: 6763
}
}

7

I see that the index on dateCreatedTicks is not being honored even though it has been explicitly mentioned to be used. By the way I do have separate index on “tenantId” on this document.

8

Is Active capitalized or not? Your index and query are inconsistent. Check all the other fields as well.

9

“Active” is correct format. I do not understand inconsistency, can you please explain so I can look into

10

As I said, please look at every field in every query and every index definition. You need to help yourself as well.

11

Here is my Index

CREATE INDEX asset_createdDateTicks_idx ON ic_v10_mammoet(ownerId, createdDateTicks) WHERE type = ‘asset’ and status = ‘Active’

Here is my query

select meta(t).id id, t.sync.rev as rev,t.*,o.tenantName ownerName from ic_v10_mammoet t USE INDEX(asset_createdDateTicks_idx) inner join ic_v10_mammoet o on keys ‘tenant::’||TOSTRING(t.ownerId) where t.createdDateTicks IS NOT NULL and t.type=‘asset’ and t._sync.rev is not null and t.tenantId = ‘439’ and t.status= ‘Active’ ORDER BY t.ownerId, t.createdDateTicks LIMIT 10 OFFSET 0

Query takes 4 seconds to complete with JOIN.

Here is EXPLAIN

{
“requestID”: “94b305ec-0d6e-4be1-aa23-2d3a22567fdc”,
“clientContextID”: “e6c5da40-5192-4e42-ab45-c3b926d5a2ff”,
“signature”: “json”,
“results”: [
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “asset_status_idx”,
“index_id”: “c678e4743be08501”,
“keyspace”: “ic_v10_mammoet”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“Active”"
],
“Inclusion”: 3,
“Low”: [
"“Active”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “t”,
“keyspace”: “ic_v10_mammoet”,
“namespace”: “default”
},
{
"#operator": “Join”,
“as”: “o”,
“keyspace”: “ic_v10_mammoet”,
“namespace”: “default”,
“on_keys”: “(“tenant::” || to_string((t.ownerId)))”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((((((t.createdDateTicks) is not null) and ((t.type) = “asset”)) and (((t._sync).rev) is not null)) and ((t.tenantId) = “439”)) and ((t.status) = “Active”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “id”,
“expr”: “(meta(t).id)”
},
{
“as”: “rev”,
“expr”: “((t.sync).rev)”
},
{
“expr”: “t”,
“star”: true
},
{
“as”: “ownerName”,
“expr”: “(o.tenantName)”
}
]
}
]
}
}
]
},
{
"#operator": “Order”,
“limit”: “10”,
“offset”: “0”,
“sort_terms”: [
{
“expr”: “(t.ownerId)”
},
{
“expr”: “(t.createdDateTicks)”
}
]
},
{
"#operator": “Offset”,
“expr”: “0”
},
{
"#operator": “Limit”,
“expr”: “10”
},
{
"#operator": “FinalProject”
}
]
},
“text”: “select meta(t).id id, t.sync.rev as rev,t.*,o.tenantName ownerName from ic_v10_mammoet t USE INDEX(asset_createdDateTicks_idx) inner join ic_v10_mammoet o on keys ‘tenant::’||TOSTRING(t.ownerId) where t.createdDateTicks IS NOT NULL and t.type=‘asset’ and t._sync.rev is not null and t.tenantId = ‘439’ and t.status= ‘Active’ ORDER BY t.ownerId, t.createdDateTicks LIMIT 10 OFFSET 0”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “20.0015ms”,
“executionTime”: “20.0015ms”,
“resultCount”: 1,
“resultSize”: 5198
}
}

12

Your query has tenantId instead of ownerId. Again, I feel we are chasing typos that you should double-check.

13

I was not able to find issue so I went ahead and created a sub document of type owner which will hold bothId and name to avoid the join.

Can you please direct me to a best practices on join and managing indexes? How do I find what exactly causing a delay and best way to creating indexes and JOIN?

ex. adding t.createdDateTicks IS NOT NULL to my original query made the index fast however all my asset document has this field so I can’t understand how did that fix it and how to overcome such situation in future.

thanks for your help on this so far that was really helpful.

14

You cannot create an index on ownerId and then query on tenantId, and expect the query to use that index.

15

Yes geraldss!! I caught that too and fixed and tested but that did not work well either. Thanks for your help so far. I changed my document structure to avoid the JOIN for now but I might have similar issues coming up soon. I will get back to you with more detailed information if I encounter any. Thanks again.

1 Like