We are using Linq2Couchbase and our documents contain .NET Guid types. In the DB they are inserted as strings but I’m wondering if there is a method similar to str_to_millis() which would provide more efficient indexing. Our queries consist of filtering by a range of Guids which typically utilize the IN() operator as well as ordering by a timestamp. I’ve tried a number of different indexes and so far have not been able to get the performance required. Running CB 4.5 on AWS (2 x m4.xlarge nodes) with ~24M documents currently.
String indexing is roughly as efficient as any other scalar indexing. If you post a sample document and your queries, we can take a look.
Sample document:
{
"time": "2016-05-24T15:05:43.2883204Z",
"regKey": "aeba70f5-3f3b-4e35-b3e4-55ac1dd21173",
"type": 5
}
Ideally we would like to group on ‘regKey’ and return the last 5 inserted documents for each of those groups sorted by time. Maybe a view is a better option?
Our indexes are:
CREATE INDEX `pinglog_time` ON `pinglog`(str_to_millis(`time`))
CREATE INDEX `pinglog_regKey` ON `pinglog`(`regKey`)
CREATE INDEX `pinglog_time_regKey_type` ON `pinglog`(str_to_millis(`time`),`regKey`,`type`)
Currently we have just been running multiple queries in parallel like the one below, but this seems inefficient.
SELECT `Extent1`.`time` as `timestamp`, `Extent1`.`type` as `type` FROM `pinglog` as `Extent1` WHERE (`Extent1`.`regKey` = '0c4edfad-86e5-4d3c-8657-33909a1ad092') ORDER BY `Extent1`.`time` DESC LIMIT 5
‘EXPLAIN’ is showing me that the indexes are being used
Please post the output of this:
CREATE INDEX idx_key_time ON pinglog( regKey, -MILLIS(time), time );
EXPLAIN SELECT time
FROM pinglog USE INDEX (idx_key_time)
WHERE regKey = "0c4edfad-86e5-4d3c-8657-33909a1ad092"
ORDER BY -MILLIS(time)
LIMIT 5;
Also, please post N1QL questions under the N1QL category.
Thanks,
Gerald
Thanks for the help Gerald, here is the explain:
cbq> EXPLAIN SELECT time FROM pinglog USE INDEX (idx_key_time) WHERE regKey = "0c4edfad-86e5-4d3c-8657-33909a1ad092" ORDER BY -MILLIS(time) LIMIT 5;
{
"requestID": "55f0b3b9-0bae-4655-beba-8a6fd730b8d6",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`pinglog`.`regKey`))",
"cover ((-str_to_millis((`pinglog`.`time`))))",
"cover ((`pinglog`.`time`))",
"cover ((meta(`pinglog`).`id`))"
],
"index": "idx_key_time",
"index_id": "3324ce878e7ca14b",
"keyspace": "pinglog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"0c4edfad-86e5-4d3c-8657-33909a1ad092\")"
],
"Inclusion": 1,
"Low": [
"\"0c4edfad-86e5-4d3c-8657-33909a1ad092\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(cover ((`pinglog`.`regKey`)) = \"0c4edfad-86e5-4d3c-8657-33909a1ad092\")"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`pinglog`.`time`))"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "5",
"sort_terms": [
{
"expr": "cover ((-str_to_millis((`pinglog`.`time`))))"
}
]
},
{
"#operator": "Limit",
"expr": "5"
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT time FROM pinglog USE INDEX (idx_key_time) WHERE regKey = \"0c4edfad-86e5-4d3c-8657-33909a1ad092\" ORDER BY -MILLIS(time) LIMIT 5"
}
],
"status": "success",
"metrics": {
"elapsedTime": "9.732715ms",
"executionTime": "9.680961ms",
"resultCount": 1,
"resultSize": 3571
}
}
Looks good.
Now please run the query without the EXPLAIN, and tell us what kind of performance you are getting (and if the results are correct).
Massive speed up, thanks Gerald! Now I need to have ‘type’ returned in the query as well. Assuming I re-create the index to include ‘type’ in the cover?
1 Like
One more assist if you could please, then I’ll bugger off. The following query now causes an index scan timeout after 3 minutes. Is there a better index when using the IN operator?
cbq> explain SELECT DISTINCT `Extent1`.`regKey` as `result` FROM `pinglog` as `Extent1` WHERE (`Extent1`.`regKey` IN (['02855b30-3e5b-4ab8-b968-3f29cd321857', '6cb5a997-87f5-4d1b-a798-55d3dde55db9', '82bf20dc-0ca1-4daa-bd73-599ceab5a9de', '5c6a0812-5f4b-4d50-8f0d-5fa85fa2ee94', 'bc50a236-128a-4f53-8cde-63d98ab3a46c', '0940ce30-77d8-47d9-afd7-6427053d9690', '381848d8-ff28-4da6-9ad7-64368237f619', '779ab760-0f12-4ed8-a416-726665071d60', '167d1968-d7af-468e-9d19-729f47eda4b5', '6bdbf4c3-15b3-4be1-ae85-85ae296192f9', 'a9569398-52a4-4e58-9732-85c989845cb6', '06862ed3-764d-4caf-bcf6-889a02a882b6', '744d694a-1338-48cd-88e4-89decb5fecd0', '244b3005-c523-4a42-8b25-8b0c8b573c5a', '441963f1-29aa-4610-8c90-97889909e803', '74f34eb6-b6ba-4fb4-ba06-97ad54912e9f', 'e26dff02-4c4a-4e34-9add-9a9065303ad8', '5d934433-c381-40d5-8de6-a886ad16abaf', '82778063-2709-4fc1-a193-00394ccb97bd', 'd74b5b73-ed0f-4644-820d-a8a2945bf109', 'd374e9ef-c31e-4ab0-b077-b44c54bf13b9', 'f185c383-4ef5-496d-9979-b57866b17e2d', '1a2c116d-1022-4808-b51f-b58d9456189d', 'f0de96e7-aa61-4dfb-9f87-b59ab0e7462d', 'c67d5e44-d0dd-4af9-9c53-d1130db085bb', '523590c2-7a13-48b3-a59a-d2afa4ff8a53', 'f1761307-1a21-4feb-9bbb-d5d20dffae2c', 'dc3c822f-3ecb-47e8-b179-d628a0d5e11b', '687425ae-0da9-479a-bb58-dafd6ed7a013', 'e6ca32e5-3899-4496-94dc-e6aad3a8bd96', '6ef8030b-0b1f-496e-ab6b-e6bcdba54248', '867e9531-7b97-4563-a6f7-ecd278cad415', 'bbe0618e-2137-46ef-91ba-3ed26e4a35d6', '31abb6b6-01c8-45b2-822d-3fc7b44c7eda', '99d9cc24-4dbc-459f-9732-545fce65013d', 'cd986da4-eaec-4732-a1ca-5dcaa3550fa9', 'cbe0dfa5-2182-4972-b081-b6a4016b58b3', '6d080690-30f4-4ec1-b764-b801b63e7142', '076d5c1e-d633-45ec-818b-d4feb33fcbed', 'fbbc7a32-17b8-4bcd-aa8a-d642c6bc7302', '6a9687d6-e05f-46e8-8611-d9cca273c60c', '9f520b89-4cab-417f-86df-da0ad41f5080', 'c018d324-7c75-46fc-ba90-3f5aa5cd9921', '80ca0ebf-d8a8-4be4-bcf2-78bea26888bd', '67139e09-2aaa-44c7-b375-552097f2abd3', 'f860a2ee-0d12-4bd5-b84b-79a37e11b9f0', '8ab30dfe-2aca-4707-8367-963dadcfea20', '439acc4f-8e86-4dc0-8267-2715bb0170b5', 'ef546f97-0a14-4172-9e7d-5c6b8b39abb3', '71dd9438-5e7c-4f99-b4f0-874481a5752f', '71f8080f-ec18-4ce0-bee6-a40c8151dce9', '0bf211ac-ca5c-4e16-a261-26f9a432e1f5', '8afdca59-00b4-48b7-83c7-6b8f9023cccd', '52d8259a-2ef5-4794-b12c-6c46385e5ae3', '670b016e-f4db-4c90-b93e-6c49687ebfd1', '24aaa6a2-c139-41c9-8b5b-07a144d2118a', '9b3883af-b452-4673-b83d-c18964a2365b', '18403e8f-8cbc-4f36-9ad3-7e4a4b40f494', '35cd0cf3-4988-480c-8ebe-7e96b1bd4b07', '2e56476c-bced-4ac4-a4d8-abc29ff03c0e', 'e19300bb-7c98-451c-86bd-03fb67598381', '73a83301-953f-4a9b-8d17-2ebc52ada244', 'b68a5476-0606-4882-abbd-75f0e1794986', '0a3ceb73-0045-4840-b31a-7ec9c25babce', 'de3353bb-12f8-47f8-b3a9-8e6fc15dc97d', '26fe5e3c-caf8-448f-bd0d-b14bb3679687', '9e789841-34eb-4b98-bee6-ddcc8140b8d6', 'd31a4c23-e654-4f28-8278-f51ac7faf2af', '09764e7b-1d08-46a8-83ee-f746665497c7', 'efa9266a-206b-4f67-a412-4f22fd0ea55d', '54c4a0f6-3d52-49c3-9254-87788210a5e9', '72b7d5ee-e9ef-429f-b90a-88c6330b7546', '2e6308a9-4555-422b-a2c5-9e0b64a8a567', '2ca44004-a081-48d0-9cfa-73323e068a7e', '4a4cdddb-ff65-429c-b84e-076d39c17456', '37fd2c3b-701b-413b-98f6-07afb8984b13', 'a178d50a-748a-473c-8f32-403f87081692', 'dced4bc6-4205-47b5-aab3-4459acfc5d9c', 'c67583ab-7ee2-414d-8b6f-44b430fd7842', 'b32f7b6c-40b4-4ff9-95ca-70f47e16425b', 'be050d11-310f-4021-b49a-721be7ac0c98', '7e49655b-68e7-48ac-9fc4-ebb497f4fe4b', '15feb036-3307-472b-8f4b-20a5362c9167', '2d42c4ca-6706-46a1-aaae-20cbf307b68b', '6b0230ac-300b-4f15-9076-217feaf684b3', 'ab71b080-4187-498d-ab82-6e0cf6b81b06', '532ffb49-e7ce-42ee-8107-92c49981e842', '34ae0535-55db-4127-b227-93f6a4a68518', 'c41f00f3-9918-429f-be44-9e2f5d26ebe2', 'c4d70d95-025d-4632-9f35-d1ea1164f4f9', 'ae7aaf4b-240c-4586-9cbc-fddf5a2d725b', '5b6510ea-9f3e-4bdd-a268-0873f0dd0007', 'fd4d1092-2639-47cd-a174-1c72b5d58f2e', '5af5a069-368e-468a-92c1-1c8fb3e880c9', '1968a523-8a76-4465-ae9f-30553693203c', '52591375-a125-47df-83cd-4c784295869c', '26de0a43-0820-4f08-9225-4d045922e416']) AND (STR_TO_MILLIS(`Extent1`.`time`) > STR_TO_MILLIS("2016-05-27T21:42:48.0832962Z"))) ORDER BY `Extent1`.`time` DESC
> ;
{
"requestID": "77ccbe2c-1a88-45e5-9224-7a513b84223a",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`Extent1`.`regKey`))",
"cover ((-str_to_millis((`Extent1`.`time`))))",
"cover ((`Extent1`.`time`))",
"cover ((meta(`Extent1`).`id`))"
],
"index": "idx_key_time",
"index_id": "3324ce878e7ca14b",
"keyspace": "pinglog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"fd4d1092-2639-47cd-a174-1c72b5d58f2e\")"
],
"Inclusion": 1,
"Low": [
"\"02855b30-3e5b-4ab8-b968-3f29cd321857\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((cover ((`Extent1`.`regKey`)) in [\"02855b30-3e5b-4ab8-b968-3f29cd321857\", \"6cb5a997-87f5-4d1b-a798-55d3dde55db9\", \"82bf20dc-0ca1-4daa-bd73-599ceab5a9de\", \"5c6a0812-5f4b-4d50-8f0d-5fa85fa2ee94\", \"bc50a236-128a-4f53-8cde-63d98ab3a46c\", \"0940ce30-77d8-47d9-afd7-6427053d9690\", \"381848d8-ff28-4da6-9ad7-64368237f619\", \"779ab760-0f12-4ed8-a416-726665071d60\", \"167d1968-d7af-468e-9d19-729f47eda4b5\", \"6bdbf4c3-15b3-4be1-ae85-85ae296192f9\", \"a9569398-52a4-4e58-9732-85c989845cb6\", \"06862ed3-764d-4caf-bcf6-889a02a882b6\", \"744d694a-1338-48cd-88e4-89decb5fecd0\", \"244b3005-c523-4a42-8b25-8b0c8b573c5a\", \"441963f1-29aa-4610-8c90-97889909e803\", \"74f34eb6-b6ba-4fb4-ba06-97ad54912e9f\", \"e26dff02-4c4a-4e34-9add-9a9065303ad8\", \"5d934433-c381-40d5-8de6-a886ad16abaf\", \"82778063-2709-4fc1-a193-00394ccb97bd\", \"d74b5b73-ed0f-4644-820d-a8a2945bf109\", \"d374e9ef-c31e-4ab0-b077-b44c54bf13b9\", \"f185c383-4ef5-496d-9979-b57866b17e2d\", \"1a2c116d-1022-4808-b51f-b58d9456189d\", \"f0de96e7-aa61-4dfb-9f87-b59ab0e7462d\", \"c67d5e44-d0dd-4af9-9c53-d1130db085bb\", \"523590c2-7a13-48b3-a59a-d2afa4ff8a53\", \"f1761307-1a21-4feb-9bbb-d5d20dffae2c\", \"dc3c822f-3ecb-47e8-b179-d628a0d5e11b\", \"687425ae-0da9-479a-bb58-dafd6ed7a013\", \"e6ca32e5-3899-4496-94dc-e6aad3a8bd96\", \"6ef8030b-0b1f-496e-ab6b-e6bcdba54248\", \"867e9531-7b97-4563-a6f7-ecd278cad415\", \"bbe0618e-2137-46ef-91ba-3ed26e4a35d6\", \"31abb6b6-01c8-45b2-822d-3fc7b44c7eda\", \"99d9cc24-4dbc-459f-9732-545fce65013d\", \"cd986da4-eaec-4732-a1ca-5dcaa3550fa9\", \"cbe0dfa5-2182-4972-b081-b6a4016b58b3\", \"6d080690-30f4-4ec1-b764-b801b63e7142\", \"076d5c1e-d633-45ec-818b-d4feb33fcbed\", \"fbbc7a32-17b8-4bcd-aa8a-d642c6bc7302\", \"6a9687d6-e05f-46e8-8611-d9cca273c60c\", \"9f520b89-4cab-417f-86df-da0ad41f5080\", \"c018d324-7c75-46fc-ba90-3f5aa5cd9921\", \"80ca0ebf-d8a8-4be4-bcf2-78bea26888bd\", \"67139e09-2aaa-44c7-b375-552097f2abd3\", \"f860a2ee-0d12-4bd5-b84b-79a37e11b9f0\", \"8ab30dfe-2aca-4707-8367-963dadcfea20\", \"439acc4f-8e86-4dc0-8267-2715bb0170b5\", \"ef546f97-0a14-4172-9e7d-5c6b8b39abb3\", \"71dd9438-5e7c-4f99-b4f0-874481a5752f\", \"71f8080f-ec18-4ce0-bee6-a40c8151dce9\", \"0bf211ac-ca5c-4e16-a261-26f9a432e1f5\", \"8afdca59-00b4-48b7-83c7-6b8f9023cccd\", \"52d8259a-2ef5-4794-b12c-6c46385e5ae3\", \"670b016e-f4db-4c90-b93e-6c49687ebfd1\", \"24aaa6a2-c139-41c9-8b5b-07a144d2118a\", \"9b3883af-b452-4673-b83d-c18964a2365b\", \"18403e8f-8cbc-4f36-9ad3-7e4a4b40f494\", \"35cd0cf3-4988-480c-8ebe-7e96b1bd4b07\", \"2e56476c-bced-4ac4-a4d8-abc29ff03c0e\", \"e19300bb-7c98-451c-86bd-03fb67598381\", \"73a83301-953f-4a9b-8d17-2ebc52ada244\", \"b68a5476-0606-4882-abbd-75f0e1794986\", \"0a3ceb73-0045-4840-b31a-7ec9c25babce\", \"de3353bb-12f8-47f8-b3a9-8e6fc15dc97d\", \"26fe5e3c-caf8-448f-bd0d-b14bb3679687\", \"9e789841-34eb-4b98-bee6-ddcc8140b8d6\", \"d31a4c23-e654-4f28-8278-f51ac7faf2af\", \"09764e7b-1d08-46a8-83ee-f746665497c7\", \"efa9266a-206b-4f67-a412-4f22fd0ea55d\", \"54c4a0f6-3d52-49c3-9254-87788210a5e9\", \"72b7d5ee-e9ef-429f-b90a-88c6330b7546\", \"2e6308a9-4555-422b-a2c5-9e0b64a8a567\", \"2ca44004-a081-48d0-9cfa-73323e068a7e\", \"4a4cdddb-ff65-429c-b84e-076d39c17456\", \"37fd2c3b-701b-413b-98f6-07afb8984b13\", \"a178d50a-748a-473c-8f32-403f87081692\", \"dced4bc6-4205-47b5-aab3-4459acfc5d9c\", \"c67583ab-7ee2-414d-8b6f-44b430fd7842\", \"b32f7b6c-40b4-4ff9-95ca-70f47e16425b\", \"be050d11-310f-4021-b49a-721be7ac0c98\", \"7e49655b-68e7-48ac-9fc4-ebb497f4fe4b\", \"15feb036-3307-472b-8f4b-20a5362c9167\", \"2d42c4ca-6706-46a1-aaae-20cbf307b68b\", \"6b0230ac-300b-4f15-9076-217feaf684b3\", \"ab71b080-4187-498d-ab82-6e0cf6b81b06\", \"532ffb49-e7ce-42ee-8107-92c49981e842\", \"34ae0535-55db-4127-b227-93f6a4a68518\", \"c41f00f3-9918-429f-be44-9e2f5d26ebe2\", \"c4d70d95-025d-4632-9f35-d1ea1164f4f9\", \"ae7aaf4b-240c-4586-9cbc-fddf5a2d725b\", \"5b6510ea-9f3e-4bdd-a268-0873f0dd0007\", \"fd4d1092-2639-47cd-a174-1c72b5d58f2e\", \"5af5a069-368e-468a-92c1-1c8fb3e880c9\", \"1968a523-8a76-4465-ae9f-30553693203c\", \"52591375-a125-47df-83cd-4c784295869c\", \"26de0a43-0820-4f08-9225-4d045922e416\"]) and (str_to_millis(\"2016-05-27T21:42:48.0832962Z\") \u003c str_to_millis(cover ((`Extent1`.`time`)))))"
},
{
"#operator": "InitialProject",
"distinct": true,
"result_terms": [
{
"as": "result",
"expr": "cover ((`Extent1`.`regKey`))"
}
]
},
{
"#operator": "Distinct"
}
]
}
},
{
"#operator": "Distinct"
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"desc": true,
"expr": "cover ((`Extent1`.`time`))"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT DISTINCT `Extent1`.`regKey` as `result` FROM `pinglog` as `Extent1` WHERE (`Extent1`.`regKey` IN (['02855b30-3e5b-4ab8-b968-3f29cd321857', '6cb5a997-87f5-4d1b-a798-55d3dde55db9', '82bf20dc-0ca1-4daa-bd73-599ceab5a9de', '5c6a0812-5f4b-4d50-8f0d-5fa85fa2ee94', 'bc50a236-128a-4f53-8cde-63d98ab3a46c', '0940ce30-77d8-47d9-afd7-6427053d9690', '381848d8-ff28-4da6-9ad7-64368237f619', '779ab760-0f12-4ed8-a416-726665071d60', '167d1968-d7af-468e-9d19-729f47eda4b5', '6bdbf4c3-15b3-4be1-ae85-85ae296192f9', 'a9569398-52a4-4e58-9732-85c989845cb6', '06862ed3-764d-4caf-bcf6-889a02a882b6', '744d694a-1338-48cd-88e4-89decb5fecd0', '244b3005-c523-4a42-8b25-8b0c8b573c5a', '441963f1-29aa-4610-8c90-97889909e803', '74f34eb6-b6ba-4fb4-ba06-97ad54912e9f', 'e26dff02-4c4a-4e34-9add-9a9065303ad8', '5d934433-c381-40d5-8de6-a886ad16abaf', '82778063-2709-4fc1-a193-00394ccb97bd', 'd74b5b73-ed0f-4644-820d-a8a2945bf109', 'd374e9ef-c31e-4ab0-b077-b44c54bf13b9', 'f185c383-4ef5-496d-9979-b57866b17e2d', '1a2c116d-1022-4808-b51f-b58d9456189d', 'f0de96e7-aa61-4dfb-9f87-b59ab0e7462d', 'c67d5e44-d0dd-4af9-9c53-d1130db085bb', '523590c2-7a13-48b3-a59a-d2afa4ff8a53', 'f1761307-1a21-4feb-9bbb-d5d20dffae2c', 'dc3c822f-3ecb-47e8-b179-d628a0d5e11b', '687425ae-0da9-479a-bb58-dafd6ed7a013', 'e6ca32e5-3899-4496-94dc-e6aad3a8bd96', '6ef8030b-0b1f-496e-ab6b-e6bcdba54248', '867e9531-7b97-4563-a6f7-ecd278cad415', 'bbe0618e-2137-46ef-91ba-3ed26e4a35d6', '31abb6b6-01c8-45b2-822d-3fc7b44c7eda', '99d9cc24-4dbc-459f-9732-545fce65013d', 'cd986da4-eaec-4732-a1ca-5dcaa3550fa9', 'cbe0dfa5-2182-4972-b081-b6a4016b58b3', '6d080690-30f4-4ec1-b764-b801b63e7142', '076d5c1e-d633-45ec-818b-d4feb33fcbed', 'fbbc7a32-17b8-4bcd-aa8a-d642c6bc7302', '6a9687d6-e05f-46e8-8611-d9cca273c60c', '9f520b89-4cab-417f-86df-da0ad41f5080', 'c018d324-7c75-46fc-ba90-3f5aa5cd9921', '80ca0ebf-d8a8-4be4-bcf2-78bea26888bd', '67139e09-2aaa-44c7-b375-552097f2abd3', 'f860a2ee-0d12-4bd5-b84b-79a37e11b9f0', '8ab30dfe-2aca-4707-8367-963dadcfea20', '439acc4f-8e86-4dc0-8267-2715bb0170b5', 'ef546f97-0a14-4172-9e7d-5c6b8b39abb3', '71dd9438-5e7c-4f99-b4f0-874481a5752f', '71f8080f-ec18-4ce0-bee6-a40c8151dce9', '0bf211ac-ca5c-4e16-a261-26f9a432e1f5', '8afdca59-00b4-48b7-83c7-6b8f9023cccd', '52d8259a-2ef5-4794-b12c-6c46385e5ae3', '670b016e-f4db-4c90-b93e-6c49687ebfd1', '24aaa6a2-c139-41c9-8b5b-07a144d2118a', '9b3883af-b452-4673-b83d-c18964a2365b', '18403e8f-8cbc-4f36-9ad3-7e4a4b40f494', '35cd0cf3-4988-480c-8ebe-7e96b1bd4b07', '2e56476c-bced-4ac4-a4d8-abc29ff03c0e', 'e19300bb-7c98-451c-86bd-03fb67598381', '73a83301-953f-4a9b-8d17-2ebc52ada244', 'b68a5476-0606-4882-abbd-75f0e1794986', '0a3ceb73-0045-4840-b31a-7ec9c25babce', 'de3353bb-12f8-47f8-b3a9-8e6fc15dc97d', '26fe5e3c-caf8-448f-bd0d-b14bb3679687', '9e789841-34eb-4b98-bee6-ddcc8140b8d6', 'd31a4c23-e654-4f28-8278-f51ac7faf2af', '09764e7b-1d08-46a8-83ee-f746665497c7', 'efa9266a-206b-4f67-a412-4f22fd0ea55d', '54c4a0f6-3d52-49c3-9254-87788210a5e9', '72b7d5ee-e9ef-429f-b90a-88c6330b7546', '2e6308a9-4555-422b-a2c5-9e0b64a8a567', '2ca44004-a081-48d0-9cfa-73323e068a7e', '4a4cdddb-ff65-429c-b84e-076d39c17456', '37fd2c3b-701b-413b-98f6-07afb8984b13', 'a178d50a-748a-473c-8f32-403f87081692', 'dced4bc6-4205-47b5-aab3-4459acfc5d9c', 'c67583ab-7ee2-414d-8b6f-44b430fd7842', 'b32f7b6c-40b4-4ff9-95ca-70f47e16425b', 'be050d11-310f-4021-b49a-721be7ac0c98', '7e49655b-68e7-48ac-9fc4-ebb497f4fe4b', '15feb036-3307-472b-8f4b-20a5362c9167', '2d42c4ca-6706-46a1-aaae-20cbf307b68b', '6b0230ac-300b-4f15-9076-217feaf684b3', 'ab71b080-4187-498d-ab82-6e0cf6b81b06', '532ffb49-e7ce-42ee-8107-92c49981e842', '34ae0535-55db-4127-b227-93f6a4a68518', 'c41f00f3-9918-429f-be44-9e2f5d26ebe2', 'c4d70d95-025d-4632-9f35-d1ea1164f4f9', 'ae7aaf4b-240c-4586-9cbc-fddf5a2d725b', '5b6510ea-9f3e-4bdd-a268-0873f0dd0007', 'fd4d1092-2639-47cd-a174-1c72b5d58f2e', '5af5a069-368e-468a-92c1-1c8fb3e880c9', '1968a523-8a76-4465-ae9f-30553693203c', '52591375-a125-47df-83cd-4c784295869c', '26de0a43-0820-4f08-9225-4d045922e416']) AND (STR_TO_MILLIS(`Extent1`.`time`) \u003e STR_TO_MILLIS(\"2016-05-27T21:42:48.0832962Z\"))) ORDER BY `Extent1`.`time` DESC"
}
],
"status": "success",
"metrics": {
"elapsedTime": "14.537602ms",
"executionTime": "14.485458ms",
"resultCount": 1,
"resultSize": 11951
}
}Yes, you would need to add type to the covering index. In general, you should try to always use covering indexes with N1QL. There are some exceptions, but in 4.5 and earlier, covering is a good idea.
For the IN query, you should be using 4.5 Beta, not 4.5 DP. Can you try it with 4.5 Beta?
Looks like we’re running 4.5.0-2203. Is there a specific download for Ubuntu you can point me toward?
I went to this page and found the 4.5 BETA Ubuntu downloads. I think you need to download again, to be sure you have the Beta.
There will also be a 4.5 GA release in a few weeks, and that will have several N1QL improvements and fixes over Beta.
Hey Gerald, I just compiled from source using this manifest: https://github.com/couchbase/manifest/blob/master/couchbase-server/watson/4.5.0.xml
I’m still getting an index scan timeout when filtering by a large number of IN() items. There must be a more efficient way for format this query.
I’ve included the explain from this new node as an attachement:
explain.zip (8.3 KB)
I was not able to download explain.zip. After reading above posts i think the query might be pushing regKey only indexer and it might be generating lot of keys. If possible paste first few spans of the explain.
The second key in index mentioned previous posts has negative key and query uses positive value. Try one of the following options.
- CREATE INDEX idx_key_time2 ON default( regKey, str_to_millis(time), time ); and try the query
- Change the query condition (-STR_TO_MILLIS(
Extent1.time) < -STR_TO_MILLIS(“2016-05-27T21:42:48.0832962Z”)))
The bulk of the explain is the large list of ‘spans’ of which I’ll only include a few…
{ "requestID": "7f91e0de-f0b0-4a92-9ed5-9df16b813e3b", "signature": "json", "results": [ { "plan": { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "covers": [ "cover ((pinglog.regKey))", "cover ((-str_to_millis((pinglog.time))))", "cover ((pinglog.time))", "cover ((pinglog.type))", "cover ((meta(pinglog).id))" ], "index": "idx_key_time_type", "index_id": "97b89bce73253019", "keyspace": "pinglog", "namespace": "default", "spans": [ { "Range": { "High": [ "successor(\"017c1b87-4359-4e0d-bad7-ee1bff221d3b\")" ], "Inclusion": 1, "Low": [ "\"017c1b87-4359-4e0d-bad7-ee1bff221d3b\"" ] } }, { "Range": { "High": [ "successor(\"042cb0a5-8888-472b-85dd-09cd25e9eaee\")" ], "Inclusion": 1, "Low": [ "\"042cb0a5-8888-472b-85dd-09cd25e9eaee\"" ] } }, { "Range": { "High": [ "successor(\"061b2aaa-3539-4522-9648-ad98d412dd27\")" ], "Inclusion": 1, "Low": [ "\"061b2aaa-3539-4522-9648-ad98d412dd27\"" ] } } .... ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Filter", "condition": "(((\"2016-06-03T00:00:00Z\" \u003c= cover ((pinglog.time))) and (cover ((pinglog.time)) \u003c \"2016-06-06T00:00:00Z\")) and (cover ((pinglog.regKey)) in [\"68f45389-0213-441b-b69d-4e35ef94bb20\", \"4d0bbd74-6bda-4afb-a066-8672c0705da9\", \"7b85c4ee-f178-462b-8fec-45afd96e1013\", \"fd628ded-c16f-4feb-827c-65554dadc64a\", \"8a9c751a-22f9-46ad-9216-9762de25b3a8\", \"cac99a6b-fe9d-40ca-9f48-14cf3adb2a76\", \"cb6be796-61ad-4db9-a7ea-28a978e60e04\", \"be475136-8820-4bc8-bef7-3175bd1aa701\", \"ca95b7c2-644c-489e-810c-319c8e11fbed\", \"76472b0d-03c3-4319-9257-52c43cfdd209\", \"cd58b44e-a6a8-4417-b79e-07503513b65c\", \"cb34007f-e02c-4bff-98e4-d5095297be91\", \"0ba3b04a-2535-4794-ac31-d52782c42189\", \"84654a97-0f31-4157-a7be-82e96367d56f\", \"4cc6789f-68a7-4a06-a0d2-8896dcc7a9a5\", \"fe9fd951-bed0-44e0-acbd-2226e9857f4c\", \"f2f92105-842d-407b-94a2-2232c607afdf\", \"76894fe7-dee7-47bc-91d0-004f9308d7d2\", \"1a9375d4-fd31-42a9-ae65-4efafdd37014\", \"e63ea199-f3f1-4808-a0c8-82d52befb4d8\", \"e3dc990a-49b3-4bae-a3fc-47cccd11c448\", \"fd8e9cfd-364f-4abf-925a-00e55bcedee1\", \"5b54f016-7238-4cdc-8562-4dfb695b458e\", \"30be5014-e5e5-4eb6-b4a0-0d5b7094da75\", \"5edcea4b-489b-400c-a62d-aae89816be8a\", \"92601485-e8f2-42a5-829e-61452fa38bd6\", \"216fa9fe-fccd-41a5-8588-f9bbf0fe3053\", \"bb28a0c6-9710-43db-8a15-63cff0cb7805\", \"1f9df9b3-9c1e-48e7-bfc3-38416303fc1f\", \"cd2a9c2d-d1fa-456e-bf3a-5c279d2eb36e\", \"b61314f3-1524-45e0-afa1-b604db523336\", \"d5519f14-c9bf-4575-a72e-5eb69bb4886c\", \"acb8b317-f456-4b08-a0c0-64304ed1ca30\", \"bde1cc47-55a9-4363-884e-7ef661d23718\", \"8cbecf2c-0d1e-44d6-87fc-7f3aff58dd37\", \"312a9d75-226f-4fdf-a68e-7f864fbea37d\", \"74953b97-fa7c-4053-a0ed-5ebc922de005\", \"ba06cab5-49b8-42d0-b984-8bdd2006e081\", \"e1382b4e-2cdc-4d14-a10c-5a9880188db4\", \"a84128e8-e4ef-4c30-b357-8c3fe93e9848\", \"832ca057-b7e7-4011-80ec-8d08a19de332\", \"aa8c0b7e-2c9f-4695-91ab-6190463555fd\", \"10d62e75-9daa-4e2e-b817-633ea6f91cd4\", \"1f3e74a5-ba64-42cd-9b47-9e84149a56dd\", \"ca75c611-dde3-4d56-b4ec-666dc191d085\", \"21c36dfc-c5d4-4274-992b-66d5023a33ec\", \"f49e61ff-635e-4e12-a305-6b5546ab3bf2\", \"dfb70d39-8320-4e9a-91a9-aeed811d6e2c\", \"276d42bf-38b4-46ff-a767-ea49a675cf5e\", \"543f050b-9a1c-4056-8863-ea512d280570\", \"7307517f-5935-4b59-baca-75f90ac76ba5\", \"017c1b87-4359-4e0d-bad7-ee1bff221d3b\", \"8ab9cd4f-bbe8-4513-bced-f179e0b7d18e\", \"cf501770-8e76-4b62-a67a-f1a1609fef0d\", \"adfcb4cf-62d5-4b8d-96c6-fb0abb330e71\", \"73c0edb8-1833-47a1-9216-3c0d2cf4a183\", \"c40cc49e-a005-48e0-b576-442cfdc6cb1a\", \"5be67e1d-2491-480f-9e4b-448df567c248\", \"20820b23-1bd6-4805-8633-7ffa9daba5d1\", \"8294e62c-f10d-4035-b091-559c05292655\", \"e3bd281c-4f17-454e-b9f3-5675b5f2e139\", \"69b487f5-440e-4e3a-a132-5852c7c62d60\", \"e91ff335-92eb-4d3b-b379-58b81a757c0d\", \"2189ed4e-4000-47b8-b66a-5bf8c12eb761\", \"7dfc2413-c6dc-45a5-99ec-6676e936643f\", \"814922a0-8fc2-4902-bdc0-8aefe4cabee6\", \"dedde284-d76e-417b-a40c-8c3940788a66\", \"c557bbfa-2eb4-43cd-a06d-8d3fb2d97770\", \"48f9c54c-7ae6-49c1-82f9-ba281093e8d9\", \"151f7788-511e-471c-8fdc-093e613a9737\", \"0fac727f-c105-4a95-9121-396b2625bca1\", \"532b3fa0-f298-4d82-aaa6-4615ef667844\", \"c7e92cd9-950a-4fb1-8d39-518ae83f5dbf\", \"100d5c8d-e54b-4b1a-a422-51f86c0f915f\", \"c5c92bdd-00a8-4748-a6c2-52b9a4bb4284\", \"bd288df5-0437-4b7b-a4b1-5705e4f37809\", \"dbdb3432-2dcc-4e1c-8d05-8891aeae45a8\", \"dfb174e7-f627-4983-a8c6-8bda67ac7456\", \"50e4c6b8-75c9-42f3-a2cf-8c63de8c7ae6\", \"6f50ca58-d7ff-420f-8bc0-969224d576a6\", \"561093fc-a7f9-4c08-befb-c192ea4f2cd2\", \"f2a81de9-bd82-442a-b483-d1e93e083492\", \"54dafee8-1c9d-4df4-91bc-a1cf0c4fdb49\", \"136e71af-5db2-425d-968c-0401fcc4e687\", \"285e6b2e-6150-4fc9-b9af-a214a99b3772\", \"803c48d4-a9f1-4b5b-bbfc-075c0b321f46\", \"b81ee8da-068f-4627-951c-a4009df9bc95\", \"3b8e9808-412f-45c0-bb2c-3eb83c04052a\", \"e918c9f3-1034-45ad-a4c2-aa0f883d33a2\", \"40224d41-50cd-46c1-8dbd-65a9f70c1700\", \"b7ad8a01-5936-46f2-a193-ab6a3f9f2465\", \"061b2aaa-3539-4522-9648-ad98d412dd27\", \"f2443042-0ce2-443d-b402-0052a0407656\", \"d27d057c-f792-44e8-9e99-8def3bfeacaf\", \"79e031ee-dff3-4381-a6ff-99b6565d6cf7\", \"0b490fb8-d284-4073-bb79-b09975b5205b\", \"bab75953-abf0-4fb1-ad33-a3bea1630928\", \"cb075665-5954-4110-b4f1-b193c8125b14\", \"84fc0444-5c18-4898-9d3f-b7e6ae693d6d\", \"3624c7ba-dd94-4aaf-b1fd-be8bdcb92b0a\", \"d30a7c87-df34-4f11-91f0-08e1504b5b14\", \"3a48ac05-8457-4e5c-aea0-02e78c0cc4bc\", \"2becf55c-291f-49c2-83d3-208bdb06a83d\", \"042cb0a5-8888-472b-85dd-09cd25e9eaee\", \"eb3b87f4-0b52-44dc-b8f1-0dca178fb2c6\", \"fa4cc4e7-3f2b-4591-9115-22a850ab6b05\", \"7909c506-dcd1-4697-9abf-16a5a7c536ec\", \"4490bdcc-37cf-4207-9c16-8d36ab13c8eb\", \"7387d233-b288-4120-a091-37634dac01f4\", \"b4ad7e5d-9bcc-4b5f-8867-c9ac9c7e3bfd\", \"d3e799af-5378-41c3-b4f0-f335282f8519\", \"7d22aae9-aaf5-4515-b6d9-f714d2221a11\", \"7d864205-a45e-46b7-8621-f99f971a55d4\", \"dabf337b-2b07-4a93-8383-e2f46708cdd1\", \"6437f89d-e2c8-42aa-b923-e3f0991379cf\", \"9695a0c5-7afa-4173-a8dd-e4b2ffb6d12e\", \"fe3bb8f0-0f14-450a-8bb3-fc2b5b4d2f96\", \"915be494-6015-43d8-8a96-e9af6fc76439\", \"e3547ada-2a11-4e10-8721-fd900fa6c982\", \"13c73c7b-96b8-4908-a646-094812fea20e\", \"d1147d25-1f16-4285-934a-121362d1d619\", \"16337b57-b357-49d3-a355-22fb0150cc0b\", \"d51029fc-da2a-44d0-89b0-5f8f5a6fa602\", \"ac3a73af-2f6a-4671-903f-61e71ba2e07f\", \"3ba39b56-78c4-43fb-aeb9-1655b9012216\", \"0c4edfad-86e5-4d3c-8657-33909a1ad092\", \"ef6286cb-009c-4df3-9fde-398011833c70\", \"6b147198-6f57-4b26-b9ee-7858e15fe5a3\", \"a1d6162b-2a9d-4563-99d7-7ad62562ebd5\", \"0c9c1ce2-2c8b-4dc5-b4a9-88e7a098e8a9\", \"311aa9ca-d949-46aa-a91d-50fa8064f86d\", \"f1c80829-5aa0-4177-9ea4-1f09803bf1fd\", \"9bc4f0cd-1ad5-42ed-bd10-1f18d5fe4035\"]))" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "cover ((pinglog.regKey))" }, { "expr": "cover ((pinglog.time))" }, { "expr": "cover ((pinglog.type))" } ] }, { "#operator": "FinalProject" } ] } } ] }, "text": "SELECTregKey,time,typeFROMpinglogWHERE ((time\u003e= '2016-06-03T00:00:00Z') AND (time\u003c '2016-06-06T00:00:00Z')) AND (regKeyIN ['68f45389-0213-441b-b69d-4e35ef94bb20', '4d0bbd74-6bda-4afb-a066-8672c0705da9', '7b85c4ee-f178-462b-8fec-45afd96e1013', 'fd628ded-c16f-4feb-827c-65554dadc64a', '8a9c751a-22f9-46ad-9216-9762de25b3a8', 'cac99a6b-fe9d-40ca-9f48-14cf3adb2a76', 'cb6be796-61ad-4db9-a7ea-28a978e60e04', 'be475136-8820-4bc8-bef7-3175bd1aa701', 'ca95b7c2-644c-489e-810c-319c8e11fbed', '76472b0d-03c3-4319-9257-52c43cfdd209', 'cd58b44e-a6a8-4417-b79e-07503513b65c', 'cb34007f-e02c-4bff-98e4-d5095297be91', '0ba3b04a-2535-4794-ac31-d52782c42189', '84654a97-0f31-4157-a7be-82e96367d56f', '4cc6789f-68a7-4a06-a0d2-8896dcc7a9a5', 'fe9fd951-bed0-44e0-acbd-2226e9857f4c', 'f2f92105-842d-407b-94a2-2232c607afdf', '76894fe7-dee7-47bc-91d0-004f9308d7d2', '1a9375d4-fd31-42a9-ae65-4efafdd37014', 'e63ea199-f3f1-4808-a0c8-82d52befb4d8', 'e3dc990a-49b3-4bae-a3fc-47cccd11c448', 'fd8e9cfd-364f-4abf-925a-00e55bcedee1', '5b54f016-7238-4cdc-8562-4dfb695b458e', '30be5014-e5e5-4eb6-b4a0-0d5b7094da75', '5edcea4b-489b-400c-a62d-aae89816be8a', '92601485-e8f2-42a5-829e-61452fa38bd6', '216fa9fe-fccd-41a5-8588-f9bbf0fe3053', 'bb28a0c6-9710-43db-8a15-63cff0cb7805', '1f9df9b3-9c1e-48e7-bfc3-38416303fc1f', 'cd2a9c2d-d1fa-456e-bf3a-5c279d2eb36e', 'b61314f3-1524-45e0-afa1-b604db523336', 'd5519f14-c9bf-4575-a72e-5eb69bb4886c', 'acb8b317-f456-4b08-a0c0-64304ed1ca30', 'bde1cc47-55a9-4363-884e-7ef661d23718', '8cbecf2c-0d1e-44d6-87fc-7f3aff58dd37', '312a9d75-226f-4fdf-a68e-7f864fbea37d', '74953b97-fa7c-4053-a0ed-5ebc922de005', 'ba06cab5-49b8-42d0-b984-8bdd2006e081', 'e1382b4e-2cdc-4d14-a10c-5a9880188db4', 'a84128e8-e4ef-4c30-b357-8c3fe93e9848', '832ca057-b7e7-4011-80ec-8d08a19de332', 'aa8c0b7e-2c9f-4695-91ab-6190463555fd', '10d62e75-9daa-4e2e-b817-633ea6f91cd4', '1f3e74a5-ba64-42cd-9b47-9e84149a56dd', 'ca75c611-dde3-4d56-b4ec-666dc191d085', '21c36dfc-c5d4-4274-992b-66d5023a33ec', 'f49e61ff-635e-4e12-a305-6b5546ab3bf2', 'dfb70d39-8320-4e9a-91a9-aeed811d6e2c', '276d42bf-38b4-46ff-a767-ea49a675cf5e', '543f050b-9a1c-4056-8863-ea512d280570', '7307517f-5935-4b59-baca-75f90ac76ba5', '017c1b87-4359-4e0d-bad7-ee1bff221d3b', '8ab9cd4f-bbe8-4513-bced-f179e0b7d18e', 'cf501770-8e76-4b62-a67a-f1a1609fef0d', 'adfcb4cf-62d5-4b8d-96c6-fb0abb330e71', '73c0edb8-1833-47a1-9216-3c0d2cf4a183', 'c40cc49e-a005-48e0-b576-442cfdc6cb1a', '5be67e1d-2491-480f-9e4b-448df567c248', '20820b23-1bd6-4805-8633-7ffa9daba5d1', '8294e62c-f10d-4035-b091-559c05292655', 'e3bd281c-4f17-454e-b9f3-5675b5f2e139', '69b487f5-440e-4e3a-a132-5852c7c62d60', 'e91ff335-92eb-4d3b-b379-58b81a757c0d', '2189ed4e-4000-47b8-b66a-5bf8c12eb761', '7dfc2413-c6dc-45a5-99ec-6676e936643f', '814922a0-8fc2-4902-bdc0-8aefe4cabee6', 'dedde284-d76e-417b-a40c-8c3940788a66', 'c557bbfa-2eb4-43cd-a06d-8d3fb2d97770', '48f9c54c-7ae6-49c1-82f9-ba281093e8d9', '151f7788-511e-471c-8fdc-093e613a9737', '0fac727f-c105-4a95-9121-396b2625bca1', '532b3fa0-f298-4d82-aaa6-4615ef667844', 'c7e92cd9-950a-4fb1-8d39-518ae83f5dbf', '100d5c8d-e54b-4b1a-a422-51f86c0f915f', 'c5c92bdd-00a8-4748-a6c2-52b9a4bb4284', 'bd288df5-0437-4b7b-a4b1-5705e4f37809', 'dbdb3432-2dcc-4e1c-8d05-8891aeae45a8', 'dfb174e7-f627-4983-a8c6-8bda67ac7456', '50e4c6b8-75c9-42f3-a2cf-8c63de8c7ae6', '6f50ca58-d7ff-420f-8bc0-969224d576a6', '561093fc-a7f9-4c08-befb-c192ea4f2cd2', 'f2a81de9-bd82-442a-b483-d1e93e083492', '54dafee8-1c9d-4df4-91bc-a1cf0c4fdb49', '136e71af-5db2-425d-968c-0401fcc4e687', '285e6b2e-6150-4fc9-b9af-a214a99b3772', '803c48d4-a9f1-4b5b-bbfc-075c0b321f46', 'b81ee8da-068f-4627-951c-a4009df9bc95', '3b8e9808-412f-45c0-bb2c-3eb83c04052a', 'e918c9f3-1034-45ad-a4c2-aa0f883d33a2', '40224d41-50cd-46c1-8dbd-65a9f70c1700', 'b7ad8a01-5936-46f2-a193-ab6a3f9f2465', '061b2aaa-3539-4522-9648-ad98d412dd27', 'f2443042-0ce2-443d-b402-0052a0407656', 'd27d057c-f792-44e8-9e99-8def3bfeacaf', '79e031ee-dff3-4381-a6ff-99b6565d6cf7', '0b490fb8-d284-4073-bb79-b09975b5205b', 'bab75953-abf0-4fb1-ad33-a3bea1630928', 'cb075665-5954-4110-b4f1-b193c8125b14', '84fc0444-5c18-4898-9d3f-b7e6ae693d6d', '3624c7ba-dd94-4aaf-b1fd-be8bdcb92b0a', 'd30a7c87-df34-4f11-91f0-08e1504b5b14', '3a48ac05-8457-4e5c-aea0-02e78c0cc4bc', '2becf55c-291f-49c2-83d3-208bdb06a83d', '042cb0a5-8888-472b-85dd-09cd25e9eaee', 'eb3b87f4-0b52-44dc-b8f1-0dca178fb2c6', 'fa4cc4e7-3f2b-4591-9115-22a850ab6b05', '7909c506-dcd1-4697-9abf-16a5a7c536ec', '4490bdcc-37cf-4207-9c16-8d36ab13c8eb', '7387d233-b288-4120-a091-37634dac01f4', 'b4ad7e5d-9bcc-4b5f-8867-c9ac9c7e3bfd', 'd3e799af-5378-41c3-b4f0-f335282f8519', '7d22aae9-aaf5-4515-b6d9-f714d2221a11', '7d864205-a45e-46b7-8621-f99f971a55d4', 'dabf337b-2b07-4a93-8383-e2f46708cdd1', '6437f89d-e2c8-42aa-b923-e3f0991379cf', '9695a0c5-7afa-4173-a8dd-e4b2ffb6d12e', 'fe3bb8f0-0f14-450a-8bb3-fc2b5b4d2f96', '915be494-6015-43d8-8a96-e9af6fc76439', 'e3547ada-2a11-4e10-8721-fd900fa6c982', '13c73c7b-96b8-4908-a646-094812fea20e', 'd1147d25-1f16-4285-934a-121362d1d619', '16337b57-b357-49d3-a355-22fb0150cc0b', 'd51029fc-da2a-44d0-89b0-5f8f5a6fa602', 'ac3a73af-2f6a-4671-903f-61e71ba2e07f', '3ba39b56-78c4-43fb-aeb9-1655b9012216', '0c4edfad-86e5-4d3c-8657-33909a1ad092', 'ef6286cb-009c-4df3-9fde-398011833c70', '6b147198-6f57-4b26-b9ee-7858e15fe5a3', 'a1d6162b-2a9d-4563-99d7-7ad62562ebd5', '0c9c1ce2-2c8b-4dc5-b4a9-88e7a098e8a9', '311aa9ca-d949-46aa-a91d-50fa8064f86d', 'f1c80829-5aa0-4177-9ea4-1f09803bf1fd', '9bc4f0cd-1ad5-42ed-bd10-1f18d5fe4035'])" } ], "status": "success", "metrics": { "elapsedTime": "53.24638ms", "executionTime": "53.180667ms", "resultCount": 1, "resultSize": 84643 } }
Second key of Index has (-str_to_millis((pinglog.time))) and query uses str_to_millis((pinglog.time)) . If you create another identical index with index with str_to_millis((pinglog.time)) the str_to_millis((pinglog.time)) will be pushed to indexer and index will produce less keys and solve the timeout issue.
The Span should look
{
“Range”: {
“High”: [
“successor(“fd4d1092-2639-47cd-a174-1c72b5d58f2e”)”
],
“Inclusion”: 0,
“Low”: [
"“fd4d1092-2639-47cd-a174-1c72b5d58f2e”",
“1464385368083”
]
}
}
Thanks, generating the index now. Adding ORDER BY time DESC to the query reduced the number of spans to a single entry, and I did get some results but the scan still timed out.
Partial explain with sort added:
3ba39b56-78c4-43fb-aeb9-1655b9012216', '0c4edfad-86e5-4d3c-8657-33909a1ad092', 'ef6286cb-009c-4df3-9fde-398011833c70', '6b147198-6f57-4b26-b9ee-7858e15fe5a3', 'a1d6162b-2a9d-4563-99d7-7ad62562ebd5', '0c9c1ce2-2c8b-4dc5-b4a9-88e7a098e8a9', '311aa9ca-d949-46aa-a91d-50fa8064f86d', 'f1c80829-5aa0-4177-9ea4-1f09803bf1fd', '9bc4f0cd-1ad5-42ed-bd10-1f18d5fe4035'])
> ORDER BY `time` DESC;
{
"requestID": "8e17fa11-27e5-4ad6-8b5c-4412d75917c0",
"signature": "json",
"results": [
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`pinglog`.`regKey`))",
"cover ((-str_to_millis((`pinglog`.`time`))))",
"cover ((`pinglog`.`time`))",
"cover ((`pinglog`.`type`))",
"cover ((meta(`pinglog`).`id`))"
],
"index": "idx_key_time_type",
"index_id": "97b89bce73253019",
"keyspace": "pinglog",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"fe9fd951-bed0-44e0-acbd-2226e9857f4c\")"
],
"Inclusion": 1,
"Low": [
"\"017c1b87-4359-4e0d-bad7-ee1bff221d3b\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((\"2016-06-03T00:00:00Z\" \u003c= cover ((`pinglog`.`time`))) and (cover ((`pinglog`.`time`)) \u003c \"2016-06-06T00:00:00Z\")) and (cover ((`pinglog`.`regKey`)) in [\"68f45389-0213-441b-b69d-4e35ef94bb20\", \"4d0bbd74-6bda-4afb-a066-8672c0705da9\", \"7b85c4ee-f178-462b-8fec-45afd96e1013\", \"fd628ded-c16f-4feb-827c-65554dadc64a\", \"8a9c751a-22f9-46ad-9216-9762de25b3a8\", \"cac99a6b-fe9d-40ca-9f48-14cf3adb2a76\", \"cb6be796-61ad-4db9-a7ea-28a978e60e04\", \"be475136-8820-4bc8-bef7-3175bd1aa701\", \"ca95b7c2-644c-489e-810c-319c8e11fbed\", \"76472b0d-03c3-4319-9257-52c43cfdd209\", \"cd58b44e-a6a8-4417-b79e-07503513b65c\", \"cb34007f-e02c-4bff-98e4-d5095297be91\", \"0ba3b04a-2535-4794-ac31-d52782c42189\", \"84654a97-0f31-4157-a7be-82e96367d56f\", \"4cc6789f-68a7-4a06-a0d2-8896dcc7a9a5\", \"fe9fd951-bed0-44e0-acbd-2226e9857f4c\", \"f2f92105-842d-407b-94a2-2232c607afdf\", \"76894fe7-dee7-47bc-91d0-004f9308d7d2\", \"1a9375d4-fd31-42a9-ae65-4efafdd37014\", \"e63ea199-f3f1-4808-a0c8-82d52befb4d8\", \"e3dc990a-49b3-4bae-a3fc-47cccd11c448\", \"fd8e9cfd-364f-4abf-925a-00e55bcedee1\", \"5b54f016-7238-4cdc-8562-4dfb695b458e\", \"30be5014-e5e5-4eb6-b4a0-0d5b7094da75\", \"5edcea4b-489b-400c-a62d-aae89816be8a\", \"92601485-e8f2-42a5-829e-61452fa38bd6\", \"216fa9fe-fccd-41a5-8588-f9bbf0fe3053\", \"bb28a0c6-9710-43db-8a15-63cff0cb7805\", \"1f9df9b3-9c1e-48e7-bfc3-38416303fc1f\", \"cd2a9c2d-d1fa-456e-bf3a-5c279d2eb36e\", \"b61314f3-1524-45e0-afa1-b604db523336\", \"d5519f14-c9bf-4575-a72e-5eb69bb4886c\", \"acb8b317-f456-4b08-a0c0-64304ed1ca30\", \"bde1cc47-55a9-4363-884e-7ef661d23718\", \"8cbecf2c-0d1e-44d6-87fc-7f3aff58dd37\", \"312a9d75-226f-4fdf-a68e-7f864fbea37d\", \"74953b97-fa7c-4053-a0ed-5ebc922de005\", \"ba06cab5-49b8-42d0-b984-8bdd2006e081\", \"e1382b4e-2cdc-4d14-a10c-5a9880188db4\", \"a84128e8-e4ef-4c30-b357-8c3fe93e9848\", \"832ca057-b7e7-4011-80ec-8d08a19de332\", \"aa8c0b7e-2c9f-4695-91ab-6190463555fd\", \"10d62e75-9daa-4e2e-b817-633ea6f91cd4\", \"1f3e74a5-ba64-42cd-9b47-9e84149a56dd\", \"ca75c611-dde3-4d56-b4ec-666dc191d085\", \"21c36dfc-c5d4-4274-992b-66d5023a33ec\", \"f49e61ff-635e-4e12-a305-6b5546ab3bf2\", \"dfb70d39-8320-4e9a-91a9-aeed811d6e2c\", \"276d42bf-38b4-46ff-a767-ea49a675cf5e\", \"543f050b-9a1c-4056-8863-ea512d280570\", \"7307517f-5935-4b59-baca-75f90ac76ba5\", \"017c1b87-4359-4e0d-bad7-ee1bff221d3b\", \"8ab9cd4f-bbe8-4513-bced-f179e0b7d18e\", \"cf501770-8e76-4b62-a67a-f1a1609fef0d\", \"adfcb4cf-62d5-4b8d-96c6-fb0abb330e71\", \"73c0edb8-1833-47a1-9216-3c0d2cf4a183\", \"c40cc49e-a005-48e0-b576-442cfdc6cb1a\", \"5be67e1d-2491-480f-9e4b-448df567c248\", \"20820b23-1bd6-4805-8633-7ffa9daba5d1\", \"8294e62c-f10d-4035-b091-559c05292655\", \"e3bd281c-4f17-454e-b9f3-5675b5f2e139\", \"69b487f5-440e-4e3a-a132-5852c7c62d60\", \"e91ff335-92eb-4d3b-b379-58b81a757c0d\", \"2189ed4e-4000-47b8-b66a-5bf8c12eb761\", \"7dfc2413-c6dc-45a5-99ec-6676e936643f\", \"814922a0-8fc2-4902-bdc0-8aefe4cabee6\", \"dedde284-d76e-417b-a40c-8c3940788a66\", \"c557bbfa-2eb4-43cd-a06d-8d3fb2d97770\", \"48f9c54c-7ae6-49c1-82f9-ba281093e8d9\", \"151f7788-511e-471c-8fdc-093e613a9737\", \"0fac727f-c105-4a95-9121-396b2625bca1\", \"532b3fa0-f298-4d82-aaa6-4615ef667844\", \"c7e92cd9-950a-4fb1-8d39-518ae83f5dbf\", \"100d5c8d-e54b-4b1a-a422-51f86c0f915f\", \"c5c92bdd-00a8-4748-a6c2-52b9a4bb4284\", \"bd288df5-0437-4b7b-a4b1-5705e4f37809\", \"dbdb3432-2dcc-4e1c-8d05-8891aeae45a8\", \"dfb174e7-f627-4983-a8c6-8bda67ac7456\", \"50e4c6b8-75c9-42f3-a2cf-8c63de8c7ae6\", \"6f50ca58-d7ff-420f-8bc0-969224d576a6\", \"561093fc-a7f9-4c08-befb-c192ea4f2cd2\", \"f2a81de9-bd82-442a-b483-d1e93e083492\", \"54dafee8-1c9d-4df4-91bc-a1cf0c4fdb49\", \"136e71af-5db2-425d-968c-0401fcc4e687\", \"285e6b2e-6150-4fc9-b9af-a214a99b3772\", \"803c48d4-a9f1-4b5b-bbfc-075c0b321f46\", \"b81ee8da-068f-4627-951c-a4009df9bc95\", \"3b8e9808-412f-45c0-bb2c-3eb83c04052a\", \"e918c9f3-1034-45ad-a4c2-aa0f883d33a2\", \"40224d41-50cd-46c1-8dbd-65a9f70c1700\", \"b7ad8a01-5936-46f2-a193-ab6a3f9f2465\", \"061b2aaa-3539-4522-9648-ad98d412dd27\", \"f2443042-0ce2-443d-b402-0052a0407656\", \"d27d057c-f792-44e8-9e99-8def3bfeacaf\", \"79e031ee-dff3-4381-a6ff-99b6565d6cf7\", \"0b490fb8-d284-4073-bb79-b09975b5205b\", \"bab75953-abf0-4fb1-ad33-a3bea1630928\", \"cb075665-5954-4110-b4f1-b193c8125b14\", \"84fc0444-5c18-4898-9d3f-b7e6ae693d6d\", \"3624c7ba-dd94-4aaf-b1fd-be8bdcb92b0a\", \"d30a7c87-df34-4f11-91f0-08e1504b5b14\", \"3a48ac05-8457-4e5c-aea0-02e78c0cc4bc\", \"2becf55c-291f-49c2-83d3-208bdb06a83d\", \"042cb0a5-8888-472b-85dd-09cd25e9eaee\", \"eb3b87f4-0b52-44dc-b8f1-0dca178fb2c6\", \"fa4cc4e7-3f2b-4591-9115-22a850ab6b05\", \"7909c506-dcd1-4697-9abf-16a5a7c536ec\", \"4490bdcc-37cf-4207-9c16-8d36ab13c8eb\", \"7387d233-b288-4120-a091-37634dac01f4\", \"b4ad7e5d-9bcc-4b5f-8867-c9ac9c7e3bfd\", \"d3e799af-5378-41c3-b4f0-f335282f8519\", \"7d22aae9-aaf5-4515-b6d9-f714d2221a11\", \"7d864205-a45e-46b7-8621-f99f971a55d4\", \"dabf337b-2b07-4a93-8383-e2f46708cdd1\", \"6437f89d-e2c8-42aa-b923-e3f0991379cf\", \"9695a0c5-7afa-4173-a8dd-e4b2ffb6d12e\", \"fe3bb8f0-0f14-450a-8bb3-fc2b5b4d2f96\", \"915be494-6015-43d8-8a96-e9af6fc76439\", \"e3547ada-2a11-4e10-8721-fd900fa6c982\", \"13c73c7b-96b8-4908-a646-094812fea20e\", \"d1147d25-1f16-4285-934a-121362d1d619\", \"16337b57-b357-49d3-a355-22fb0150cc0b\", \"d51029fc-da2a-44d0-89b0-5f8f5a6fa602\", \"ac3a73af-2f6a-4671-903f-61e71ba2e07f\", \"3ba39b56-78c4-43fb-aeb9-1655b9012216\", \"0c4edfad-86e5-4d3c-8657-33909a1ad092\", \"ef6286cb-009c-4df3-9fde-398011833c70\", \"6b147198-6f57-4b26-b9ee-7858e15fe5a3\", \"a1d6162b-2a9d-4563-99d7-7ad62562ebd5\", \"0c9c1ce2-2c8b-4dc5-b4a9-88e7a098e8a9\", \"311aa9ca-d949-46aa-a91d-50fa8064f86d\", \"f1c80829-5aa0-4177-9ea4-1f09803bf1fd\", \"9bc4f0cd-1ad5-42ed-bd10-1f18d5fe4035\"]))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`pinglog`.`regKey`))"
},
{
"expr": "cover ((`pinglog`.`time`))"
},
{
"expr": "cover ((`pinglog`.`type`))"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [
{
"desc": true,
"expr": "cover ((`pinglog`.`time`))"
}
]
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT `regKey`, `time`, `type` FROM `pinglog` WHERE ((`time` \u003e= '2016-06-03T00:00:00Z') AND (`time` \u003c '2016-06-06T00:00:00Z')) AND (`regKey` IN ['68f45389-0213-441b-b69d-4e35ef94bb20', '4d0bbd74-6bda-4afb-a066-8672c0705da9', '7b85c4ee-f178-462b-8fec-45afd96e1013', 'fd628ded-c16f-4feb-827The following will be right index for the latest query.
create index idx_regkey_time_type on pinglog(`regKey`,`time`,`type`);
1 Like