How to optimize inner join query on one large table and one small table

Question

I have table A with 15M records and table B with 5k records. I need to perform an inner join on both but the query time is considerably high.

explain analyze 
SELECT distinct(a.student_id), b.student_name, a.class_year
FROM table_a a
INNER JOIN table_b b on a.student_id = b.student_id;

Explain Plan

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3628096.85..3779293.37 rows=11780855 width=50) (actual time=35421.004..50690.702 rows=5078 loops=1)
   ->  Sort  (cost=3628096.85..3665895.98 rows=15119652 width=50) (actual time=35421.002..46385.451 rows=14264755 loops=1)
         Sort Key: a.student_id, b.student_name, a.class_year
         Sort Method: external merge  Disk: 890528kB
         ->  Hash Join  (cost=242.20..1308298.78 rows=15119652 width=50) (actual time=3.877..22332.795 rows=14264755 loops=1)
               Hash Cond: ((a.student_id)::text = (b.student_id)::text)
               ->  Seq Scan on table_a a  (cost=0.00..1268336.52 rows=15119652 width=25) (actual time=0.035..6168.042 rows=15119652 loops=1)
               ->  Hash  (cost=174.31..174.31 rows=5431 width=45) (actual time=3.822..3.822 rows=5431 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 483kB
                     ->  Seq Scan on table_b b  (cost=0.00..174.31 rows=5431 width=45) (actual time=0.008..1.886 rows=5431 loops=1)
 Planning time: 2.386 ms
 Execution time: 50822.593 ms
(12 rows)

I have an index on table_a for student_id

"student_id" btree (student_id)

Answer 1

You could try to first get the rows you are interested in, then do the join. Assuming you want the row with the highest class_year per student you can try:

select a.student_id, b.student_name, a.class_year
from (
  SELECT distinct on (student_id) student_id, class_year
  FROM table_a
  ORDER BY student_id, class_year desc
) a 
  JOIN table_b b on a.student_id = b.student_id;

Or you could try a GROUP BY instead, which can be done using a parallel aggregation since Postgres 11:

select a.student_id, b.student_name, a.class_year
from (
  SELECT student_id, max(class_year) as class_year
  FROM table_a
  GROUP BY student_id
) a 
  JOIN table_b b on a.student_id = b.student_id;

An index on table_a (student_id, class_year) should help with that.

Answer 2

Looks to me like the best plan is being used:

1. First scan the small table B to make the hash buckets,
2. then scan the big table A to find the matching rows from B via the hash.

You may have an I/O problem, 15 seconds for 15 million records, although if I assume that each row of table A is about 100 bytes long, then scanning it in 50 seconds is a throughput of about 30 MB/s, which isn't out of the ordinary on normal hardware, and depending on what else is going on.

And then, yes, you spend a lot of time in the DISTINCT sort. If you join and you get multiple matches, but you are not interested in the replicates at all, then it means you don't really need the columns of the joined table, except if your schema isn't normalized.

It seems that that's the clue actually.

SELECT distinct a.student_id, b.student_name, a.class_year
  FROM table_a a
  INNER JOIN table_b b on a.student_id = b.student_id;

The query doesn't seem to be logically thought through. You would get all those different class years, but nothing else from table_A. Look at your final result numbers, it says "rows=5078", which is about the size of table_B. You probably should normalize all these repeating class_years out of this massive table_A.