title: postgres window function trebles query time
body: <p>I'm using postgres 10, and have the following query</p>
  
  <pre><code>select 
      count(task.id) over() as _total_ ,
      json_agg(u.*) as users, 
      task.* 
  
      from task  
          left outer join taskuserlink_history tu on (task.id = tu.taskid) 
              left outer join "user" u on (tu.userId = u.id) 
  
      group by task.id offset 10 limit 10;
  </code></pre>
  
  <p>this query takes approx 800ms to execute </p>
  
  <p>if I remove the <code>count(task.id) over() as _total_ ,</code> line, then it executes in 250ms </p>
  
  <p>I have to confess being a complete sql noob, so the query itself may be completely borked</p>
  
  <p>I was wondering if anyone could point to the flaws in the query, and make suggestions on how to speed it up.</p>
  
  <p>The number of <code>tasks</code> is approx 15k, with an average of 5 <code>users</code> per task, linked through <code>taskuserlink</code></p>
  
  <p>I have looked at the pgadmin "explain" diagram </p>
  
  <p><a href="https://i.stack.imgur.com/R3UYA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/R3UYA.png" alt="enter image description here"></a></p>
  
  <p>but to be honest can't really figure it out yet ;)</p>
  
  <p>the table definitions are</p>
  
  <p><code>task</code> , with <code>id</code> (int) as primary column</p>
  
  <p><code>taskuserlink_history</code>, with <code>taskId</code> (int) and <code>userId</code> (int) (both as foreign key constraints, indexed)</p>
  
  <p><code>user</code>, with <code>id</code> (int) as primary column</p>
  
  <p>the query plan is as follows</p>
  
  <pre><code>-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=4.74..12.49 rows=10 width=44) (actual time=1178.016..1178.043 rows=10 loops=1)
     Buffers: shared hit=3731, temp read=6655 written=6914
     -&gt;  WindowAgg  (cost=4.74..10248.90 rows=13231 width=44) (actual time=1178.014..1178.040 rows=10 loops=1)
           Buffers: shared hit=3731, temp read=6655 written=6914
           -&gt;  GroupAggregate  (cost=4.74..10083.51 rows=13231 width=36) (actual time=0.417..1049.294 rows=13255 loops=1)
                 Group Key: task.id
                 Buffers: shared hit=3731
                 -&gt;  Nested Loop Left Join  (cost=4.74..9586.77 rows=66271 width=36) (actual time=0.103..309.372 rows=66162 loops=1)
                       Join Filter: (taskuserlink_history.userid = user_archive.id)
                       Rows Removed by Join Filter: 1182904
                       Buffers: shared hit=3731
                       -&gt;  Merge Left Join  (cost=0.58..5563.22 rows=66271 width=8) (actual time=0.044..73.598 rows=66162 loops=1)
                             Merge Cond: (task.id = taskuserlink_history.taskid)
                             Buffers: shared hit=3629
                             -&gt;  Index Only Scan using task_pkey on task  (cost=0.29..1938.30 rows=13231 width=4) (actual time=0.026..7.683 rows=13255 loops=1)
                                   Heap Fetches: 13255
                                   Buffers: shared hit=1810
                             -&gt;  Index Scan using taskuserlink_history_task_fk_idx on taskuserlink_history  (cost=0.29..2764.46 rows=66271 width=8) (actual time=0.015..40.109 rows=66162 loops=1)
                                   Filter: (timeend IS NULL)
                                   Rows Removed by Filter: 13368
                                   Buffers: shared hit=1819
                       -&gt;  Materialize  (cost=4.17..50.46 rows=4 width=36) (actual time=0.000..0.001 rows=19 loops=66162)
                             Buffers: shared hit=102
                             -&gt;  Bitmap Heap Scan on user_archive  (cost=4.17..50.44 rows=4 width=36) (actual time=0.050..0.305 rows=45 loops=1)
                                   Recheck Cond: (archived_at IS NULL)
                                   Heap Blocks: exact=11
                                   Buffers: shared hit=102
                                   -&gt;  Bitmap Index Scan on user_unique_username  (cost=0.00..4.16 rows=4 width=0) (actual time=0.014..0.014 rows=46 loops=1)
                                         Buffers: shared hit=1
                                   SubPlan 1
                                     -&gt;  Aggregate  (cost=8.30..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=45)
                                           Buffers: shared hit=90
                                           -&gt;  Index Scan using task_assignedto_idx on task task_1  (cost=0.29..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=45)
                                                 Index Cond: (assignedtoid = user_archive.id)
                                                 Buffers: shared hit=90
   Planning time: 0.989 ms
   Execution time: 1191.451 ms
  (37 rows)
  </code></pre>
  
  <p>without the window function it is </p>
  
  <pre><code>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=4.74..12.36 rows=10 width=36) (actual time=0.510..1.763 rows=10 loops=1)
     Buffers: shared hit=91
     -&gt;  GroupAggregate  (cost=4.74..10083.51 rows=13231 width=36) (actual time=0.509..1.759 rows=10 loops=1)
           Group Key: task.id
           Buffers: shared hit=91
           -&gt;  Nested Loop Left Join  (cost=4.74..9586.77 rows=66271 width=36) (actual time=0.073..0.744 rows=50 loops=1)
                 Join Filter: (taskuserlink_history.userid = user_archive.id)
                 Rows Removed by Join Filter: 361
                 Buffers: shared hit=91
                 -&gt;  Merge Left Join  (cost=0.58..5563.22 rows=66271 width=8) (actual time=0.029..0.161 rows=50 loops=1)
                       Merge Cond: (task.id = taskuserlink_history.taskid)
                       Buffers: shared hit=7
                       -&gt;  Index Only Scan using task_pkey on task  (cost=0.29..1938.30 rows=13231 width=4) (actual time=0.016..0.031 rows=11 loops=1)
                             Heap Fetches: 11
                             Buffers: shared hit=4
                       -&gt;  Index Scan using taskuserlink_history_task_fk_idx on taskuserlink_history  (cost=0.29..2764.46 rows=66271 width=8) (actual time=0.009..0.081 rows=50 loops=1)
                             Filter: (timeend IS NULL)
                             Rows Removed by Filter: 11
                             Buffers: shared hit=3
                 -&gt;  Materialize  (cost=4.17..50.46 rows=4 width=36) (actual time=0.001..0.009 rows=8 loops=50)
                       Buffers: shared hit=84
                       -&gt;  Bitmap Heap Scan on user_archive  (cost=4.17..50.44 rows=4 width=36) (actual time=0.040..0.382 rows=38 loops=1)
                             Recheck Cond: (archived_at IS NULL)
                             Heap Blocks: exact=7
                             Buffers: shared hit=84
                             -&gt;  Bitmap Index Scan on user_unique_username  (cost=0.00..4.16 rows=4 width=0) (actual time=0.012..0.012 rows=46 loops=1)
                                   Buffers: shared hit=1
                             SubPlan 1
                               -&gt;  Aggregate  (cost=8.30..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=38)
                                     Buffers: shared hit=76
                                     -&gt;  Index Scan using task_assignedto_idx on task task_1  (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=38)
                                           Index Cond: (assignedtoid = user_archive.id)
                                           Buffers: shared hit=76
   Planning time: 0.895 ms
   Execution time: 1.890 ms
  (35 rows)|
  </code></pre>