Agreed that we should make the "lists" pages faster. I had not investigated far enough to discover that count(*) was the problem.

I had not investigated far enough to discover that count(*) was the problem

It might not be the only problem, but it is a problem.

2) we can use estimates from query planner

I'm fine with this, but we should make sure to indicate in the ui that is is an estimate. E.g. "Tasks 1 through 50 of about 15100"

3) Do the first page fast without count and just "next" button, while second page can show what is there today (count + pages)

This also seems fine to me and is certainly easier.

As I understand it, postgres has to do the whole query even without the count call, since it has to order the rows it returns, so I wonder how much of this is because we're calling count in a separate rpc call, and therefore a separate transaction.

I wonder if things would improve if we could optionally include the count in the single query. Perhaps with this approach:

https://stackoverflow.com/questions/3984643/equivalent-of-found-rows-function-in-postgresql

I think that the last is not easy to achieve without slowing other cases. Planner will have it more complicated. E.g simple comparison (not exactly what task page does).

explain  select count(*) over(), id from task where method = 'buildArch' order by id desc limit 100;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..180.97 rows=100 width=12)
   ->  WindowAgg  (cost=0.44..5038823.90 rows=2791103 width=12)
         ->  Index Scan Backward using task_pkey on task  (cost=0.44..5003935.12 rows=2791103 width=4)
               Filter: (method = 'buildArch'::text)
(4 rows)

vs

brew=> explain  select  id from task where method = 'buildArch' order by id desc limit 100;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..179.72 rows=100 width=4)
   ->  Index Scan Backward using task_pkey on task  (cost=0.44..5003935.12 rows=2791103 width=4)
         Filter: (method = 'buildArch'::text)
(3 rows)

window aggregation put there whole index scan, while without it it can quit after finding first 100 results. Real times are 200ms vs something over 14 minutes (cancelled).

Option 3 is super easy and saves load in the common cases. Maybe we should start there and see how far it gets us?

Unless we think people will complain?

A tangent to #1, if we get less than the limit number of results, perhaps we can assume that we've hit the last page and simply calculate the count ourselves?

I'm struggling with reliable estimation. Problem is that basic technique uses explain to parse original query. But it needs correct query string which we don't get without cursor.execute(). Best variant looks to be here https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/

CREATE FUNCTION row_estimator(query text) RETURNS bigint
   LANGUAGE plpgsql AS
$$DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;

   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;

But it also needs SQL string input. It could be improved to create prepared statement and delete it after that, but it looks to me like a lot of additional complexity in QueryProcessor. Something like this:

PREPARE foo AS SELECT * FROM task WHERE parent IS NULL;
SELECT row_estimator('EXECUTE foo');
DEALLOCATE foo;

Such call could use normal argument interpolation without interfering with '.

For basic variant (skipping count on first page): #2827

Commit d6694d0 fixes this issue

Commit aa2011c fixes this issue