Hi there,
I have a query that just doing something as below and it is using a covered index and only ran under 20ms. but the issue is, randomly, it returns different values for the totalCount. does anyone have clue to what causes it? thank you.
select count(1) totalCount from products where type = ‘art’ and username = ‘hellokitty’
If data is not changing, it should not return different results. Post the text based EXPLAIN output of the query
Here you go. just want to mention that we partition on the index to 2 nodes.
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"covers": [
"cover ((`products`.`username`))",
"cover ((meta(`products`).`id`))",
"cover (count(1))"
],
"filter_covers": {
"cover ((`products`.`type`))": "art"
},
"index": "idxx_username_arts_createdAt",
"index_group_aggs": {
"aggregates": [
{
"aggregate": "COUNT",
"expr": "1",
"id": 7,
"keypos": -1
}
],
"partial": true
},
"index_id": "3c80680bea3cd893",
"index_partition_by": "[`username`]",
"index_projection": {
"entry_keys": [
7
]
},
"keyspace": "products",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"hellokitty\"",
"inclusion": 3,
"low": "\"hellokitty\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialGroup",
"aggregates": [
"sum(cover (count(1)))"
],
"group_keys": []
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(cover (count(1)))"
],
"group_keys": []
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(cover (count(1)))"
],
"group_keys": []
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"raw": true,
"result_terms": [
{
"expr": "sum(cover (count(1)))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}
What version of CB it is.
It looks like some thing went wrong. I don’t have clean answer for this.
According to plan it using idxx_username_arts_createdAt (which might be more than 1 index key) my guess is 6 index keys (based on id 7 in aggregates, entry_keys. But some how covers showing only 3 entries(it must be all index keys + meta().id all entries in index aggregates).
“~versions”: [
“6.6.5-N1QL”,
“6.6.5-10080-enterprise”
],
besides we partition on 2 nodes we also have 1 replica of the index. is it possible the replica isn’t synced? i ran the same query on all query nodes, and they all behave the same: randomly return one or the other value for totalCount.
is there a way to run the query with index node IP specified to see if the index are up-to-date?
there is no way to choose replica index it is automatic if you use scan_consistency “request_plus” it will force catch up (try at least once). Also check UI index count of all the copies that will give idea. any pending mutations.
My main concern the inconsistency in query plan.
By any chance when you get correct results did it use different index?
Run query multiple times from Query Work bench and look at Plan Text Tab and see the index used correct results and wrong results?
maybe because the partitions not the same on both to cause this issue? this is what we found as below. so we are trying to create a new index to see if the partitions will be the same on both indexes.
idxx_username_arts_createdAt: 6 partitions
idxx_username_arts_createdAt (replica): 2 partitions
we created a new index and a replica, this time, 1 partition vs 7 partitions (replica) 
@icy44 This does seem odd.
Are you running just a single CREATE INDEX statement to create both the index and its replica? (This is to rule out mutliple CREATE INDEX statements creating equivalent but separate indexes.)
Can you please post the full CREATE INDEX statement.
Is there any write activity on the database during the experiment?
Also can you please run the following against one of the index nodes and post all the JSON sub-objects it returns about this index. (There will be one from each host that has any partitions from the index and another entry from each host that has any partitions of the replica.) It will help to put the output through a JSON formatter.
curl -X GET -u <administrator_user>:<password> "http://<host>:9102/getIndexStatus?getAll=true"
If using https the port will be 19102 instead of 9102.
Thank you!
hi @Kevin.Cherkauer:
1 - yes
2 - CREATE INDEX idxx_username_arts_createdAt ON products( username ) PARTITION BY hash(username) WHERE (type = "art") WITH { "defer_build":true, "nodes":[ "10.10.30.42:8091","10.10.30.43:8091" ], "num_replica":1, "num_partition":8 }
3 - no
4 - does it matter which node that command is being run on?
so that means the number of partitions should be the same on both indexes, is that correct?
Thank you!
@icy44 Thank you. Looks good so far. It does not matter which Index node you run the getIndexStatus on as it will do scatter-gather to all Index nodes to collect their local metadata.
Number of partitions should be same (8) for both indexes. Replicas should always be identical to the “main” index (main and replica indexes differ only in which replica ID they have, with main = 0 and other replicas numbered incrementally starting from 1). All are also active for both reads and writes. The getIndexStatus output might show something strange that will give us a clue, but what you are seeing is not normal behavior.
@Kevin.Cherkauer i will let you know the result of that command once i have it. at the meantime, i have a question for "num_partition":8. do you mean that it should have 8 partitions per node? or it should be 4 per node since we spread them into 2?
@icy44 Each copy of the index (main and replica) will have 8 partitions. The main and replica copy of any given partition will be on opposite nodes so if one node dies the index is still available. The specific layout is done by a stochastic planning algorithm so it is not predictable for any given partition whether a node will have the main copy (replicaId 0) or the replica copy (replicaId 1), but this does not matter. As there are only two index nodes allowed, it is certain that of the 16 total partitions, 8 will be on each node, and the main and replica copies of each individual partition will always be on opposite nodes.
@Kevin.Cherkauer i don’t think that’s what we saw on the servers thou. we have 2 more indexes with replica and partitions, and they all end up with both indexes with different number of partitions but total is equal to what we defined.
for example, we defined: "num_replica":1, "num_partition":16. after the indexes are built, on original index, it has 9 partitions; on replica, it has 7 partitions.
@icy44 I think you must be looking at the contents of only a single Index node. Each node only contains the metadata about the partitions it holds. If you look at the other node, it will have the other ones. That is why you are always seeing N = (x + y) where N is the number of partitions specified in CREATE INDEX, x is main index partitions on the node you are looking at, and y is replica index partitions on the same node. The reason x and y vary is due to the stochastic planner I mentioned earlier – for any partition p it does not care whether a given node contains the main or replica copy of p; it just ensures that main and replica of p are always on different nodes.
