The value decides selectivity. N1QL uses rule based optimization. When both indexes qualify N1QL will use one of the index. To perform better you can use index hint.
I can use index hint only if i known which index is better. In this case, if i gave an IP, i will don’t known query with idx1 or idx2 is better.
Try the following without any index hint. It should intersect the two indexes.
idx1: default( IPFrom, Ctry )
idx2: default( IPTo, Ctry )
It seems select index to run query randomly, and it’ll not make a covering index.
Can you post the index definitions, query, and EXPLAIN.
Indexes:
CREATE INDEX doc_type_idx ON default(_type) WHERE (_type is not missing)
create index ip2c_idx_1 on default(IPFrom, Ctry) where _type='ip2c'
create index ip2c_idx_2 on default(IPTo, Ctry) where _type='ip2c'
Query:
SELECT Ctry FROM default WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1
Explain: try to run explain a few times:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "ip2c_idx_1",
"index_id": "75e3b5ca955bf324",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(3127730286)"
],
"Inclusion": 0,
"Low": [
"null"
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "ip2c_idx_2",
"index_id": "b1b81c2d41db762f",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 1,
"Low": [
"3127730286"
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "doc_type_idx",
"index_id": "97523710ab687893",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"ip2c\""
],
"Inclusion": 3,
"Low": [
"\"ip2c\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`default`.`IPFrom`) <= 3127730286) and (3127730286 <= (`default`.`IPTo`))) and ((`default`.`_type`) = \"ip2c\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`Ctry`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "1"
}
]
},
"text": "SELECT Ctry FROM default WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1"
}
]
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "ip2c_idx_2",
"index_id": "b1b81c2d41db762f",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 1,
"Low": [
"3127730286"
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "doc_type_idx",
"index_id": "97523710ab687893",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"ip2c\""
],
"Inclusion": 3,
"Low": [
"\"ip2c\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "ip2c_idx_1",
"index_id": "75e3b5ca955bf324",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(3127730286)"
],
"Inclusion": 0,
"Low": [
"null"
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`default`.`IPFrom`) <= 3127730286) and (3127730286 <= (`default`.`IPTo`))) and ((`default`.`_type`) = \"ip2c\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`Ctry`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "1"
}
]
},
"text": "SELECT Ctry FROM default WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1"
}
]
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "doc_type_idx",
"index_id": "97523710ab687893",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"ip2c\""
],
"Inclusion": 3,
"Low": [
"\"ip2c\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "ip2c_idx_1",
"index_id": "75e3b5ca955bf324",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(3627730286)"
],
"Inclusion": 0,
"Low": [
"null"
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "ip2c_idx_2",
"index_id": "b1b81c2d41db762f",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 1,
"Low": [
"3627730286"
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`default`.`IPFrom`) <= 3627730286) and (3627730286 <= (`default`.`IPTo`))) and ((`default`.`_type`) = \"ip2c\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`Ctry`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "1"
}
]
},
"text": "SELECT Ctry FROM default WHERE IPFrom <= 3627730286 and IPTo >= 3627730286 and _type = 'ip2c' limit 1"
}
]
Query using index hint (get from system:completed_request):
{
"completed_requests": {
"ClientContextID": "9c7a763c-c1be-47bd-b29d-1a2f41c39bd1",
"ElapsedTime": "4.597223203s",
"ErrorCount": 0,
"PhaseCounts": {
"Fetch": 82455,
"IndexScan": 82455
},
"PhaseOperators": {
"Fetch": 1,
"IndexScan": 1
},
"RequestId": "58810004-0c1f-45d0-8320-df93630d0dbb",
"ResultCount": 1,
"ResultSize": 36,
"ServiceTime": "4.597159516s",
"State": "completed",
"Statement": "SELECT Ctry FROM default use index(ip2c_idx_2 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1",
"Time": "2016-11-04 01:51:43.985278963 +0000 UTC"
}
},
{
"completed_requests": {
"ClientContextID": "6e64800a-ab7d-4a5e-ae23-70999f1369ac",
"ElapsedTime": "4.760764578s",
"ErrorCount": 0,
"PhaseCounts": {
"Fetch": 82980,
"IndexScan": 82980
},
"PhaseOperators": {
"Fetch": 1,
"IndexScan": 1
},
"RequestId": "5964cb74-0914-4a9f-b3da-ab2f95473682",
"ResultCount": 1,
"ResultSize": 36,
"ServiceTime": "4.76070396s",
"State": "completed",
"Statement": "SELECT Ctry FROM default use index(ip2c_idx_1 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1",
"Time": "2016-11-04 01:51:33.790682543 +0000 UTC"
}
}
Ok. You need to either drop the index doc_type_idx, or add a USE INDEX clause to exclude it.
Yes, used USE INDEX and you can see, it took ~4s.
There is no USE INDEX in your query. You need to measure that and post the results here.
Please look at the last part of my prev reply:
{
"completed_requests": {
"ClientContextID": "9c7a763c-c1be-47bd-b29d-1a2f41c39bd1",
"ElapsedTime": "4.597223203s",
"ErrorCount": 0,
"PhaseCounts": {
"Fetch": 82455,
"IndexScan": 82455
},
"PhaseOperators": {
"Fetch": 1,
"IndexScan": 1
},
"RequestId": "58810004-0c1f-45d0-8320-df93630d0dbb",
"ResultCount": 1,
"ResultSize": 36,
"ServiceTime": "4.597159516s",
"State": "completed",
"Statement": "SELECT Ctry FROM default use index(ip2c_idx_2 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1",
"Time": "2016-11-04 01:51:43.985278963 +0000 UTC"
}
},
{
"completed_requests": {
"ClientContextID": "6e64800a-ab7d-4a5e-ae23-70999f1369ac",
"ElapsedTime": "4.760764578s",
"ErrorCount": 0,
"PhaseCounts": {
"Fetch": 82980,
"IndexScan": 82980
},
"PhaseOperators": {
"Fetch": 1,
"IndexScan": 1
},
"RequestId": "5964cb74-0914-4a9f-b3da-ab2f95473682",
"ResultCount": 1,
"ResultSize": 36,
"ServiceTime": "4.76070396s",
"State": "completed",
"Statement": "SELECT Ctry FROM default use index(ip2c_idx_1 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1",
"Time": "2016-11-04 01:51:33.790682543 +0000 UTC"
}
}
You need to put two the two indexes I sent you in your USE INDEX. Two indexes, not one and not three.
Did you mean this query?
SELECT Ctry FROM default use index(ip2c_idx_1, ip2c_idx_2 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1
explain:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "ip2c_idx_1",
"index_id": "75e3b5ca955bf324",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(3127730286)"
],
"Inclusion": 0,
"Low": [
"null"
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "ip2c_idx_2",
"index_id": "b1b81c2d41db762f",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Range": {
"Inclusion": 1,
"Low": [
"3127730286"
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "default",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((((`default`.`IPFrom`) <= 3127730286) and (3127730286 <= (`default`.`IPTo`))) and ((`default`.`_type`) = \"ip2c\"))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`default`.`Ctry`)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
{
"#operator": "Limit",
"expr": "1"
}
]
},
"text": "SELECT Ctry FROM default use index(ip2c_idx_1, ip2c_idx_2 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1"
}
]
It took ~9s
{
"completed_requests": {
"ClientContextID": "83cb41c1-7e64-4662-b0b5-22dc010d1c3c",
"ElapsedTime": "9.621825349s",
"ErrorCount": 0,
"PhaseCounts": {
"Fetch": 162336,
"IndexScan": 162337
},
"PhaseOperators": {
"Fetch": 1,
"IndexScan": 2
},
"RequestId": "71324da1-a9e0-4413-ab9a-82a03261adc9",
"ResultCount": 1,
"ResultSize": 36,
"ServiceTime": "9.621691998s",
"State": "completed",
"Statement": "SELECT Ctry FROM default use index(ip2c_idx_1, ip2c_idx_2 using gsi) WHERE IPFrom <= 3127730286 and IPTo >= 3127730286 and _type = 'ip2c' limit 1",
"Time": "2016-11-06 14:45:09.543766823 +0000 UTC"
}
}
Maybe CB is not good choice for this usecase. I’ll try to use redis instead.
Not sure why it is taking so long.