{"action":"closed","issue":{"url":"https://api.github.com/repos/G-Node/GCA-Web/issues/194","labels_url":"https://api.github.com/repos/G-Node/GCA-Web/issues/194/labels{/name}","comments_url":"https://api.github.com/repos/G-Node/GCA-Web/issues/194/comments","events_url":"https://api.github.com/repos/G-Node/GCA-Web/issues/194/events","html_url":"https://github.com/G-Node/GCA-Web/issues/194","id":33656762,"number":194,"title":"controllers.api.Abstracts.listAllByConference creates slow query","user":{"login":"gicmo","id":2040,"avatar_url":"https://avatars.githubusercontent.com/u/2040?v=3","gravatar_id":"","url":"https://api.github.com/users/gicmo","html_url":"https://github.com/gicmo","followers_url":"https://api.github.com/users/gicmo/followers","following_url":"https://api.github.com/users/gicmo/following{/other_user}","gists_url":"https://api.github.com/users/gicmo/gists{/gist_id}","starred_url":"https://api.github.com/users/gicmo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gicmo/subscriptions","organizations_url":"https://api.github.com/users/gicmo/orgs","repos_url":"https://api.github.com/users/gicmo/repos","events_url":"https://api.github.com/users/gicmo/events{/privacy}","received_events_url":"https://api.github.com/users/gicmo/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/G-Node/GCA-Web/labels/cleanup","name":"cleanup","color":"c7def8"},{"url":"https://api.github.com/repos/G-Node/GCA-Web/labels/Performance","name":"Performance","color":"ff1d21"}],"state":"closed","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/G-Node/GCA-Web/milestones/1","labels_url":"https://api.github.com/repos/G-Node/GCA-Web/milestones/1/labels","id":934725,"number":1,"title":"BCCN15 Cleanup","description":"Code cleanup and migration to current play version","creator":{"login":"stoewer","id":2076136,"avatar_url":"https://avatars.githubusercontent.com/u/2076136?v=3","gravatar_id":"","url":"https://api.github.com/users/stoewer","html_url":"https://github.com/stoewer","followers_url":"https://api.github.com/users/stoewer/followers","following_url":"https://api.github.com/users/stoewer/following{/other_user}","gists_url":"https://api.github.com/users/stoewer/gists{/gist_id}","starred_url":"https://api.github.com/users/stoewer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/stoewer/subscriptions","organizations_url":"https://api.github.com/users/stoewer/orgs","repos_url":"https://api.github.com/users/stoewer/repos","events_url":"https://api.github.com/users/stoewer/events{/privacy}","received_events_url":"https://api.github.com/users/stoewer/received_events","type":"User","site_admin":false},"open_issues":6,"closed_issues":8,"state":"open","created_at":"2015-01-14T08:23:13Z","updated_at":"2015-02-03T12:53:02Z","due_on":"2015-01-21T23:00:00Z","closed_at":null},"comments":1,"created_at":"2014-05-16T08:38:59Z","updated_at":"2015-02-03T12:53:02Z","closed_at":"2015-02-03T12:53:02Z","body":"Culprit most likely is `AbstractService::listAll`.\r\n\r\n```SQL\r\nSELECT DISTINCT t1.UUID, t1.ACKNOWLEDGEMENTS, t1.CONFLICTOFINTEREST, t1.DOI, t1.ISTALK, t1.MTIME, t1.REASONFORTALK,\r\nt1.SORTID, t1.STATE, t1.TEXT, t1.TITLE, t1.TOPIC, t1.CONFERENCE_UUID, t0.UUID, t0.AUTHENTICATIONMETHOD, t0.AVATAR,\r\nt0.FIRSTNAME, t0.FULLNAME, t0.LASTNAME, t0.MAIL, t0.PROVIDER, t0.USERID, t0.SECRET, t0.TOKEN, t0.ACCESSTOKEN,\r\nt0.EXPIRESIN, t0.REFRESHTOKEN, t0.TOKENTYPE, t0.HASHER, t0.PASSWORD, t0.SALT, t2.UUID, t2.FIRSTNAME, t2.LASTNAME,\r\nt2.MAIL, t2.MIDDLENAME, t2.POSITION, t2.ABSTR_UUID, t3.UUID, t3.ADDRESS, t3.COUNTRY, t3.DEPARTMENT, t3.POSITION,\r\nt3.SECTION, t3.ABSTR_UUID, t4.UUID, t4.CITE, t4.DEADLINE, t4.DESCRIPTION, t4.ENDDATE, t4.ISOPEN, t4.ISPUBLISHED,\r\nt4.LINK, t4.LOGO, t4.NAME, t4.SHORT, t4.STARTDATE, t4.THUMBNAIL, t5.UUID, t5.CAPTION, t5.POSITION, t5.ABSTR_UUID,\r\nt6.UUID, t6.DOI, t6.LINK, t6.POSITION, t6.TEXT, t6.ABSTR_UUID FROM ABSTRACT t1 LEFT OUTER JOIN CONFERENCE t4 ON (t4.UUID\r\n= t1.CONFERENCE_UUID) LEFT OUTER JOIN (abstract_owners t7 JOIN ACCOUNT t0 ON (t0.UUID = t7.owners_UUID)) ON\r\n(t7.abstracts_UUID = t1.UUID) LEFT OUTER JOIN AUTHOR t2 ON (t2.ABSTR_UUID = t1.UUID) LEFT OUTER JOIN AFFILIATION t3 ON\r\n(t3.ABSTR_UUID = t1.UUID) LEFT OUTER JOIN FIGURE t5 ON (t5.ABSTR_UUID = t1.UUID) LEFT OUTER JOIN REFERENCE t6 ON\r\n(t6.ABSTR_UUID = t1.UUID) WHERE (t4.UUID = 'ba604a3c-e95a-4569-84a9-7d4dedd08310') ORDER BY t1.SORTID, t1.TITLE \r\n```\r\n\r\n```\r\n--- Query Plan ---\r\n Unique  (cost=112.87..211.54 rows=572 width=10865) (actual time=3179.676..3185.441 rows=1245 loops=1)\r\n   ->  Sort  (cost=112.87..114.30 rows=572 width=10865) (actual time=3179.666..3180.184 rows=1245 loops=1)\r\n         Sort Key: t1.sortid, t1.title, t1.uuid, t1.acknowledgements, t1.conflictofinterest, t1.doi, t1.istalk, t1.mtime, t1.reasonfortalk, t1.state, t1.text, t1.topic, t0.uuid, t0.authenticationmethod, t0.avatar, t0.firstname, t0.fullname, t0.lastname, t0.mail, t0.provider, t0.userid, t0.secret, t0.token, t0.accesstoken, t0.expiresin, t0.refreshtoken, t0.tokentype, t0.hasher, t0.password, t0.salt, t2.uuid, t2.firstname, t2.lastname, t2.mail, t2.middlename, t2.\"position\", t2.abstr_uuid, t3.uuid, t3.address, t3.country, t3.department, t3.\"position\", t3.section, t3.abstr_uuid, t4.cite, t4.deadline, t4.description, t4.enddate, t4.isopen, t4.ispublished, t4.link, t4.logo, t4.name, t4.short, t4.startdate, t4.thumbnail, t5.uuid, t5.caption, t5.\"position\", t5.abstr_uuid, t6.uuid, t6.doi, t6.link, t6.\"position\", t6.text, t6.abstr_uuid\r\n         Sort Method: quicksort  Memory: 4852kB\r\n         ->  Hash Left Join  (cost=69.53..86.68 rows=572 width=10865) (actual time=3.207..5.592 rows=1245 loops=1)\r\n               Hash Cond: ((t1.uuid)::text = (t2.abstr_uuid)::text)\r\n               ->  Hash Right Join  (cost=61.88..70.34 rows=237 width=10746) (actual time=2.943..3.886 rows=382 loops=1)\r\n                     Hash Cond: ((t3.abstr_uuid)::text = (t1.uuid)::text)\r\n                     ->  Seq Scan on affiliation t3  (cost=0.00..5.39 rows=139 width=168) (actual time=0.012..0.108 rows=123 loops=1)\r\n                     ->  Hash  (cost=60.46..60.46 rows=114 width=10578) (actual time=2.900..2.900 rows=176 loops=1)\r\n                           Buckets: 1024  Batches: 1  Memory Usage: 403kB\r\n                           ->  Hash Right Join  (cost=51.75..60.46 rows=114 width=10578) (actual time=1.387..1.847 rows=176 loops=1)\r\n                                 Hash Cond: ((t6.abstr_uuid)::text = (t1.uuid)::text)\r\n                                 ->  Seq Scan on reference t6  (cost=0.00..7.14 rows=114 width=279) (actual time=0.009..0.095 rows=130 loops=1)\r\n                                 ->  Hash  (cost=50.91..50.91 rows=67 width=10299) (actual time=1.352..1.352 rows=70 loops=1)\r\n                                       Buckets: 1024  Batches: 1  Memory Usage: 134kB\r\n                                       ->  Hash Left Join  (cost=12.94..50.91 rows=67 width=10299) (actual time=0.518..0.953 rows=70 loops=1)\r\n                                             Hash Cond: ((t1.uuid)::text = (t5.abstr_uuid)::text)\r\n                                             ->  Nested Loop  (cost=11.47..48.90 rows=67 width=10116) (actual time=0.465..0.804 rows=66 loops=1)\r\n                                                   ->  Seq Scan on conference t4  (cost=0.00..1.01 rows=1 width=4154) (actual time=0.021..0.022 rows=1 loops=1)\r\n                                                         Filter: ((uuid)::text = 'ba604a3c-e95a-4569-84a9-7d4dedd08310'::text)\r\n                                                   ->  Hash Left Join  (cost=11.47..47.21 rows=67 width=5962) (actual time=0.435..0.728 rows=66 loops=1)\r\n                                                         Hash Cond: ((t1.uuid)::text = (t7.abstracts_uuid)::text)\r\n                                                         ->  Seq Scan on abstract t1  (cost=0.00..34.84 rows=67 width=2587) (actual time=0.012..0.216 rows=63 loops=1)\r\n                                                               Filter: ((conference_uuid)::text = 'ba604a3c-e95a-4569-84a9-7d4dedd08310'::text)\r\n                                                         ->  Hash  (cost=10.76..10.76 rows=57 width=3412) (actual time=0.403..0.403 rows=66 loops=1)\r\n                                                               Buckets: 1024  Batches: 1  Memory Usage: 19kB\r\n                                                               ->  Hash Join  (cost=2.28..10.76 rows=57 width=3412) (actual time=0.104..0.303 rows=66 loops=1)\r\n                                                                     Hash Cond: ((t0.uuid)::text = (t7.owners_uuid)::text)\r\n                                                                     ->  Seq Scan on account t0  (cost=0.00..6.55 rows=155 width=3375) (actual time=0.007..0.069 rows=138 loops=1)\r\n                                                                     ->  Hash  (cost=1.57..1.57 rows=57 width=74) (actual time=0.075..0.075 rows=66 loops=1)\r\n                                                                           Buckets: 1024  Batches: 1  Memory Usage: 7kB\r\n                                                                           ->  Seq Scan on abstract_owners t7  (cost=0.00..1.57 rows=57 width=74) (actual time=0.013..0.033 rows=66 loops=1)\r\n                                             ->  Hash  (cost=1.21..1.21 rows=21 width=183) (actual time=0.039..0.039 rows=22 loops=1)\r\n                                                   Buckets: 1024  Batches: 1  Memory Usage: 5kB\r\n                                                   ->  Seq Scan on figure t5  (cost=0.00..1.21 rows=21 width=183) (actual time=0.010..0.016 rows=22 loops=1)\r\n               ->  Hash  (cost=5.62..5.62 rows=162 width=119) (actual time=0.241..0.241 rows=166 loops=1)\r\n                     Buckets: 1024  Batches: 1  Memory Usage: 25kB\r\n                     ->  Seq Scan on author t2  (cost=0.00..5.62 rows=162 width=119) (actual time=0.013..0.074 rows=166 loops=1)\r\n Total runtime: 3187.009 ms\r\n(40 rows)\r\n```"}}