{"action":"created","issue":{"url":"https://api.github.com/repos/coreos/clair/issues/281","repository_url":"https://api.github.com/repos/coreos/clair","labels_url":"https://api.github.com/repos/coreos/clair/issues/281/labels{/name}","comments_url":"https://api.github.com/repos/coreos/clair/issues/281/comments","events_url":"https://api.github.com/repos/coreos/clair/issues/281/events","html_url":"https://github.com/coreos/clair/pull/281","id":193798821,"number":281,"title":"pgsql: Disable hashjoins to get introducing layers for notifications","user":{"login":"Quentin-M","id":1332289,"avatar_url":"https://avatars.githubusercontent.com/u/1332289?v=3","gravatar_id":"","url":"https://api.github.com/users/Quentin-M","html_url":"https://github.com/Quentin-M","followers_url":"https://api.github.com/users/Quentin-M/followers","following_url":"https://api.github.com/users/Quentin-M/following{/other_user}","gists_url":"https://api.github.com/users/Quentin-M/gists{/gist_id}","starred_url":"https://api.github.com/users/Quentin-M/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Quentin-M/subscriptions","organizations_url":"https://api.github.com/users/Quentin-M/orgs","repos_url":"https://api.github.com/users/Quentin-M/repos","events_url":"https://api.github.com/users/Quentin-M/events{/privacy}","received_events_url":"https://api.github.com/users/Quentin-M/received_events","type":"User","site_admin":false},"labels":[{"id":340225001,"url":"https://api.github.com/repos/coreos/clair/labels/area/performance","name":"area/performance","color":"bfe5bf","default":false},{"id":340228667,"url":"https://api.github.com/repos/coreos/clair/labels/component/database","name":"component/database","color":"0052cc","default":false}],"state":"open","locked":false,"assignee":{"login":"jzelinskie","id":343539,"avatar_url":"https://avatars.githubusercontent.com/u/343539?v=3","gravatar_id":"","url":"https://api.github.com/users/jzelinskie","html_url":"https://github.com/jzelinskie","followers_url":"https://api.github.com/users/jzelinskie/followers","following_url":"https://api.github.com/users/jzelinskie/following{/other_user}","gists_url":"https://api.github.com/users/jzelinskie/gists{/gist_id}","starred_url":"https://api.github.com/users/jzelinskie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jzelinskie/subscriptions","organizations_url":"https://api.github.com/users/jzelinskie/orgs","repos_url":"https://api.github.com/users/jzelinskie/repos","events_url":"https://api.github.com/users/jzelinskie/events{/privacy}","received_events_url":"https://api.github.com/users/jzelinskie/received_events","type":"User","site_admin":false},"assignees":[{"login":"jzelinskie","id":343539,"avatar_url":"https://avatars.githubusercontent.com/u/343539?v=3","gravatar_id":"","url":"https://api.github.com/users/jzelinskie","html_url":"https://github.com/jzelinskie","followers_url":"https://api.github.com/users/jzelinskie/followers","following_url":"https://api.github.com/users/jzelinskie/following{/other_user}","gists_url":"https://api.github.com/users/jzelinskie/gists{/gist_id}","starred_url":"https://api.github.com/users/jzelinskie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jzelinskie/subscriptions","organizations_url":"https://api.github.com/users/jzelinskie/orgs","repos_url":"https://api.github.com/users/jzelinskie/repos","events_url":"https://api.github.com/users/jzelinskie/events{/privacy}","received_events_url":"https://api.github.com/users/jzelinskie/received_events","type":"User","site_admin":false}],"milestone":null,"comments":1,"created_at":"2016-12-06T15:17:57Z","updated_at":"2016-12-06T15:33:22Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/coreos/clair/pulls/281","html_url":"https://github.com/coreos/clair/pull/281","diff_url":"https://github.com/coreos/clair/pull/281.diff","patch_url":"https://github.com/coreos/clair/pull/281.patch"},"body":"```\r\n\t// Create a transaction to disable hash joins as our experience shows that\r\n\t// PostgreSQL plans in certain cases a sequential scan and a hash on\r\n\t// Layer_diff_FeatureVersion for the condition `ldfv.layer_id >= $2 AND\r\n\t// ldfv.modification = 'add'` before realizing a hash inner join with\r\n\t// Vulnerability_Affects_FeatureVersion. By disabling explictly hash joins,\r\n\t// we force PostgreSQL to perform a bitmap index scan with\r\n\t// `ldfv.featureversion_id = fv.id` on Layer_diff_FeatureVersion, followed by\r\n\t// a bitmap heap scan on `ldfv.layer_id >= $2 AND ldfv.modification = 'add'`,\r\n\t// thus avoiding a sequential scan on the biggest database table and\r\n\t// allowing a small nested loop join instead.\r\n```\r\n\r\nThis commit reduces the execution time of `searchNotificationLayerIntroducingVulnerability` with certain parameter sets, that took 4-5min as noticed this morning to milliseconds. Let's experiment and verify it doesn't make the general case worse."},"comment":{"url":"https://api.github.com/repos/coreos/clair/issues/comments/265180577","html_url":"https://github.com/coreos/clair/pull/281#issuecomment-265180577","issue_url":"https://api.github.com/repos/coreos/clair/issues/281","id":265180577,"user":{"login":"Quentin-M","id":1332289,"avatar_url":"https://avatars.githubusercontent.com/u/1332289?v=3","gravatar_id":"","url":"https://api.github.com/users/Quentin-M","html_url":"https://github.com/Quentin-M","followers_url":"https://api.github.com/users/Quentin-M/followers","following_url":"https://api.github.com/users/Quentin-M/following{/other_user}","gists_url":"https://api.github.com/users/Quentin-M/gists{/gist_id}","starred_url":"https://api.github.com/users/Quentin-M/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Quentin-M/subscriptions","organizations_url":"https://api.github.com/users/Quentin-M/orgs","repos_url":"https://api.github.com/users/Quentin-M/repos","events_url":"https://api.github.com/users/Quentin-M/events{/privacy}","received_events_url":"https://api.github.com/users/Quentin-M/received_events","type":"User","site_admin":false},"created_at":"2016-12-06T15:33:22Z","updated_at":"2016-12-06T15:33:22Z","body":"**Before**\r\n\r\n```\r\nSELECT current_setting('enable_hashjoin');\r\n+-------------------+\r\n| current_setting   |\r\n|-------------------|\r\n| on                |\r\n+-------------------+\r\n\r\n+-----------------------------------------------------------------------------------------------------------------------------------------\r\n| QUERY PLAN\r\n|-----------------------------------------------------------------------------------------------------------------------------------------\r\n| Limit  (cost=3584098.82..3589355.86 rows=1000 width=92) (actual time=396725.947..397293.267 rows=1000 loops=1)\r\n|   CTE ldfv\r\n|     ->  Sort  (cost=3583581.36..3584098.38 rows=206808 width=8) (actual time=396725.910..396727.252 rows=1000 loops=1)\r\n|           Sort Key: ldfv_1.id\r\n|           Sort Method: quicksort  Memory: 279kB\r\n|           ->  Hash Join  (cost=3054514.44..3562494.35 rows=206808 width=8) (actual time=323790.647..396721.617 rows=3903 loops=1)\r\n|                 Hash Cond: (vafv.featureversion_id = ldfv_1.featureversion_id)\r\n|                 ->  Nested Loop  (cost=0.72..1989.25 rows=229 width=8) (actual time=4.201..5.873 rows=97 loops=1)\r\n|                       ->  Index Only Scan using vulnerability_affects_feature_vulnerability_id_featureversi_key on vulnerability_affects\r\n|                             Index Cond: (vulnerability_id = 1138617)\r\n|                             Heap Fetches: 97\r\n|                       ->  Index Only Scan using featureversion_pkey on featureversion fv  (cost=0.29..6.41 rows=1 width=4) (actual time=\r\n|                             Index Cond: (id = vafv.featureversion_id)\r\n|                             Heap Fetches: 97\r\n|                 ->  Hash  (cost=1656990.09..1656990.09 rows=80396850 width=12) (actual time=323765.432..323765.432 rows=80336226 loops=1\r\n|                       Buckets: 131072 (originally 131072)  Batches: 65536 (originally 2048)  Memory Usage: 11670kB\r\n|                       ->  Seq Scan on layer_diff_featureversion ldfv_1  (cost=0.00..1656990.09 rows=80396850 width=12) (actual time=0.06\r\n|                             Filter: ((layer_id >= 0) AND (modification = 'add'::modification))\r\n|                             Rows Removed by Filter: 741791\r\n|   ->  Nested Loop  (cost=0.44..1087197.88 rows=206808 width=92) (actual time=396725.944..397291.538 rows=1000 loops=1)\r\n|         ->  CTE Scan on ldfv  (cost=0.00..4136.16 rows=206808 width=4) (actual time=396725.915..396729.219 rows=1000 loops=1)\r\n|         ->  Index Scan using layer_pkey on layer l  (cost=0.44..5.23 rows=1 width=92) (actual time=0.557..0.558 rows=1 loops=1000)\r\n|               Index Cond: (id = ldfv.layer_id)\r\n| Planning time: 8.458 ms\r\n| Execution time: 397296.731 ms\r\n+-----------------------------------------------------------------------------------------------------------------------------------------\r\n```\r\n\r\n**After**\r\n\r\n```\r\n+-----------------------------------------------------------------------------------------------------------------------------------------\r\n| QUERY PLAN\r\n|-----------------------------------------------------------------------------------------------------------------------------------------\r\n| Limit  (cost=3960906.47..3966163.54 rows=1000 width=92) (actual time=75.168..89.405 rows=1000 loops=1)\r\n|   CTE ldfv\r\n|     ->  Sort  (cost=3960388.98..3960906.03 rows=206819 width=8) (actual time=75.150..76.229 rows=1000 loops=1)\r\n|           Sort Key: ldfv_1.id\r\n|           Sort Method: quicksort  Memory: 279kB\r\n|           ->  Nested Loop  (cost=171.25..3939300.92 rows=206819 width=8) (actual time=0.110..70.779 rows=3903 loops=1)\r\n|                 Join Filter: (vafv.featureversion_id = ldfv_1.featureversion_id)\r\n|                 ->  Nested Loop  (cost=0.72..1989.25 rows=229 width=8) (actual time=0.022..1.085 rows=97 loops=1)\r\n|                       ->  Index Only Scan using vulnerability_affects_feature_vulnerability_id_featureversi_key on vulnerability_affects\r\n|                             Index Cond: (vulnerability_id = 1138617)\r\n|                             Heap Fetches: 97\r\n|                       ->  Index Only Scan using featureversion_pkey on featureversion fv  (cost=0.29..6.41 rows=1 width=4) (actual time=\r\n|                             Index Cond: (id = vafv.featureversion_id)\r\n|                             Heap Fetches: 97\r\n|                 ->  Bitmap Heap Scan on layer_diff_featureversion ldfv_1  (cost=170.53..17008.51 rows=14799 width=12) (actual time=0.562\r\n|                       Recheck Cond: (featureversion_id = fv.id)\r\n|                       Filter: ((layer_id >= 0) AND (modification = 'add'::modification))\r\n|                       Rows Removed by Filter: 5\r\n|                       Heap Blocks: exact=4219\r\n|                       ->  Bitmap Index Scan on layer_diff_featureversion_featureversion_id_idx  (cost=0.00..166.83 rows=14947 width=0) (\r\n|                             Index Cond: (featureversion_id = fv.id)\r\n|   ->  Nested Loop  (cost=0.44..1087263.21 rows=206819 width=92) (actual time=75.165..87.677 rows=1000 loops=1)\r\n|         ->  CTE Scan on ldfv  (cost=0.00..4136.38 rows=206819 width=4) (actual time=75.154..78.190 rows=1000 loops=1)\r\n|         ->  Index Scan using layer_pkey on layer l  (cost=0.44..5.23 rows=1 width=92) (actual time=0.005..0.006 rows=1 loops=1000)\r\n|               Index Cond: (id = ldfv.layer_id)\r\n| Planning time: 0.887 ms\r\n| Execution time: 90.395 ms\r\n+-----------------------------------------------------------------------------------------------------------------------------------------\r\n```"}}
{"actor":{"display_login":"Quentin-M"}}