Postgres 16

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) 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) 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) 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) 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=4658.84..6622.11 rows=19983 width=8) (actual time=96.497..119.528 rows=6 loops=1)

  ->  WindowAgg  (cost=4658.84..6422.28 rows=19983 width=18) (actual time=96.496..119.525 rows=6 loops=1)

        Run Condition: (row_number() OVER (?) <= 2)

        ->  Incremental Sort  (cost=4658.84..6072.58 rows=19983 width=10) (actual time=96.491..116.851 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: 27kB  Peak Memory: 27kB

              Pre-sorted Groups: 3  Sort Method: quicksort  Average Memory: 777kB  Peak Memory: 1550kB

              ->  Subquery Scan on sub1  (cost=4653.01..5155.09 rows=19983 width=10) (actual time=96.329..108.675 rows=30200 loops=1)

                    ->  Unique  (cost=4653.01..4955.26 rows=19983 width=15) (actual time=96.326..105.247 rows=30200 loops=1)

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

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

                                Sort Method: quicksort  Memory: 3255kB

                                ->  Hash Join  (cost=843.57..1570.37 rows=40300 width=15) (actual time=8.310..21.381 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.005..3.139 rows=40300 loops=1)

                                      ->  Hash  (cost=466.03..466.03 rows=30203 width=15) (actual time=8.279..8.280 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.004..3.484 rows=30203 loops=1)

Planning Time: 0.198 ms

Execution Time: 119.784 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) 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=682.64..755.60 rows=1000 width=8) (actual time=6.305..6.449 rows=2 loops=1)

  ->  WindowAgg  (cost=682.64..745.60 rows=1000 width=18) (actual time=6.304..6.448 rows=2 loops=1)

        Run Condition: (row_number() OVER (?) <= 2)

        ->  Incremental Sort  (cost=682.64..728.10 rows=1000 width=10) (actual time=6.301..6.352 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: 27kB  Peak Memory: 27kB

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

              ->  Subquery Scan on sub1  (cost=682.49..699.99 rows=1000 width=10) (actual time=5.674..6.021 rows=1000 loops=1)

                    ->  Unique  (cost=682.49..689.99 rows=1000 width=23) (actual time=5.673..5.895 rows=1000 loops=1)

                          ->  Sort  (cost=682.49..684.99 rows=1000 width=23) (actual time=5.673..5.732 rows=1000 loops=1)

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

                                Sort Method: quicksort  Memory: 64kB

                                ->  Hash Join  (cost=43.37..632.66 rows=1000 width=23) (actual time=5.101..5.423 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.007..1.989 rows=30203 loops=1)

                                      ->  Hash  (cost=30.87..30.87 rows=1000 width=16) (actual time=0.365..0.366 rows=1000 loops=1)

                                            Buckets: 1024  Batches: 1  Memory Usage: 55kB

                                            ->  Limit  (cost=0.29..30.87 rows=1000 width=16) (actual time=0.020..0.224 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.019..0.146 rows=1000 loops=1)

                                                        Heap Fetches: 0

Planning Time: 0.265 ms

Execution Time: 6.481 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=5125.22..6077.79 rows=202 width=8) (actual time=100.523..140.466 rows=6 loops=1)

  ->  WindowAgg  (cost=5125.22..6075.77 rows=202 width=58) (actual time=100.522..140.463 rows=6 loops=1)

        Run Condition: (dense_rank() OVER (?) <= 2)

        ->  Incremental Sort  (cost=5125.22..6072.23 rows=202 width=10) (actual time=100.518..137.784 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: 27kB  Peak Memory: 27kB

              Pre-sorted Groups: 3  Sort Method: quicksort  Average Memory: 777kB  Peak Memory: 1550kB

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

                    Filter: (ordered_student_cards.student_card_order = 1)

                    ->  WindowAgg  (cost=4653.01..5559.76 rows=40300 width=23) (actual time=100.275..125.726 rows=30200 loops=1)

                          Run Condition: (dense_rank() OVER (?) <= 1)

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

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

                                Sort Method: quicksort  Memory: 3111kB

                                ->  Hash Join  (cost=843.57..1570.37 rows=40300 width=15) (actual time=8.527..21.576 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.007..3.196 rows=40300 loops=1)

                                      ->  Hash  (cost=466.03..466.03 rows=30203 width=15) (actual time=8.488..8.489 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.005..3.684 rows=30203 loops=1)

Planning Time: 0.325 ms

Execution Time: 140.732 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