N1Ql Query is sometimes very very slow

SQL++
1

Hello,
I have a query like this:

SELECT Extent1.document.payload as result FROM H2H_AT as Extent1
WHERE (Extent1.type = ‘ATCore.ReadModel.ODCAssignment_RM’)
AND NOT (META(Extent1).id LIKE ‘%_sync%’)
AND Extent1.document.payload.H2H_ODC.EQNUM IN ([‘RAN-144’])
AND Extent1.document.payload.H2H_ODC.SITEID IN ([‘RANDSTAD’])
AND (Extent1.document.payload.H2H_ODC.STATUS = 0)

the execution plan is this:
[
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IntersectScan”,
“scans”: [
{
“#operator”: “IndexScan”,
“index”: “AT_idx_odc_equip”,
“index_id”: “da24dc3672525f6e”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“RAN-144"”
],
“Inclusion”: 3,
“Low”: [
"“RAN-144"”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan”,
“index”: “AT_idx_odc_status”,
“index_id”: “4f1a538b5a89fb0”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“0”
],
“Inclusion”: 3,
“Low”: [
“0”
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan”,
“index”: “AT_idx_type”,
“index_id”: “b0cebd4cc8dae442”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
““ATCore.ReadModel.ODCAssignment_RM””
],
“Inclusion”: 3,
“Low”: [
““ATCore.ReadModel.ODCAssignment_RM””
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan”,
“index”: “AT_idx_odc_site”,
“index_id”: “9af30d65aa28b309”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
““RANDSTAD””
],
“Inclusion”: 3,
“Low”: [
““RANDSTAD””
]
}
}
],
“using”: “gsi”
},
{
“#operator”: “IndexScan”,
“index”: “H2H_AT”,
“index_id”: “a9e97a6549715ced”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“Inclusion”: 0,
“Low”: [
“null”
]
}
}
],
“using”: “gsi”
}
]
},
{
“#operator”: “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H_AT”,
“namespace”: “default”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((((Extent1.type) = “ATCore.ReadModel.ODCAssignment_RM”) and (not ((meta(Extent1).id) like “%_sync%”))) and (((((Extent1.document).payload).H2H_ODC).EQNUM) in [“RAN-144”])) and (((((Extent1.document).payload).H2H_ODC).SITEID) in [“RANDSTAD”])) and (((((Extent1.document).payload).H2H_ODC).STATUS) = 0))”
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1.document).payload)”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1.document.payload as result FROM H2H_AT as Extent1 \r\nWHERE (Extent1.type = ‘ATCore.ReadModel.ODCAssignment_RM’) \r\nAND NOT (META(Extent1).id LIKE ‘%_sync%’) \r\nAND Extent1.document.payload.H2H_ODC.EQNUM IN ([‘RAN-144’]) \r\nAND Extent1.document.payload.H2H_ODC.SITEID IN ([‘RANDSTAD’]) \r\nAND (Extent1.document.payload.H2H_ODC.STATUS = 0)”
}
]

query
query.jpg2038×527 143 KB

the query execution time is 49 sec.

sometimes the same query has the execution time of 200-300 ms

what can I check for this problem?

thank you
diego

2

You are doing an intersection of several index scans. That can be slow. Could you try again with an index on (Extent1.type, Extent1.document.payload.H2H_ODC.EQNUM, Extent1.document.payload.H2H_ODC.SITEID, Extent1.document.payload.H2H_ODC.STATUS) ? Also, you might want to replace the IN clauses by =, since the RHS of the IN is an array of size 1.

3

thank you for fast answer!

yes I can create an index covering all the where clauses, but the expression is build on criteria choosen by user who can select many combinations of criteria.

The command is produced by Linq2Couchbase provider, so I can’t modify the part with IN clause and the user can select many values for that criteria.

The strangest thing is that sometimes the query executes in milliseconds and sometimes in many seconds in production environment, but on develoment machine the same query executes always in milliseconds.

4 
_ in like is escape character so change it to  LIKE "%\\_sync%"

Also try the following index. If it uses IntersectScan  supply USE INDEX or drop other indexes and try it.

CREATE INDEX ix1 ON H2H_AT(Extent1.type, Extent1.document.payload.H2H_ODC.STATUS, Extent1.document.payload.H2H_ODC.EQNUM, Extent1.document.payload.H2H_ODC.SITEID) WHERE NOT (META().id LIKE "%\\_sync%");
5

I think there is a problem in creating this INDEX.
In my bucket there aren’t documents with property ‘Extent1’ because this is the alias generated during LINQ to N1ql translation from the provider.
So when I create the index and modify expression with ‘USE INDEX’ result is empty (I think because when the index is created and the documents are read the result of the index is empty)

thank you

6

CREATE INDEX ix1 ON H2H_AT(type, document.payload.H2H_ODC.STATUS, document.payload.H2H_ODC.EQNUM, document.payload.H2H_ODC.SITEID) WHERE NOT (META().id LIKE "%\\_sync%");

7

thank you

I did what you said and now the explain plan of the statement is:

[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “ix1”,
“index_id”: “3deaec1b0deee06”,
“keyspace”: “H2H_AT”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“ATCore.ReadModel.ODCAssignment_RM”",
“0”,
"“RAN-144"”,
"“RANDSTAD”"
],
“Inclusion”: 3,
“Low”: [
"“ATCore.ReadModel.ODCAssignment_RM”",
“0”,
"“RAN-144"”,
"“RANDSTAD”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Fetch”,
“as”: “Extent1”,
“keyspace”: “H2H_AT”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((((((Extent1.type) = “ATCore.ReadModel.ODCAssignment_RM”) and (not ((meta(Extent1).id) like “%\\_sync%”))) and (((((Extent1.document).payload).H2H_ODC).EQNUM) in [“RAN-144”])) and (((((Extent1.document).payload).H2H_ODC).SITEID) in [“RANDSTAD”])) and (((((Extent1.document).payload).H2H_ODC).STATUS) = 0))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “result”,
“expr”: “((Extent1.document).payload)”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “SELECT Extent1.document.payload as result FROM H2H_AT as Extent1 USE INDEX (ix1)\r\nWHERE (Extent1.type = ‘ATCore.ReadModel.ODCAssignment_RM’) \r\nAND NOT (META(Extent1).id LIKE ‘%\\_sync%’) \r\nAND Extent1.document.payload.H2H_ODC.EQNUM IN ([‘RAN-144’]) \r\nAND Extent1.document.payload.H2H_ODC.SITEID IN ([‘RANDSTAD’]) \r\nAND (Extent1.document.payload.H2H_ODC.STATUS = 0)”
}
]

The execution time seems constant around 300ms and this is very good.
But the problem is that the statement is generated by linq2couchbase provider and I cannot include the USE INDEX and the WHERE clause may or may not include the same properties and not in the same order.

May I have to create one index covering every combination and permutation of parameters?

thank you

8

EXPALIN query with out USE INDEX and if still uses IntersectScan you can drop other indexes if they don’t need any other query.
If you don’t know predicate at least you might have (Extent1.type) = “ATCore.ReadModel.ODCAssignment_RM”) and (not (meta(Extent1).id) like “%\_sync%”)). in that case it choses new index and time may increase.