{"action":"closed","issue":{"url":"https://api.github.com/repos/ArkEcosystem/core/issues/1287","repository_url":"https://api.github.com/repos/ArkEcosystem/core","labels_url":"https://api.github.com/repos/ArkEcosystem/core/issues/1287/labels{/name}","comments_url":"https://api.github.com/repos/ArkEcosystem/core/issues/1287/comments","events_url":"https://api.github.com/repos/ArkEcosystem/core/issues/1287/events","html_url":"https://github.com/ArkEcosystem/core/issues/1287","id":377198916,"node_id":"MDU6SXNzdWUzNzcxOTg5MTY=","number":1287,"title":"core-database-postgres: Some slow queries","user":{"login":"zillionn","id":3462869,"node_id":"MDQ6VXNlcjM0NjI4Njk=","avatar_url":"https://avatars0.githubusercontent.com/u/3462869?v=4","gravatar_id":"","url":"https://api.github.com/users/zillionn","html_url":"https://github.com/zillionn","followers_url":"https://api.github.com/users/zillionn/followers","following_url":"https://api.github.com/users/zillionn/following{/other_user}","gists_url":"https://api.github.com/users/zillionn/gists{/gist_id}","starred_url":"https://api.github.com/users/zillionn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/zillionn/subscriptions","organizations_url":"https://api.github.com/users/zillionn/orgs","repos_url":"https://api.github.com/users/zillionn/repos","events_url":"https://api.github.com/users/zillionn/events{/privacy}","received_events_url":"https://api.github.com/users/zillionn/received_events","type":"User","site_admin":false},"labels":[{"id":878024357,"node_id":"MDU6TGFiZWw4NzgwMjQzNTc=","url":"https://api.github.com/repos/ArkEcosystem/core/labels/backlog","name":"backlog","color":"fbca04","default":false}],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-11-04T21:53:14Z","updated_at":"2018-11-07T08:30:59Z","closed_at":"2018-11-07T08:30:59Z","author_association":"CONTRIBUTOR","body":"I see some slow queries in the postgresql log\r\n\r\n`SELECT COUNT (DISTINCT \"height\") AS \"count\" FROM blocks`\r\n`Time: 488.243 ms`\r\n\r\n`SELECT SUM (\"number_of_transactions\") AS \"numberOfTransactions\", SUM (\"total_fee\") AS \"totalFee\", SUM (\"total_amount\") AS \"totalAmount\", COUNT (DISTINCT \"height\") AS \"count\" FROM blocks`\r\n`Time: 346.399 ms`\r\n\r\nI guess `height.blocks` can't be unique because of the forks, right?\r\n\r\n`SELECT COUNT (DISTINCT \"id\") AS \"count\", SUM (\"fee\") AS \"totalFee\", SUM (\"amount\") AS \"totalAmount\" FROM transactions`\r\n`Time: 602.522 ms`\r\n\r\n`id.transactions` is `PRIMARY KEY`, therefore can't duplicate. The new query should be:\r\n`SELECT COUNT (\"id\") AS \"count\", SUM (\"fee\") AS \"totalFee\", SUM (\"amount\") AS \"totalAmount\" FROM transactions`\r\n`Time: 85.756 ms`"}}
{"actor":{"display_login":"faustbrian"}}