{"action":"created","issue":{"url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/5790","repository_url":"https://api.github.com/repos/rethinkdb/rethinkdb","labels_url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/5790/labels{/name}","comments_url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/5790/comments","events_url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/5790/events","html_url":"https://github.com/rethinkdb/rethinkdb/issues/5790","id":156170033,"number":5790,"title":"secondary index isn't used","user":{"login":"santo74","id":7080464,"avatar_url":"https://avatars.githubusercontent.com/u/7080464?v=3","gravatar_id":"","url":"https://api.github.com/users/santo74","html_url":"https://github.com/santo74","followers_url":"https://api.github.com/users/santo74/followers","following_url":"https://api.github.com/users/santo74/following{/other_user}","gists_url":"https://api.github.com/users/santo74/gists{/gist_id}","starred_url":"https://api.github.com/users/santo74/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/santo74/subscriptions","organizations_url":"https://api.github.com/users/santo74/orgs","repos_url":"https://api.github.com/users/santo74/repos","events_url":"https://api.github.com/users/santo74/events{/privacy}","received_events_url":"https://api.github.com/users/santo74/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":6,"created_at":"2016-05-22T20:07:50Z","updated_at":"2016-05-24T21:46:05Z","closed_at":null,"body":"Using a secondary index in my query doesn't have any effect.\r\n\r\n// Retrieve all posts and merge userInfo from users table, using no indexes\r\n```\r\nr.db('example').table('posts')\r\n  .merge(function(doc) {\r\n    return {\r\n      userInfo: r.db('example').table('users').filter({key: doc('userKey')}).map(function(user) {\r\n                return {\r\n                    firstname: user('firstname'),\r\n                    lastname: user('lastname')\r\n                };\r\n            }).coerceTo('array').nth(0)\r\n  }\r\n})\r\n```\r\n\r\n```\r\n27 rows returned\r\n\r\n2.39s round-trip time --- 1.56s server time --- 40 shard accesses\r\n```\r\n\r\n// Create secondary index and wait for it to be ready\r\n```\r\nr.db('example').table('users').indexWait('key')\r\n```\r\n\r\n```\r\n[\r\n\r\n    {\r\n        \"function\": <binary, 90 bytes, \"24 72 65 71 6c 5f...\"> ,\r\n        \"geo\": false ,\r\n        \"index\": \"key\" ,\r\n        \"multi\": false ,\r\n        \"outdated\": false ,\r\n        \"query\": \"indexCreate('key', function(_var19) { return _var19.getField(\"key\"); })\" ,\r\n        \"ready\": true\r\n    }\r\n\r\n]\r\n```\r\n\r\n// Retrieve all posts and merge userInfo from users table, using secondary index on 'key'\r\n```\r\nr.db('example').table('posts')\r\n  .merge(function(doc) {\r\n    return {\r\n      userInfo: r.db('example').table('users').getAll(doc('userKey'), {index: 'key'}).map(function(user) {\r\n                return {\r\n                    firstname: user('firstname'),\r\n                    lastname: user('lastname')\r\n                };\r\n            }).coerceTo('array').nth(0)\r\n  }\r\n})\r\n```\r\n\r\n```\r\n27 rows returned\r\n\r\n2.29s round-trip time --- 1.69s server time --- 40 shard accesses\r\n```\r\n\r\n```\r\n[\r\n  {\r\n    \"description\": \"Evaluating merge.\",\r\n    \"duration(ms)\": 153.670535,\r\n    \"sub_tasks\": [\r\n      {\r\n        \"description\": \"Evaluating table.\",\r\n        \"duration(ms)\": 152.835981,\r\n        \"sub_tasks\": [\r\n          {\r\n            \"description\": \"Evaluating db.\",\r\n            \"duration(ms)\": 0.146117,\r\n            \"sub_tasks\": [\r\n              {\r\n                \"description\": \"Evaluating datum.\",\r\n                \"duration(ms)\": 0.014949,\r\n                \"sub_tasks\": []\r\n              }\r\n            ]\r\n          },\r\n          {\r\n            \"description\": \"Evaluating datum.\",\r\n            \"duration(ms)\": 0.012681,\r\n            \"sub_tasks\": []\r\n          }\r\n        ]\r\n      }\r\n    ]\r\n  },\r\n  {\r\n    \"description\": \"Perform read.\",\r\n    \"duration(ms)\": 1531.745019,\r\n    \"sub_tasks\": [\r\n      {\r\n        \"parallel_tasks\": [\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 123.968755,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 123.959097,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.017079,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.013429,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 240.336271,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 240.331794,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.013805,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.010524,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.026217,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.019038,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.015885,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.012107,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.012278,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.009261,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 154.223709,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 154.213956,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.029514,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.022162,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.02251,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.015362,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 367.176434,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 367.167564,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 657.288589,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 657.279892,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 664.513634,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 664.501419,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.032581,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.024939,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 657.936123,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 657.92508,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.046335,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.034251,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1232.008147,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1231.938339,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.071341,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.038578,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.146596,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.141462,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.102776,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.084646,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1340.872127,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1340.862116,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1354.677564,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1354.620232,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1359.95545,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1359.885705,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.08567,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.075582,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 504.390079,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 504.379888,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.014304,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.010211,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 535.790611,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 535.780026,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.043525,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.033701,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.012488,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.009438,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 500.189306,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 500.177138,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 520.775068,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 520.770674,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 554.393869,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 554.387848,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1127.406309,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1127.390346,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.22929,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.170289,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.180022,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.172577,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.063534,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.042118,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.134371,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.106315,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1116.184038,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1116.171142,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 0.131825,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 0.090173,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ],\r\n          [\r\n            {\r\n              \"description\": \"Perform read on shard.\",\r\n              \"duration(ms)\": 1127.049845,\r\n              \"sub_tasks\": [\r\n                {\r\n                  \"description\": \"Do range scan on primary index.\",\r\n                  \"duration(ms)\": 1126.877067,\r\n                  \"sub_tasks\": []\r\n                }\r\n              ]\r\n            }\r\n          ]\r\n        ]\r\n      }\r\n    ]\r\n  }\r\n]\r\n```\r\n\r\n// Retrieve all posts and merge userInfo from users table, using primary index\r\n```\r\nr.db('example').table('posts')\r\n  .merge(function(doc) {\r\n    return {\r\n      userInfo: r.db('example').table('users').getAll(doc('userId')).map(function(user) {\r\n                return {\r\n                    firstname: user('firstname'),\r\n                    lastname: user('lastname')\r\n                };\r\n            }).coerceTo('array').nth(0)\r\n  }\r\n})\r\n```\r\n\r\n```\r\n27 rows returned\r\n\r\n567ms round-trip time --- 141ms server time --- 40 shard accesses\r\n```\r\n\r\nAs you can see, using the secondary index is even slightly slower than using no index at all.\r\nUsing primary index is much faster, as expected.\r\nShouldn't the use of the secondary index approximate the use of a primary index?\r\nAlso the output of the query profiler doesn't mention any usage of the secondary index at all."},"comment":{"url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/comments/221411341","html_url":"https://github.com/rethinkdb/rethinkdb/issues/5790#issuecomment-221411341","issue_url":"https://api.github.com/repos/rethinkdb/rethinkdb/issues/5790","id":221411341,"user":{"login":"danielmewes","id":505365,"avatar_url":"https://avatars.githubusercontent.com/u/505365?v=3","gravatar_id":"","url":"https://api.github.com/users/danielmewes","html_url":"https://github.com/danielmewes","followers_url":"https://api.github.com/users/danielmewes/followers","following_url":"https://api.github.com/users/danielmewes/following{/other_user}","gists_url":"https://api.github.com/users/danielmewes/gists{/gist_id}","starred_url":"https://api.github.com/users/danielmewes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/danielmewes/subscriptions","organizations_url":"https://api.github.com/users/danielmewes/orgs","repos_url":"https://api.github.com/users/danielmewes/repos","events_url":"https://api.github.com/users/danielmewes/events{/privacy}","received_events_url":"https://api.github.com/users/danielmewes/received_events","type":"User","site_admin":false},"created_at":"2016-05-24T21:46:05Z","updated_at":"2016-05-24T21:46:05Z","body":"`getAll` on a primary key is more efficient because it can route only to the shard that contains the requested key (because the shard boundaries are based on primary key ranges). secondary-index based `getAll` requests must route to all shards. Again, if the table becomes large, both have `O(log n)` performance. But the constant overhead is different."}}
{"actor":{"display_login":"danielmewes"}}