{"action":"opened","issue":{"url":"https://api.github.com/repos/facebook/osquery/issues/1068","labels_url":"https://api.github.com/repos/facebook/osquery/issues/1068/labels{/name}","comments_url":"https://api.github.com/repos/facebook/osquery/issues/1068/comments","events_url":"https://api.github.com/repos/facebook/osquery/issues/1068/events","html_url":"https://github.com/facebook/osquery/issues/1068","id":71760920,"number":1068,"title":"Less than ideal performance on joins in some cases","user":{"login":"wxsBSD","id":1394879,"avatar_url":"https://avatars.githubusercontent.com/u/1394879?v=3","gravatar_id":"","url":"https://api.github.com/users/wxsBSD","html_url":"https://github.com/wxsBSD","followers_url":"https://api.github.com/users/wxsBSD/followers","following_url":"https://api.github.com/users/wxsBSD/following{/other_user}","gists_url":"https://api.github.com/users/wxsBSD/gists{/gist_id}","starred_url":"https://api.github.com/users/wxsBSD/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wxsBSD/subscriptions","organizations_url":"https://api.github.com/users/wxsBSD/orgs","repos_url":"https://api.github.com/users/wxsBSD/repos","events_url":"https://api.github.com/users/wxsBSD/events{/privacy}","received_events_url":"https://api.github.com/users/wxsBSD/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-04-29T02:32:32Z","updated_at":"2015-04-29T02:32:32Z","closed_at":null,"body":"I've discussed this with @theopolis recently but I don't think an issue was ever made so I'm doing it just to make sure it isn't lost.\r\n\r\nWhen issuing a query which does a join on a constraint performance can be sub-optimal in certain cases, depending upon what the table does. The only table I'm currently aware of with this problem is ```yara```, and it is best illustrated by an example.\r\n\r\n```\r\nselect distinct count(*) from processes p, yara y where y.sigfile=\"/tmp/foo.sig\" and y.path=p.path;\r\n```\r\n\r\nThis will use ```/tmp/foo.sig``` and scan the path for every process currently running. Due to the way osquery works, the ```genYara()``` function is called once per path. The ```yara``` table does not keep state between calls (that would be a bad idea) so the net result is that every call causes the signature file to be recompiled which is sub-optimal to say the least.\r\n\r\nIdeally the constraints would be collected up and passed in as a list so that I can compile once and iterate over the list of various constraints."}}