Hi,
I am having problem with my count queries. Using v4.5.3. I have index on all the queried fields. There is time condition in my query. If I remove time condition part from the query, it takes milliseconds to respond but with time condition, it takes more than 20 seconds. I checked out many related questions but they don’t have (or I couldn’t find) time condition and that is what slows my query down.
Here are 2 queries and EXPLAIN outputs:
With time condition:
select COUNT(*) as count from
transaction_mswhere _class='net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction' and customer.code='EVM' and transactiontime > 1567896132879
Output of EXPLAIN query:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IntersectScan”,
“scans”: [
{
"#operator": “IndexScan”,
“index”: “ptCustomerCode”,
“index_id”: “4c5b146267e036a4”,
“keyspace”: “transaction_ms”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“EVM”"
],
“Inclusion”: 3,
“Low”: [
"“EVM”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexScan”,
“index”: “paymentTransactionSecondaryIndex”,
“index_id”: “b66c0eab845e7f77”,
“keyspace”: “transaction_ms”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction”"
],
“Inclusion”: 3,
“Low”: [
"“net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction”"
]
}
}
],
“using”: “gsi”
}
]
},
{
"#operator": “Fetch”,
“keyspace”: “transaction_ms”,
“namespace”: “default”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Filter”,
“condition”: “((((transaction_ms
._class
) = “net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction”) and (((transaction_ms
.customer
).code
) = “EVM”)) and (1567896132879 < (transaction_ms
.transactiontime
)))”
},
{
"#operator": “InitialGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
}
]
}
},
{
"#operator": “IntermediateGroup”,
“aggregates”: [
"count()”
],
“group_keys”: []
},
{
"#operator": “FinalGroup”,
“aggregates”: [
“count()"
],
“group_keys”: []
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “count”,
“expr”: "count()”
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
“text”: “select COUNT(*) as count fromtransaction_ms
where _class=‘net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction’ and customer.code=‘EVM’ and transactiontime > 1567896132879”
}
]
Without time condition:
select COUNT(1) as count from
transaction_mswhere _class='net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction' and customer.code='EVM'
Output of EXPLAIN query:
[
{
“plan”: {
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexCountScan”,
“covers”: [
“cover (((transaction_ms
.customer
).code
))”,
“cover ((meta(transaction_ms
).id
))”
],
“index”: “ptCustomerCode”,
“index_id”: “4c5b146267e036a4”,
“keyspace”: “transaction_ms”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“EVM”"
],
“Inclusion”: 3,
“Low”: [
"“EVM”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “IndexCountProject”,
“result_terms”: [
{
“as”: “count”,
“expr”: “count()"
}
]
}
]
},
“text”: "select COUNT() as count fromtransaction_ms
where _class=‘net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction’ and customer.code=‘EVM’”
}
]
My query with time condition took 20.44s and the other took 337.47ms when I tried just before writing here. Both 2 queries have the same result but timings are not.
And also, here is a sample document.
{
“basket”: {
“basketAmount”: 1
},
“bankOrderId”: “----”,
“modifiedAt”: 1502375629754,
“transactionTime”: 1502375628000,
“result”: {},
“createdAt”: 1502375629754,
“merchantId”: “B9D3-A6E20103C109”,
“currency”: “TRY”,
“modifiedBy”: “----”,
“amount”: 11100,
“opc”: false,
“basketItems”: [
{
“unitPrice”: 100,
“count”: 1,
“description”: “desc”,
“basketItemId”: “0001”,
“productCode”: “test1”
}
],
“mailSubscription”: false,
“smsSubscription”: false,
“transactionId”: “12444”,
“transactionType”: “SALE”,
“createdBy”: “----”,
“installment”: 1,
“paymentMethod”: “NONE”,
“header”: {
“operationName”: “Payment”,
“application”: “Dpay”,
“transactionDate”: 1502375628000,
“transactionId”: “----”
},
"_class": “net.infoowl.fraud.microservice.transaction.domain.PaymentTransaction” ,
“customer”: {
“name”: “----”,
“ipAddress”: “----”,
“phoneNumber”: “----”,
“surname”: “----”,
“email”: “----”,
“code”: “EVM”,
}
}
My question is:
I know about IndexScan
and IndexCountScan
. There is no grouping and no distinct here. I tried with count(*)
and count(1)
but same result. I even tried triple index with _class, customer.code, transactionTime
but no change. Query with time condition doesn’t use IndexCountScan
. There is also index on transactionTime
field. What am I missing here?
Any suggestions?
Thanks in advance
Best regards
slhddn