Using windowing function to do multiple smaller limits. Give me the first 3 records (for each grouping) If you use limit, this will require 1 query to determine the groupings, and one limit queries per each grouping. N+1

windowing.sql

create table windowing_example (id int, counter int);

-- id is the common component of the rows
-- counter is the timestamp / other id where ordering is introduced
insert into windowing_example(id, counter) values
  (1, 1),
  (1, 2),
  (1, 3),
  (1, 4),
  (1, 5),
  (2, 6),
  (2, 7),
  (2, 8),
  (2, 9),
  (2, 10),
  (3, 1),
  (3, 2),
  (3, 3),
  (3, 4),
  (3, 5);

--- I want:
--- 1, 5
--- 1, 4
--- 1, 3
--- 2, 10
--- 2, 9
--- 2, 8
--- 3, 5
--- 3, 4
--- 3, 3


--- orig N+1 query (show the 3 highest counters for each id )
select distinct id from windowing_example; --get ids
select id from windowing_example where id = 1 order by counter desc limit 3; -- get data per id
select id from windowing_example where id = 2 order by counter desc limit 3;
select id from windowing_example where id = 3 order by counter desc limit 3;


--- new 1 query (show the 3 highest counters for each id )
select id, counter
  from (
    select id, counter, rank()
      over (partition by id
                order by counter desc) --setup grouping, and precedent/ordering
      from windowing_example
  ) x
  where rank <= 3;
 



                                                      QUERY FIRST PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=36.75..38.75 rows=200 width=4) (actual time=0.014..0.014 rows=3 loops=1)
   ->  Seq Scan on windowing_example  (cost=0.00..31.40 rows=2140 width=4) (actual time=0.005..0.007 rows=15 loops=1)
 Total runtime: 0.041 ms

                                                       QUERY N PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=36.89..36.90 rows=3 width=8) (actual time=0.018..0.018 rows=3 loops=1)
   ->  Sort  (cost=36.89..36.92 rows=11 width=8) (actual time=0.017..0.017 rows=3 loops=1)
         Sort Key: counter
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on windowing_example  (cost=0.00..36.75 rows=11 width=8) (actual time=0.012..0.014 rows=5 loops=1)
               Filter: (id = 1)
               Rows Removed by Filter: 10
 Total runtime: 0.054 ms

---

                                                        SINGLE QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on x  (cost=149.78..219.33 rows=713 width=16) (actual time=0.032..0.050 rows=9 loops=1)
   Filter: (x.rank <= 3)
   Rows Removed by Filter: 6
   ->  WindowAgg  (cost=149.78..192.58 rows=2140 width=8) (actual time=0.030..0.046 rows=15 loops=1)
         ->  Sort  (cost=149.78..155.13 rows=2140 width=8) (actual time=0.020..0.022 rows=15 loops=1)
               Sort Key: windowing_example.id, windowing_example.counter
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on windowing_example  (cost=0.00..31.40 rows=2140 width=8) (actual time=0.003..0.003 rows=15 loops=1)
 Total runtime: 0.092 ms