{"action":"created","issue":{"url":"https://api.github.com/repos/begriffs/postgrest/issues/273","labels_url":"https://api.github.com/repos/begriffs/postgrest/issues/273/labels{/name}","comments_url":"https://api.github.com/repos/begriffs/postgrest/issues/273/comments","events_url":"https://api.github.com/repos/begriffs/postgrest/issues/273/events","html_url":"https://github.com/begriffs/postgrest/issues/273","id":104001485,"number":273,"title":"Get rid of count(1) by default","user":{"login":"NikolayS","id":1345402,"avatar_url":"https://avatars.githubusercontent.com/u/1345402?v=3","gravatar_id":"","url":"https://api.github.com/users/NikolayS","html_url":"https://github.com/NikolayS","followers_url":"https://api.github.com/users/NikolayS/followers","following_url":"https://api.github.com/users/NikolayS/following{/other_user}","gists_url":"https://api.github.com/users/NikolayS/gists{/gist_id}","starred_url":"https://api.github.com/users/NikolayS/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NikolayS/subscriptions","organizations_url":"https://api.github.com/users/NikolayS/orgs","repos_url":"https://api.github.com/users/NikolayS/repos","events_url":"https://api.github.com/users/NikolayS/events{/privacy}","received_events_url":"https://api.github.com/users/NikolayS/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-08-31T07:26:07Z","updated_at":"2015-08-31T16:27:29Z","closed_at":null,"body":"(This is related to #255)\r\n\r\nIn MANY cases the app is designed in the way where count is not needed at all. For example if you query items and show them to user in twitter/pinterest/facebook-like \"infinite\" list, when at the first step, only Top-N items are selected, then, for the second portion, again, Top-N items are selected but with some \"shift\" (using `id` or `created` column usually), and this process can be repeated many times.\r\n\r\nThis very common task. And usually you have many millions items in the table and query should be executed in milliseconds.\r\n\r\nSo in such cases count(1) is not needed at all. Moreover, estimated count is not needed either since the app doesn't show user any information about it (no items count, no page number). I implemented solutions with estimated count in a couple of large projects (dozends Ms of users) and unfortunately such approaches fail very fast – in cases when you have some filters where Postgres has no good statistics and makes error estimations (not to mention that it does require many additional milliseconds – see below). In many cases, it's better to show nothing to user than to show wrong information. So in my personal cases I decided to stop using estimated count for large datasets and design apps in a way where this info is not needed.\r\n\r\nHowever, in some cases count(1) is useful of course and should be provided.\r\n\r\nThe main thing here is that for years Postgres has slow count(1). This is very serious problem and people who use Postgres know how to handle it and how to live with it. \r\n\r\nWhat I want to say is that by default, count(1) is not needed. I propose to skip it by default and show only if user asks for it. This is separate issue than #255 since we would have 3 options: \r\n - no count\r\n - exact count\r\n - estimated count\r\n\r\n.. and large projects *need* the first one to be used by default, otherwise performance issues will be real headache. \r\n\r\n\"Estimated count by default\" is also no-good, since in loaded server it's better to avoid additional planner executions on *every* request. Here is a quick example for one real production server with 5k TPS, dozens of M of rows in the table:\r\n```\r\nXXXXXX=# explain select * from user where birthday <= '1980-01-01';\r\n                                      QUERY PLAN\r\n---------------------------------------------------------------------------------------\r\n Seq Scan on \"user\"  (cost=10000000000.00..10000273915.79 rows=1926654 width=405)\r\n   Filter: (birthday <= '1980-01-01'::date)\r\n(2 rows)\r\n\r\nTime: 83.222 ms\r\nXXXXXX=# explain select * from user where birthday <= '1980-01-01';\r\n                                      QUERY PLAN\r\n---------------------------------------------------------------------------------------\r\n Seq Scan on \"user\"  (cost=10000000000.00..10000273915.79 rows=1926654 width=405)\r\n   Filter: (birthday <= '1980-01-01'::date)\r\n(2 rows)\r\n\r\nTime: 0.295 ms\r\nXXXXXX=# explain select * from user where birthday > '2000-01-01';\r\n                                     QUERY PLAN\r\n-------------------------------------------------------------------------------------\r\n Seq Scan on \"user\"  (cost=10000000000.00..10000273915.79 rows=49839 width=405)\r\n   Filter: (birthday > '2000-01-01'::date)\r\n(2 rows)\r\n\r\nTime: 24.530 ms\r\nXXXXXX=# explain select * from user where birthday > '2000-01-01';\r\n                                     QUERY PLAN\r\n-------------------------------------------------------------------------------------\r\n Seq Scan on \"user\"  (cost=10000000000.00..10000273915.79 rows=49839 width=405)\r\n   Filter: (birthday > '2000-01-01'::date)\r\n(2 rows)\r\n\r\nTime: 0.338 ms\r\n```\r\n– no one would be happy to have additional 20-80 ms (even though 2nd call is quicker, but it should be the same call and what if you have many possible filters?).\r\n\r\nSo I propose to disable count(1) by default. This will help to adopt this solution in mid-sized and big apps. The project itself is great and well designed and according my twitter, more and more people are interested in such solutions."},"comment":{"url":"https://api.github.com/repos/begriffs/postgrest/issues/comments/136421047","html_url":"https://github.com/begriffs/postgrest/issues/273#issuecomment-136421047","issue_url":"https://api.github.com/repos/begriffs/postgrest/issues/273","id":136421047,"user":{"login":"NikolayS","id":1345402,"avatar_url":"https://avatars.githubusercontent.com/u/1345402?v=3","gravatar_id":"","url":"https://api.github.com/users/NikolayS","html_url":"https://github.com/NikolayS","followers_url":"https://api.github.com/users/NikolayS/followers","following_url":"https://api.github.com/users/NikolayS/following{/other_user}","gists_url":"https://api.github.com/users/NikolayS/gists{/gist_id}","starred_url":"https://api.github.com/users/NikolayS/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/NikolayS/subscriptions","organizations_url":"https://api.github.com/users/NikolayS/orgs","repos_url":"https://api.github.com/users/NikolayS/repos","events_url":"https://api.github.com/users/NikolayS/events{/privacy}","received_events_url":"https://api.github.com/users/NikolayS/received_events","type":"User","site_admin":false},"created_at":"2015-08-31T16:27:28Z","updated_at":"2015-08-31T16:27:28Z","body":"Thank you. \r\nThe \"optimization #2\" described in http://leopard.in.ua/2014/10/11/postgresql-paginattion/ is exactly what I talked about in the first paragraph.\r\n\r\nThis is widely used technique, with its drawbacks.\r\n\r\nCursor method I would rather consider for any heavy-loaded OLTP project. pgbouncer is a standard de facto right now, for connection pooling. I'm really not an expert in Postgres cursors topic, but AFAICS you're considering keeping connection between different requests – I'd expect problems with \"statement\" and  \"transaction\" pgbouncer's pooling modes (which are usually used under heavy load). So cursors are maybe not the best choice for OLTP apps that use pgbouncer. Though they might be considered as a good thing in non-OLTP fields when low numbers of users work with large data sets.\r\n\r\nMeanwhile, I wonder why you at all thinking about implementing these tasks inside PostgREST. Is the pagination really a task that should be implemented by API engine? This can lead the project to narrowing use cases where it can be used – I'd stick to be as low-level as possible, leaving this task to the end user who can choose between true OFFSET and \"order-and-shift\" approaches for pagination. Both approaches could be easily achieved with `eq` and `gte`/`lte` abilities that are already provided.\r\nMaybe it's better to be as low-level as possible and think about providing general ability to work with cursors for those who can afford them in their projects allowing implementing pagination or whatever on top of it?\r\n\r\n"}}