{"action":"created","issue":{"url":"https://api.github.com/repos/graphile/postgraphile/issues/393","repository_url":"https://api.github.com/repos/graphile/postgraphile","labels_url":"https://api.github.com/repos/graphile/postgraphile/issues/393/labels{/name}","comments_url":"https://api.github.com/repos/graphile/postgraphile/issues/393/comments","events_url":"https://api.github.com/repos/graphile/postgraphile/issues/393/events","html_url":"https://github.com/graphile/postgraphile/issues/393","id":214415375,"number":393,"title":"Performance problem: to_json","user":{"login":"danielbuechele","id":685740,"avatar_url":"https://avatars0.githubusercontent.com/u/685740?v=4","gravatar_id":"","url":"https://api.github.com/users/danielbuechele","html_url":"https://github.com/danielbuechele","followers_url":"https://api.github.com/users/danielbuechele/followers","following_url":"https://api.github.com/users/danielbuechele/following{/other_user}","gists_url":"https://api.github.com/users/danielbuechele/gists{/gist_id}","starred_url":"https://api.github.com/users/danielbuechele/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/danielbuechele/subscriptions","organizations_url":"https://api.github.com/users/danielbuechele/orgs","repos_url":"https://api.github.com/users/danielbuechele/repos","events_url":"https://api.github.com/users/danielbuechele/events{/privacy}","received_events_url":"https://api.github.com/users/danielbuechele/received_events","type":"User","site_admin":false},"labels":[{"id":700318902,"url":"https://api.github.com/repos/graphile/postgraphile/labels/Fixed%20in%20v4","name":"Fixed in v4","color":"00bdda","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":14,"created_at":"2017-03-15T14:53:51Z","updated_at":"2018-04-13T07:59:50Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"I am experiencing performance problems with row level security and the `to_json` function. The issue may be related to #380. Here's my setup: I have a table `articles` with about 100k rows, and two roles (`root` and `viewer`). `root` is allowed to query all articles, `viewer` only the ones that are published. Therefore I enabled row level security and added this policy:\r\n```\r\nCREATE POLICY articles_read_viewer ON articles FOR SELECT TO viewer USING ((status = 'PUBLISHED'::status));\r\n```\r\n\r\nWhen querying as `viewer` this request is slower (100ms vs 8ms) than as `root` which is fine. But as soon as `to_json` is added, the response time goes from 100ms to 8 seconds (!).\r\n\r\n**Without to_json:**\r\n```\r\nexplain analyze select * from latest_articles() as a where true offset 0 limit 10;\r\n```\r\n```\r\nLimit  (cost=166838.48..166838.60 rows=10 width=1674) (actual time=92.209..92.214 rows=10 loops=1)\r\n  ->  Sort  (cost=166838.48..167072.71 rows=93694 width=1674) (actual time=92.208..92.213 rows=10 loops=1)\r\n        Sort Key: articles.publication_date DESC\r\n        Sort Method: top-N heapsort  Memory: 45kB\r\n        ->  Seq Scan on articles  (cost=0.00..21734.44 rows=93694 width=1674) (actual time=0.500..71.917 rows=94549 loops=1)\r\n              Filter: ((status = 'PUBLISHED'::status) AND (publication_date < (now())::timestamp without time zone))\r\n              Rows Removed by Filter: 5223\r\nPlanning time: 3.484 ms\r\nExecution time: 92.255 ms\r\n```\r\n\r\n**With to_json:**\r\n```\r\nexplain analyze select to_json(a) as value from latest_articles() as a where true offset 0 limit 10;\r\n```\r\n```\r\nLimit  (cost=166838.48..166838.63 rows=10 width=32) (actual time=7709.402..7709.629 rows=10 loops=1)\r\n  ->  Subquery Scan on __local_0__  (cost=166838.48..168243.89 rows=93694 width=32) (actual time=7709.401..7709.628 rows=10 loops=1)\r\n        ->  Sort  (cost=166838.48..167072.71 rows=93694 width=1674) (actual time=7709.324..7709.326 rows=10 loops=1)\r\n              Sort Key: articles.publication_date DESC\r\n              Sort Method: external merge  Disk: 130496kB\r\n              ->  Seq Scan on articles  (cost=0.00..21734.44 rows=93694 width=1674) (actual time=0.369..77.828 rows=94549 loops=1)\r\n                    Filter: ((status = 'PUBLISHED'::status) AND (publication_date < (now())::timestamp without time zone))\r\n                    Rows Removed by Filter: 5223\r\nPlanning time: 2.762 ms\r\nExecution time: 7796.936 ms\r\n```\r\n\r\nFor the `root`-role, where no policy exists, both queries are performant.\r\n\r\nlatest_articles is a pretty simple function:\r\n```\r\nCREATE FUNCTION latest_articles(before_date timestamp without time zone DEFAULT now()) RETURNS SETOF articles\r\n    LANGUAGE sql STABLE\r\n    AS $$\r\n  select *\r\n  from articles\r\n  WHERE publication_date < before_date\r\n  order by publication_date DESC\r\n$$;\r\n```"},"comment":{"url":"https://api.github.com/repos/graphile/postgraphile/issues/comments/381056424","html_url":"https://github.com/graphile/postgraphile/issues/393#issuecomment-381056424","issue_url":"https://api.github.com/repos/graphile/postgraphile/issues/393","id":381056424,"user":{"login":"benjie","id":129910,"avatar_url":"https://avatars2.githubusercontent.com/u/129910?v=4","gravatar_id":"","url":"https://api.github.com/users/benjie","html_url":"https://github.com/benjie","followers_url":"https://api.github.com/users/benjie/followers","following_url":"https://api.github.com/users/benjie/following{/other_user}","gists_url":"https://api.github.com/users/benjie/gists{/gist_id}","starred_url":"https://api.github.com/users/benjie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/benjie/subscriptions","organizations_url":"https://api.github.com/users/benjie/orgs","repos_url":"https://api.github.com/users/benjie/repos","events_url":"https://api.github.com/users/benjie/events{/privacy}","received_events_url":"https://api.github.com/users/benjie/received_events","type":"User","site_admin":false},"created_at":"2018-04-13T07:59:50Z","updated_at":"2018-04-13T07:59:50Z","author_association":"OWNER","body":"45s?! That extremely unexpected. If you run PostGraphile as a superuser database user does it run faster?\r\n\r\nBtw a more efficient implementation of your function would be:\r\n```sql\r\nCREATE OR REPLACE FUNCTION remove_staff_from_slot(\r\n  id uuid\r\n) RETURNS slot AS $$\r\n\r\n  UPDATE slot SET staff_id = null WHERE slot.id = id returning *;\r\n\r\n$$ language sql volatile;\r\n```"}}
{"actor":{"display_login":"benjie"}}