I've been struggling with the performance on a query. Trying to do a lookup of a large set of ids in a large table (500GB). I create a temporary table of the ids with the ids as a primary key and run analyze after. It takes about 17 seconds to execute the below query for 1000 ids for uncached data (2 seconds cached). Our typical query is usually for about 100,000 ids which can take 5-12 minutes. These times are only for the below query and do not include the temp table creation. Looking at the query plan, its the nested loop that seems to be bogging it down. We are on the largest RDS machine at 244GB RAM so we can't load the whole table into memory. What can I do to improve it?
The query looks like:
SELECT * FROM temp_table
JOIN large_table ON temp_table.id = large_table.id
The output from explain:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=0.00..5420956.52 rows=2682643 width=46) │
│ -> Seq Scan on temp_table (cost=0.00..1065.09 rows=73809 width=8) │
│ -> Index Scan using id_idx on large_table (cost=0.00..73.07 rows=36 width=46) │
│ Index Cond: (id = (temp_table.id)::numeric) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘
Running postgresql 9.5.4 on AWS RDS.
EXTRA INFO
The large_table id field is
id │ numeric(18,0) │ not null
While the temp_table id field is
id | bigint | not null default nextval('temp_table_id_seq'::regclass)
The indices for the large table:
"large_table_pkey" PRIMARY KEY, btree (id, month)
"id_idx" btree (id)
Output from EXPLAIN (ANALYZE, BUFFERS):
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=0.00..5420956.52 rows=2682643 width=46) (actual time=0.107..90871.252 rows=745842 loops=1) │
│ Buffers: shared hit=747255 read=163891 │
│ -> Seq Scan on temp_table (cost=0.00..1065.09 rows=73809 width=8) (actual time=0.026..29.963 rows=73809 loops=1) │
│ Buffers: shared hit=327 │
│ -> Index Scan using id_idx on large_table (cost=0.00..73.07 rows=36 width=46) (actual time=0.139..1.204 rows=10 loops=73809) │
│ Index Cond: (id = (temp_table.id)::numeric) │
│ Rows Removed by Index Recheck: 0 │
│ Buffers: shared hit=746928 read=163891 │
│ Planning time: 0.844 ms │
│ Execution time: 91051.477 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)
Time: 91067.663 ms
DB parameters:
┌──────────┐
│ work_mem │
├──────────┤
│ 1GB │
└──────────┘
──────────────────────┐
│ effective_cache_size │
├──────────────────────┤
│ 188781552kB │
└──────────────────────┘
┌──────────────────┐
│ random_page_cost │
├──────────────────┤
│ 2 │
└──────────────────┘
┌────────────────┐
│ shared_buffers │
├────────────────┤
│ 62927184kB │
└────────────────┘
UPDATE 1
Tried to do a SELECT cols FROM large_table WHERE id = ANY (VALUES (1), (2),....) which yielded a HashAggregate instead of a SeqScan but it took almost 8 minutes for 21,000 ids.
Nested Loop (cost=321.26..15436.17 rows=275581120 width=38)
-> HashAggregate (cost=320.69..322.69 rows=200 width=8)
Group Key: ("*VALUES*".column1)::numeric
-> Values Scan on "*VALUES*" (cost=0.00..267.24 rows=21379 width=8)
-> Index Scan using id_idx on large_table (cost=0.58..75.21 rows=36 width=46)
Index Cond: (id = ("*VALUES*".column1)::numeric)
