title: which is more efficient, select array_agg over partition, or select array (subquery)?
body: <p>I have data like:</p>
  
  <pre><code> group_id |         day | amount
  ----------+-------------+-------
          1 | 15 Nov 2015 |   5.0
          1 | 15 Nov 2015 |   6.0
          1 | 14 Nov 2015 |   3.0
          2 | 17 Nov 2015 |   5.0
          2 | 15 Nov 2015 |   5.0
  </code></pre>
  
  <p>and I want to select the top ten amounts for each (group_id, day).  I tried writing things like:</p>
  
  <p>Postgres 9.4</p>
  
  <pre><code>select max(x.group_id), max(x.day), max(x.amounts) 
  from (select group_id, day, array_agg(amount) over w as amounts,
               row_number() over w as r
        from my_table window w as (partition group_id, day 
                                   order by amount desc)) as x
  where x.r&lt;=10 group by x.group_id,x.day
  </code></pre>
  
  <p>It also occurred to me that I could write a much more straightforward query:</p>
  
  <pre><code>select a.day, a.group_id, array(select amount 
                                  from my_table 
                                  where day=a.day and group_id=a.group_id
                                  order by amount desc limit 10)
  from my_table as a group by a.day, a.group_id
  </code></pre>
  
  <p>Which does exactly what I want.  This led me to the question: assuming I can tweak the first example to get what I want, which query would be faster?  Is the subquery slower than the partitions ?</p>