Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE school_students (
  id BIGSERIAL PRIMARY KEY,
  school_id varchar NOT NULL,
  student_id varchar NOT NULL
);
CREATE UNIQUE INDEX school_students_school_student_idx ON school_students(school_id, student_id);

CREATE TABLE id_cards (
  id BIGSERIAL PRIMARY KEY,
  school_student_id bigint NOT NULL REFERENCES school_students(id)
);
CREATE INDEX id_cards_school_student_id_idx ON id_cards(school_student_id);

CREATE TABLE



CREATE INDEX



CREATE TABLE



CREATE INDEX


-- sample school_student data
INSERT INTO school_students (school_id, student_id) 
SELECT 1, generate_series(0, 200);

INSERT INTO school_students (school_id, student_id) 
SELECT 2, generate_series(0, 10000);

INSERT INTO school_students (school_id, student_id) 
SELECT 3, generate_series(0, 20000);

-- sample id_card data
INSERT INTO id_cards (school_student_id) 
SELECT generate_series(1, 200);

INSERT INTO id_cards (school_student_id) 
SELECT generate_series(201, 10200);

INSERT INTO id_cards (school_student_id) 
SELECT generate_series(201, 10200);

INSERT INTO id_cards (school_student_id) 
SELECT generate_series(10101, 30200);

INSERT 0 201



INSERT 0 10001



INSERT 0 20001



INSERT 0 200



INSERT 0 10000



INSERT 0 10000



INSERT 0 20100


-- for tests below
VACUUM ANALYZE id_cards;

VACUUM


-- for tests below
VACUUM ANALYZE school_students;

VACUUM


-- covering PK index (optional)
ALTER TABLE id_cards
  DROP CONSTRAINT id_cards_pkey
, ADD  CONSTRAINT id_cards_pkey PRIMARY KEY (id) INCLUDE (school_student_id);

ALTER TABLE


-- generic query - decent performance for moderately few rows
SELECT card_id
FROM  (
   SELECT card_id
        , row_number() OVER (PARTITION BY school_id ORDER BY card_id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM  (
      SELECT DISTINCT ON (s.school_id, s.student_id)
             s.school_id, c.id AS card_id
      FROM   id_cards c
      JOIN   school_students s ON s.id = c.school_student_id
      ORDER  BY s.school_id, s.student_id, c.id DESC
      ) sub1
   ) sub2
WHERE  rn <= 2;  -- your limit


card_id


10201

200

20302

20301

40300

40299



SELECT 6


-- same, show all columns
SELECT *
FROM  (
   SELECT *
        , row_number() OVER (PARTITION BY school_id ORDER BY card_id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM  (
      SELECT DISTINCT ON (s.school_id, s.student_id)
             s.*, c.id AS card_id
      FROM   id_cards c
      JOIN   school_students s ON s.id = c.school_student_id
      ORDER  BY s.school_id, s.student_id, c.id DESC
      ) sub1
   ) sub2
WHERE  rn <= 2;  -- your limit


id
school_id
student_id
card_id
rn


201
1
200
10201
1

200
1
199
200
2

10202
2
10000
20302
1

10201
2
9999
20301
2

30200
3
19997
40300
1

30199
3
19996
40299
2



SELECT 6


-- Possible optimization (falls short for this data distribution!)
SELECT card_id
FROM  (
   SELECT card_id
        , row_number() OVER (PARTITION BY school_id ORDER BY card_id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM  (
      SELECT DISTINCT ON (s.school_id, s.student_id)
             s.*, c.id AS card_id
      FROM  (
         SELECT *
         FROM   id_cards
         ORDER  BY id DESC
         LIMIT  1000         -- this covers the latest 2 for all schools (!)
         ) c
      JOIN   school_students s ON s.id = c.school_student_id
      ORDER  BY s.school_id, s.student_id, c.id DESC
      ) sub1
   ) sub2
WHERE  rn <= 2;  -- your limit


card_id


40300

40299



SELECT 2


-- slow generic query for comparison
EXPLAIN ANALYZE
SELECT card_id
FROM  (
   SELECT card_id
        , row_number() OVER (PARTITION BY school_id ORDER BY card_id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM  (
      SELECT DISTINCT ON (s.school_id, s.student_id)
             s.school_id, c.id AS card_id
      FROM   id_cards c
      JOIN   school_students s ON s.id = c.school_student_id
      ORDER  BY s.school_id, s.student_id, c.id DESC
      ) sub1
   ) sub2
WHERE  rn <= 2;  -- your limit


QUERY PLAN


Subquery Scan on sub2  (cost=4660.94..7116.45 rows=6661 width=8) (actual time=149.742..181.656 rows=6 loops=1)

  Filter: (sub2.rn <= 2)

  Rows Removed by Filter: 30194

  ->  WindowAgg  (cost=4660.94..6866.67 rows=19982 width=18) (actual time=149.740..180.096 rows=30200 loops=1)

        ->  Incremental Sort  (cost=4660.94..6516.99 rows=19982 width=10) (actual time=149.735..170.993 rows=30200 loops=1)

              Sort Key: sub1.school_id, sub1.card_id DESC

              Presorted Key: sub1.school_id

              Full-sort Groups: 3  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

              Pre-sorted Groups: 3  Sort Method: quicksort  Average Memory: 856kB  Peak Memory: 1706kB

              ->  Subquery Scan on sub1  (cost=4653.01..5155.08 rows=19982 width=10) (actual time=149.576..162.241 rows=30200 loops=1)

                    ->  Unique  (cost=4653.01..4955.26 rows=19982 width=15) (actual time=149.572..158.876 rows=30200 loops=1)

                          ->  Sort  (cost=4653.01..4753.76 rows=40300 width=15) (actual time=149.569..152.094 rows=40300 loops=1)

                                Sort Key: s.school_id, s.student_id, c.id DESC

                                Sort Method: quicksort  Memory: 4094kB

                                ->  Hash Join  (cost=843.57..1570.37 rows=40300 width=15) (actual time=7.543..24.220 rows=40300 loops=1)

                                      Hash Cond: (c.school_student_id = s.id)

                                      ->  Seq Scan on id_cards c  (cost=0.00..621.00 rows=40300 width=16) (actual time=0.008..3.846 rows=40300 loops=1)

                                      ->  Hash  (cost=466.03..466.03 rows=30203 width=15) (actual time=7.506..7.507 rows=30203 loops=1)

                                            Buckets: 32768  Batches: 1  Memory Usage: 1672kB

                                            ->  Seq Scan on school_students s  (cost=0.00..466.03 rows=30203 width=15) (actual time=0.007..3.369 rows=30203 loops=1)

Planning Time: 0.235 ms

Execution Time: 182.076 ms



EXPLAIN


 -- faster optimized query
EXPLAIN ANALYZE
SELECT card_id
FROM  (
   SELECT card_id
        , row_number() OVER (PARTITION BY school_id ORDER BY card_id DESC ROWS UNBOUNDED PRECEDING) AS rn
   FROM  (
      SELECT DISTINCT ON (s.school_id, s.student_id)
             s.*, c.id AS card_id
      FROM  (
         SELECT *
         FROM   id_cards
         ORDER  BY id DESC
         LIMIT  1000         -- this covers the latest 2 for all schools (!)
         ) c
      JOIN   school_students s ON s.id = c.school_student_id
      ORDER  BY s.school_id, s.student_id, c.id DESC
      ) sub1
   ) sub2
WHERE  rn <= 2;  -- your limit


QUERY PLAN


Subquery Scan on sub2  (cost=692.69..779.54 rows=333 width=8) (actual time=6.789..7.200 rows=2 loops=1)

  Filter: (sub2.rn <= 2)

  Rows Removed by Filter: 998

  ->  WindowAgg  (cost=692.69..767.04 rows=1000 width=18) (actual time=6.788..7.146 rows=1000 loops=1)

        ->  Incremental Sort  (cost=692.69..749.54 rows=1000 width=10) (actual time=6.785..6.846 rows=1000 loops=1)

              Sort Key: sub1.school_id, sub1.card_id DESC

              Presorted Key: sub1.school_id

              Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

              Pre-sorted Groups: 1  Sort Method: quicksort  Average Memory: 71kB  Peak Memory: 71kB

              ->  Subquery Scan on sub1  (cost=692.49..709.99 rows=1000 width=10) (actual time=6.197..6.547 rows=1000 loops=1)

                    ->  Unique  (cost=692.49..699.99 rows=1000 width=23) (actual time=6.197..6.427 rows=1000 loops=1)

                          ->  Sort  (cost=692.49..694.99 rows=1000 width=23) (actual time=6.196..6.254 rows=1000 loops=1)

                                Sort Key: s.school_id, s.student_id, id_cards.id DESC

                                Sort Method: quicksort  Memory: 71kB

                                ->  Hash Join  (cost=53.37..642.66 rows=1000 width=23) (actual time=5.472..5.834 rows=1000 loops=1)

                                      Hash Cond: (s.id = id_cards.school_student_id)

                                      ->  Seq Scan on school_students s  (cost=0.00..466.03 rows=30203 width=15) (actual time=0.010..2.133 rows=30203 loops=1)

                                      ->  Hash  (cost=40.87..40.87 rows=1000 width=16) (actual time=0.346..0.347 rows=1000 loops=1)

                                            Buckets: 1024  Batches: 1  Memory Usage: 55kB

                                            ->  Limit  (cost=0.29..30.87 rows=1000 width=16) (actual time=0.010..0.221 rows=1000 loops=1)

                                                  ->  Index Only Scan Backward using id_cards_pkey on id_cards  (cost=0.29..1232.79 rows=40300 width=16) (actual time=0.009..0.141 rows=1000 loops=1)

                                                        Heap Fetches: 0

Planning Time: 0.283 ms

Execution Time: 7.236 ms



EXPLAIN


CREATE VIEW school_id_cards AS
SELECT c.id AS card_id, ss.school_id, ss.student_id
FROM   id_cards c
JOIN   school_students ss ON c.school_student_id = ss.id;

CREATE VIEW


-- for comparison: Schwern's query
with ordered_student_cards as (
  select *, dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order
  from school_id_cards
), ordered_school_cards as (
  select *, dense_rank() over(partition by school_id order by card_id desc) as school_card_order
  from ordered_student_cards
  where student_card_order = 1
)
select card_id
from ordered_school_cards
where school_card_order <= 2;


card_id


10201

200

20302

20301

40300

40299



SELECT 6


EXPLAIN ANALYZE
with ordered_student_cards as (
  select *, dense_rank() over(partition by school_id, student_id order by card_id desc) as student_card_order
  from school_id_cards
), ordered_school_cards as (
  select *, dense_rank() over(partition by school_id order by card_id desc) as school_card_order
  from ordered_student_cards
  where student_card_order = 1
)
select card_id
from ordered_school_cards
where school_card_order <= 2;


QUERY PLAN


Subquery Scan on ordered_school_cards  (cost=5126.54..6082.50 rows=67 width=8) (actual time=148.959..214.949 rows=6 loops=1)

  Filter: (ordered_school_cards.school_card_order <= 2)

  Rows Removed by Filter: 30194

  ->  WindowAgg  (cost=5126.54..6079.97 rows=202 width=58) (actual time=148.957..213.236 rows=30200 loops=1)

        ->  Incremental Sort  (cost=5126.54..6076.44 rows=202 width=10) (actual time=148.954..195.329 rows=30200 loops=1)

              Sort Key: ordered_student_cards.school_id, ordered_student_cards.card_id DESC

              Presorted Key: ordered_student_cards.school_id

              Full-sort Groups: 3  Sort Method: quicksort  Average Memory: 28kB  Peak Memory: 28kB

              Pre-sorted Groups: 3  Sort Method: quicksort  Average Memory: 856kB  Peak Memory: 1706kB

              ->  Subquery Scan on ordered_student_cards  (cost=4653.01..6063.51 rows=202 width=10) (actual time=148.685..184.957 rows=30200 loops=1)

                    Filter: (ordered_student_cards.student_card_order = 1)

                    Rows Removed by Filter: 10100

                    ->  WindowAgg  (cost=4653.01..5559.76 rows=40300 width=23) (actual time=148.683..180.746 rows=40300 loops=1)

                          ->  Sort  (cost=4653.01..4753.76 rows=40300 width=15) (actual time=148.667..151.593 rows=40300 loops=1)

                                Sort Key: ss.school_id, ss.student_id, c.id DESC

                                Sort Method: quicksort  Memory: 3255kB

                                ->  Hash Join  (cost=843.57..1570.37 rows=40300 width=15) (actual time=7.815..23.423 rows=40300 loops=1)

                                      Hash Cond: (c.school_student_id = ss.id)

                                      ->  Seq Scan on id_cards c  (cost=0.00..621.00 rows=40300 width=16) (actual time=0.010..3.782 rows=40300 loops=1)

                                      ->  Hash  (cost=466.03..466.03 rows=30203 width=15) (actual time=7.770..7.772 rows=30203 loops=1)

                                            Buckets: 32768  Batches: 1  Memory Usage: 1672kB

                                            ->  Seq Scan on school_students ss  (cost=0.00..466.03 rows=30203 width=15) (actual time=0.008..3.618 rows=30203 loops=1)

Planning Time: 0.345 ms

Execution Time: 215.336 ms



EXPLAIN
id	school_id	student_id	card_id	rn
201	1	200	10201	1
200	1	199	200	2
10202	2	10000	20302	1
10201	2	9999	20301	2
30200	3	19997	40300	1
30199	3	19996	40299	2