{"action":"opened","number":2062,"pull_request":{"url":"https://api.github.com/repos/Netflix/lemur/pulls/2062","id":229207289,"node_id":"MDExOlB1bGxSZXF1ZXN0MjI5MjA3Mjg5","html_url":"https://github.com/Netflix/lemur/pull/2062","diff_url":"https://github.com/Netflix/lemur/pull/2062.diff","patch_url":"https://github.com/Netflix/lemur/pull/2062.patch","issue_url":"https://api.github.com/repos/Netflix/lemur/issues/2062","number":2062,"state":"open","locked":false,"title":"Optimize certificate filtering by name","user":{"login":"castrapel","id":7538233,"node_id":"MDQ6VXNlcjc1MzgyMzM=","avatar_url":"https://avatars3.githubusercontent.com/u/7538233?v=4","gravatar_id":"","url":"https://api.github.com/users/castrapel","html_url":"https://github.com/castrapel","followers_url":"https://api.github.com/users/castrapel/followers","following_url":"https://api.github.com/users/castrapel/following{/other_user}","gists_url":"https://api.github.com/users/castrapel/gists{/gist_id}","starred_url":"https://api.github.com/users/castrapel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/castrapel/subscriptions","organizations_url":"https://api.github.com/users/castrapel/orgs","repos_url":"https://api.github.com/users/castrapel/repos","events_url":"https://api.github.com/users/castrapel/events{/privacy}","received_events_url":"https://api.github.com/users/castrapel/received_events","type":"User","site_admin":false},"body":"Filtering by certificate name is pretty expensive for us, even after adding gin indexes ( #2037 ). Short queries could take hours to run (essentially never finishing) and peg database CPU. The expensive queries were the filter / count queries. Using explicit joins makes things faster, and the queries don't hang.\r\n\r\nShown below are the old and new count queries. \r\n\r\nPrevious:\r\n`\r\nSELECT count(*) AS count_1\r\n        FROM certificates\r\n        WHERE (certificates.name ILIKE '%a%' OR (EXISTS(SELECT 1\r\n                                                         FROM certificate_associations, domains\r\n                                                         WHERE\r\n                                                           certificates.id = certificate_associations.certificate_id AND\r\n                                                           domains.id = certificate_associations.domain_id AND\r\n                                                           domains.name ILIKE '%a%')) OR certificates.cn ILIKE '%a%')\r\n`\r\nNew:\r\n`\r\nSELECT count(*) AS count_1\r\nFROM certificates\r\n  JOIN certificate_associations ON certificates.id = certificate_associations.certificate_id\r\n  JOIN domains ON domains.id = certificate_associations.domain_id\r\nWHERE (certificates.name ILIKE '%a%' OR ( EXISTS ( SELECT 1\r\n                                                         FROM certificate_associations, domains\r\n                                                         WHERE certificates.id = certificate_associations.certificate_id\r\n                                                                               AND domains.id = certificate_associations\r\n    .domain_id AND domains.name ILIKE '%a%'  )) OR certificates.cn ILIKE '%a%' )\r\n`","created_at":"2018-11-07T23:53:16Z","updated_at":"2018-11-07T23:53:16Z","closed_at":null,"merged_at":null,"merge_commit_sha":null,"assignee":null,"assignees":[],"requested_reviewers":[],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/Netflix/lemur/pulls/2062/commits","review_comments_url":"https://api.github.com/repos/Netflix/lemur/pulls/2062/comments","review_comment_url":"https://api.github.com/repos/Netflix/lemur/pulls/comments{/number}","comments_url":"https://api.github.com/repos/Netflix/lemur/issues/2062/comments","statuses_url":"https://api.github.com/repos/Netflix/lemur/statuses/08a2a2b0e566f4acadedd634c7905dea14393da2","head":{"label":"castrapel:optimize_filter_queries","ref":"optimize_filter_queries","sha":"08a2a2b0e566f4acadedd634c7905dea14393da2","user":{"login":"castrapel","id":7538233,"node_id":"MDQ6VXNlcjc1MzgyMzM=","avatar_url":"https://avatars3.githubusercontent.com/u/7538233?v=4","gravatar_id":"","url":"https://api.github.com/users/castrapel","html_url":"https://github.com/castrapel","followers_url":"https://api.github.com/users/castrapel/followers","following_url":"https://api.github.com/users/castrapel/following{/other_user}","gists_url":"https://api.github.com/users/castrapel/gists{/gist_id}","starred_url":"https://api.github.com/users/castrapel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/castrapel/subscriptions","organizations_url":"https://api.github.com/users/castrapel/orgs","repos_url":"https://api.github.com/users/castrapel/repos","events_url":"https://api.github.com/users/castrapel/events{/privacy}","received_events_url":"https://api.github.com/users/castrapel/received_events","type":"User","site_admin":false},"repo":{"id":117755007,"node_id":"MDEwOlJlcG9zaXRvcnkxMTc3NTUwMDc=","name":"lemur","full_name":"castrapel/lemur","private":false,"owner":{"login":"castrapel","id":7538233,"node_id":"MDQ6VXNlcjc1MzgyMzM=","avatar_url":"https://avatars3.githubusercontent.com/u/7538233?v=4","gravatar_id":"","url":"https://api.github.com/users/castrapel","html_url":"https://github.com/castrapel","followers_url":"https://api.github.com/users/castrapel/followers","following_url":"https://api.github.com/users/castrapel/following{/other_user}","gists_url":"https://api.github.com/users/castrapel/gists{/gist_id}","starred_url":"https://api.github.com/users/castrapel/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/castrapel/subscriptions","organizations_url":"https://api.github.com/users/castrapel/orgs","repos_url":"https://api.github.com/users/castrapel/repos","events_url":"https://api.github.com/users/castrapel/events{/privacy}","received_events_url":"https://api.github.com/users/castrapel/received_events","type":"User","site_admin":false},"html_url":"https://github.com/castrapel/lemur","description":"Repository for the Lemur Certificate Manager","fork":true,"url":"https://api.github.com/repos/castrapel/lemur","forks_url":"https://api.github.com/repos/castrapel/lemur/forks","keys_url":"https://api.github.com/repos/castrapel/lemur/keys{/key_id}","collaborators_url":"https://api.github.com/repos/castrapel/lemur/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/castrapel/lemur/teams","hooks_url":"https://api.github.com/repos/castrapel/lemur/hooks","issue_events_url":"https://api.github.com/repos/castrapel/lemur/issues/events{/number}","events_url":"https://api.github.com/repos/castrapel/lemur/events","assignees_url":"https://api.github.com/repos/castrapel/lemur/assignees{/user}","branches_url":"https://api.github.com/repos/castrapel/lemur/branches{/branch}","tags_url":"https://api.github.com/repos/castrapel/lemur/tags","blobs_url":"https://api.github.com/repos/castrapel/lemur/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/castrapel/lemur/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/castrapel/lemur/git/refs{/sha}","trees_url":"https://api.github.com/repos/castrapel/lemur/git/trees{/sha}","statuses_url":"https://api.github.com/repos/castrapel/lemur/statuses/{sha}","languages_url":"https://api.github.com/repos/castrapel/lemur/languages","stargazers_url":"https://api.github.com/repos/castrapel/lemur/stargazers","contributors_url":"https://api.github.com/repos/castrapel/lemur/contributors","subscribers_url":"https://api.github.com/repos/castrapel/lemur/subscribers","subscription_url":"https://api.github.com/repos/castrapel/lemur/subscription","commits_url":"https://api.github.com/repos/castrapel/lemur/commits{/sha}","git_commits_url":"https://api.github.com/repos/castrapel/lemur/git/commits{/sha}","comments_url":"https://api.github.com/repos/castrapel/lemur/comments{/number}","issue_comment_url":"https://api.github.com/repos/castrapel/lemur/issues/comments{/number}","contents_url":"https://api.github.com/repos/castrapel/lemur/contents/{+path}","compare_url":"https://api.github.com/repos/castrapel/lemur/compare/{base}...{head}","merges_url":"https://api.github.com/repos/castrapel/lemur/merges","archive_url":"https://api.github.com/repos/castrapel/lemur/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/castrapel/lemur/downloads","issues_url":"https://api.github.com/repos/castrapel/lemur/issues{/number}","pulls_url":"https://api.github.com/repos/castrapel/lemur/pulls{/number}","milestones_url":"https://api.github.com/repos/castrapel/lemur/milestones{/number}","notifications_url":"https://api.github.com/repos/castrapel/lemur/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/castrapel/lemur/labels{/name}","releases_url":"https://api.github.com/repos/castrapel/lemur/releases{/id}","deployments_url":"https://api.github.com/repos/castrapel/lemur/deployments","created_at":"2018-01-16T23:21:33Z","updated_at":"2018-07-12T20:20:43Z","pushed_at":"2018-11-07T23:35:45Z","git_url":"git://github.com/castrapel/lemur.git","ssh_url":"git@github.com:castrapel/lemur.git","clone_url":"https://github.com/castrapel/lemur.git","svn_url":"https://github.com/castrapel/lemur","homepage":"","size":2889,"stargazers_count":0,"watchers_count":0,"language":"Python","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"Netflix:master","ref":"master","sha":"a2b22a7d0948190a955478c4f4b71a439c3d28e2","user":{"login":"Netflix","id":913567,"node_id":"MDEyOk9yZ2FuaXphdGlvbjkxMzU2Nw==","avatar_url":"https://avatars3.githubusercontent.com/u/913567?v=4","gravatar_id":"","url":"https://api.github.com/users/Netflix","html_url":"https://github.com/Netflix","followers_url":"https://api.github.com/users/Netflix/followers","following_url":"https://api.github.com/users/Netflix/following{/other_user}","gists_url":"https://api.github.com/users/Netflix/gists{/gist_id}","starred_url":"https://api.github.com/users/Netflix/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Netflix/subscriptions","organizations_url":"https://api.github.com/users/Netflix/orgs","repos_url":"https://api.github.com/users/Netflix/repos","events_url":"https://api.github.com/users/Netflix/events{/privacy}","received_events_url":"https://api.github.com/users/Netflix/received_events","type":"Organization","site_admin":false},"repo":{"id":37542590,"node_id":"MDEwOlJlcG9zaXRvcnkzNzU0MjU5MA==","name":"lemur","full_name":"Netflix/lemur","private":false,"owner":{"login":"Netflix","id":913567,"node_id":"MDEyOk9yZ2FuaXphdGlvbjkxMzU2Nw==","avatar_url":"https://avatars3.githubusercontent.com/u/913567?v=4","gravatar_id":"","url":"https://api.github.com/users/Netflix","html_url":"https://github.com/Netflix","followers_url":"https://api.github.com/users/Netflix/followers","following_url":"https://api.github.com/users/Netflix/following{/other_user}","gists_url":"https://api.github.com/users/Netflix/gists{/gist_id}","starred_url":"https://api.github.com/users/Netflix/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Netflix/subscriptions","organizations_url":"https://api.github.com/users/Netflix/orgs","repos_url":"https://api.github.com/users/Netflix/repos","events_url":"https://api.github.com/users/Netflix/events{/privacy}","received_events_url":"https://api.github.com/users/Netflix/received_events","type":"Organization","site_admin":false},"html_url":"https://github.com/Netflix/lemur","description":"Repository for the Lemur Certificate Manager","fork":false,"url":"https://api.github.com/repos/Netflix/lemur","forks_url":"https://api.github.com/repos/Netflix/lemur/forks","keys_url":"https://api.github.com/repos/Netflix/lemur/keys{/key_id}","collaborators_url":"https://api.github.com/repos/Netflix/lemur/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/Netflix/lemur/teams","hooks_url":"https://api.github.com/repos/Netflix/lemur/hooks","issue_events_url":"https://api.github.com/repos/Netflix/lemur/issues/events{/number}","events_url":"https://api.github.com/repos/Netflix/lemur/events","assignees_url":"https://api.github.com/repos/Netflix/lemur/assignees{/user}","branches_url":"https://api.github.com/repos/Netflix/lemur/branches{/branch}","tags_url":"https://api.github.com/repos/Netflix/lemur/tags","blobs_url":"https://api.github.com/repos/Netflix/lemur/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/Netflix/lemur/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/Netflix/lemur/git/refs{/sha}","trees_url":"https://api.github.com/repos/Netflix/lemur/git/trees{/sha}","statuses_url":"https://api.github.com/repos/Netflix/lemur/statuses/{sha}","languages_url":"https://api.github.com/repos/Netflix/lemur/languages","stargazers_url":"https://api.github.com/repos/Netflix/lemur/stargazers","contributors_url":"https://api.github.com/repos/Netflix/lemur/contributors","subscribers_url":"https://api.github.com/repos/Netflix/lemur/subscribers","subscription_url":"https://api.github.com/repos/Netflix/lemur/subscription","commits_url":"https://api.github.com/repos/Netflix/lemur/commits{/sha}","git_commits_url":"https://api.github.com/repos/Netflix/lemur/git/commits{/sha}","comments_url":"https://api.github.com/repos/Netflix/lemur/comments{/number}","issue_comment_url":"https://api.github.com/repos/Netflix/lemur/issues/comments{/number}","contents_url":"https://api.github.com/repos/Netflix/lemur/contents/{+path}","compare_url":"https://api.github.com/repos/Netflix/lemur/compare/{base}...{head}","merges_url":"https://api.github.com/repos/Netflix/lemur/merges","archive_url":"https://api.github.com/repos/Netflix/lemur/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/Netflix/lemur/downloads","issues_url":"https://api.github.com/repos/Netflix/lemur/issues{/number}","pulls_url":"https://api.github.com/repos/Netflix/lemur/pulls{/number}","milestones_url":"https://api.github.com/repos/Netflix/lemur/milestones{/number}","notifications_url":"https://api.github.com/repos/Netflix/lemur/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/Netflix/lemur/labels{/name}","releases_url":"https://api.github.com/repos/Netflix/lemur/releases{/id}","deployments_url":"https://api.github.com/repos/Netflix/lemur/deployments","created_at":"2015-06-16T16:36:07Z","updated_at":"2018-11-05T23:24:04Z","pushed_at":"2018-11-07T22:51:33Z","git_url":"git://github.com/Netflix/lemur.git","ssh_url":"git@github.com:Netflix/lemur.git","clone_url":"https://github.com/Netflix/lemur.git","svn_url":"https://github.com/Netflix/lemur","homepage":"","size":3686,"stargazers_count":1017,"watchers_count":1017,"language":"Python","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":172,"mirror_url":null,"archived":false,"open_issues_count":71,"license":{"key":"apache-2.0","name":"Apache License 2.0","spdx_id":"Apache-2.0","url":"https://api.github.com/licenses/apache-2.0","node_id":"MDc6TGljZW5zZTI="},"forks":172,"open_issues":71,"watchers":1017,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/Netflix/lemur/pulls/2062"},"html":{"href":"https://github.com/Netflix/lemur/pull/2062"},"issue":{"href":"https://api.github.com/repos/Netflix/lemur/issues/2062"},"comments":{"href":"https://api.github.com/repos/Netflix/lemur/issues/2062/comments"},"review_comments":{"href":"https://api.github.com/repos/Netflix/lemur/pulls/2062/comments"},"review_comment":{"href":"https://api.github.com/repos/Netflix/lemur/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/Netflix/lemur/pulls/2062/commits"},"statuses":{"href":"https://api.github.com/repos/Netflix/lemur/statuses/08a2a2b0e566f4acadedd634c7905dea14393da2"}},"author_association":"CONTRIBUTOR","merged":false,"mergeable":null,"rebaseable":null,"mergeable_state":"unknown","merged_by":null,"comments":0,"review_comments":0,"maintainer_can_modify":true,"commits":1,"additions":6,"deletions":5,"changed_files":2}}
{"actor":{"display_login":"castrapel"}}