{"action":"created","issue":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/3327","labels_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/3327/labels{/name}","comments_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/3327/comments","events_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/3327/events","html_url":"https://github.com/orientechnologies/orientdb/issues/3327","id":53284423,"number":3327,"title":"(Client=55) Indexed query takes 2.2s","user":{"login":"garrett-gottlieb","id":1219054,"avatar_url":"https://avatars.githubusercontent.com/u/1219054?v=3","gravatar_id":"","url":"https://api.github.com/users/garrett-gottlieb","html_url":"https://github.com/garrett-gottlieb","followers_url":"https://api.github.com/users/garrett-gottlieb/followers","following_url":"https://api.github.com/users/garrett-gottlieb/following{/other_user}","gists_url":"https://api.github.com/users/garrett-gottlieb/gists{/gist_id}","starred_url":"https://api.github.com/users/garrett-gottlieb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/garrett-gottlieb/subscriptions","organizations_url":"https://api.github.com/users/garrett-gottlieb/orgs","repos_url":"https://api.github.com/users/garrett-gottlieb/repos","events_url":"https://api.github.com/users/garrett-gottlieb/events{/privacy}","received_events_url":"https://api.github.com/users/garrett-gottlieb/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/orientechnologies/orientdb/labels/question","name":"question","color":"cc317c"}],"state":"closed","locked":false,"assignee":{"login":"luigidellaquila","id":459289,"avatar_url":"https://avatars.githubusercontent.com/u/459289?v=3","gravatar_id":"","url":"https://api.github.com/users/luigidellaquila","html_url":"https://github.com/luigidellaquila","followers_url":"https://api.github.com/users/luigidellaquila/followers","following_url":"https://api.github.com/users/luigidellaquila/following{/other_user}","gists_url":"https://api.github.com/users/luigidellaquila/gists{/gist_id}","starred_url":"https://api.github.com/users/luigidellaquila/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/luigidellaquila/subscriptions","organizations_url":"https://api.github.com/users/luigidellaquila/orgs","repos_url":"https://api.github.com/users/luigidellaquila/repos","events_url":"https://api.github.com/users/luigidellaquila/events{/privacy}","received_events_url":"https://api.github.com/users/luigidellaquila/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/orientechnologies/orientdb/milestones/33","labels_url":"https://api.github.com/repos/orientechnologies/orientdb/milestones/33/labels","id":897335,"number":33,"title":"2.0 Final","description":"2.0 Final version after the 2.0-RC","creator":{"login":"lvca","id":312606,"avatar_url":"https://avatars.githubusercontent.com/u/312606?v=3","gravatar_id":"","url":"https://api.github.com/users/lvca","html_url":"https://github.com/lvca","followers_url":"https://api.github.com/users/lvca/followers","following_url":"https://api.github.com/users/lvca/following{/other_user}","gists_url":"https://api.github.com/users/lvca/gists{/gist_id}","starred_url":"https://api.github.com/users/lvca/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lvca/subscriptions","organizations_url":"https://api.github.com/users/lvca/orgs","repos_url":"https://api.github.com/users/lvca/repos","events_url":"https://api.github.com/users/lvca/events{/privacy}","received_events_url":"https://api.github.com/users/lvca/received_events","type":"User","site_admin":false},"open_issues":28,"closed_issues":12,"state":"open","created_at":"2014-12-09T14:13:06Z","updated_at":"2015-01-15T08:12:10Z","due_on":"2015-01-14T23:00:00Z","closed_at":null},"comments":3,"created_at":"2015-01-02T23:02:42Z","updated_at":"2015-01-15T08:12:10Z","closed_at":"2015-01-15T08:12:10Z","body":"Our main feed query takes 2.7s to complete. We get this query 10M+ times per month, and it is the first thing a user sees. Our entire goal with OrientDB was to make this query faster due to the heavy amount of joins it requires, so let's see how fast we can make it!\r\n\r\nThis query grabs a batch of 10 Posts:\r\n```sql\r\nSELECT\r\n  @rid as @rid,\r\n  @class,\r\n  actionId,\r\n  commentCount,\r\n  facebook,\r\n  featurePostOnDate,\r\n  forcedPrivate,\r\n  instagram,\r\n  isReported,\r\n  likeCount,\r\n  note,\r\n  photo,\r\n  private,\r\n  new,\r\n  popular,\r\n  twitter,\r\n  createdAt,\r\n  updatedAt,\r\n  in_UserWrotePost\r\nFROM indexvaluesdesc:Post.newFeed\r\nWHERE new = true AND actionId IN [1,207,8,1300,2000,2001,2002,2003,2004] AND in_UserWrotePost.size() = 1 AND (private = false OR private IS NULL)\r\nLIMIT 10\r\nFETCHPLAN in_UserWrotePost.out:0 in_UserWrotePost.out.out_UserHasSettings:0 in_UserWrotePost.out.out_UserHasSubscription:0 in_UserWrotePost.out.out_UserHasPromoCode:0 in_UserWrotePost.out.out_UserAddedActivityLog:-2 in_UserWrotePost.out.out_UserAddedWeightLog:-2 in_UserWrotePost.out.out_UserAddedWorkoutLog:-2 in_UserWrotePost.out.out_UserBuiltWorkout:-2 in_UserWrotePost.out.out_UserClaimedPromoCode:-2 in_UserWrotePost.out.out_UserFollowsUser:-2 in_UserWrotePost.out.out_UserHadError:-2 in_UserWrotePost.out.out_UserLikesPost:-2 in_UserWrotePost.out.out_UserSentInvite:-2 in_UserWrotePost.out.out_UserWroteComment:-2 in_UserWrotePost.out.out_UserWrotePost:-2 in_UserWrotePost.out.in_NotificationIsFromUser:-2 in_UserWrotePost.out.in_NotificationIsToUser:-2 in_UserWrotePost.out.in_UserFollowsUser:-2\r\n``` \r\n\r\nHere is the index:\r\n```sql\r\nCREATE INDEX Post.newFeed ON Post (new, actionId, createdAt) NOTUNIQUE\r\n```\r\n\r\nSimply selecting from the index is slow, as described at https://github.com/orientechnologies/orientdb/issues/3312, so I'm looking at any way to make this query faster. Without using the index directly, it is incredibly slow (I cut it off after 30s).\r\n\r\nI'm also worried that query caching is not working, as running the query multiple times yields the same time (2.2s).\r\n\r\nFor some reason, since working on this query with @tglman a week ago, the speed of this query has more than doubled. I'm thinking this may be due to some recent changes, as we are using the latest commit in the `develop` branch.\r\n\r\nThis query speed for Binary Protocol is also dependent on https://github.com/orientechnologies/orientdb/issues/3248 and https://github.com/orientechnologies/orientdb/issues/3263, which are of higher priority, but this is next in line."},"comment":{"url":"https://api.github.com/repos/orientechnologies/orientdb/issues/comments/70052462","html_url":"https://github.com/orientechnologies/orientdb/issues/3327#issuecomment-70052462","issue_url":"https://api.github.com/repos/orientechnologies/orientdb/issues/3327","id":70052462,"user":{"login":"luigidellaquila","id":459289,"avatar_url":"https://avatars.githubusercontent.com/u/459289?v=3","gravatar_id":"","url":"https://api.github.com/users/luigidellaquila","html_url":"https://github.com/luigidellaquila","followers_url":"https://api.github.com/users/luigidellaquila/followers","following_url":"https://api.github.com/users/luigidellaquila/following{/other_user}","gists_url":"https://api.github.com/users/luigidellaquila/gists{/gist_id}","starred_url":"https://api.github.com/users/luigidellaquila/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/luigidellaquila/subscriptions","organizations_url":"https://api.github.com/users/luigidellaquila/orgs","repos_url":"https://api.github.com/users/luigidellaquila/repos","events_url":"https://api.github.com/users/luigidellaquila/events{/privacy}","received_events_url":"https://api.github.com/users/luigidellaquila/received_events","type":"User","site_admin":false},"created_at":"2015-01-15T08:12:10Z","updated_at":"2015-01-15T08:12:10Z","body":"@garrett-gottlieb I had no feedback about this, I'm closing.\r\nPlease re-open (or let's talk about it in chat) if needed"}}