We have a large table (265M rows), which has about 50 columns in total, mostly either integers, dates, or varchars. The table has a primary key defined on an autoincremented column.
A query loads temp table with Pk values (say, 10000-20000 rows) and then the large table is queried by being joined to the temp table.
Average size of the row in the large table is fairly consistent and is around 280 bytes. Here is the query plan it produces, when running:
Nested Loop (cost=0.57..115712.21 rows=13515 width=372) (actual time=0.016..54797.581 rows=11838 loops=1)
Buffers: shared hit=49960 read=9261, local hit=53
-> Seq Scan on t_ids ti (cost=0.00..188.15 rows=13515 width=4) (actual time=0.006..6.993 rows=11838 loops=1)
Buffers: local hit=53
-> Index Scan using test_pk on test t (cost=0.57..8.55 rows=1 width=368) (actual time=4.624..4.624 rows=1 loops=11838)
Index Cond: (pk = ti.pk)
Buffers: shared hit=49960 read=9261
Planning Time: 0.128 ms
Execution Time: 54801.600 ms
... where test is the large table (actually, clustered by pk) and t_ids is the temporary table. It seems to be doing the right thing - scanning temp table and hitting the large table on the pk index, 11k times... But it is sooo slow....
Any suggestions on what can be tried to make it run faster are gretly appreciated!