I'm trying to run a query that should return around 2000 rows, but my RDS-hosted PostgreSQL 9.3 database is giving me the error "out of memory DETAIL: Failed on request of size 2048.".
What does that mean? My instance has 3GB of memory, so what would be limiting it enough to run out of memory with such a small query?
Edit:
SHOW work_mem;
"1024GB"
I can't show the full SQL, but it's attempting to perform a pivot. I have two primary tables, library and book, which points back to a library record. My query attempts to find the most popular book for each of the last 12 months for each library record, and join them to a separate column in the result queryset, to have something like:
library_id, month_1_book_id, month_2_book_id, month_3_book_id, ...
Explain shows this results in quite a few loops:
explain
select * from myapp_library_get_monthly_popular
where id in (5495060, 5495059, 5495048)
Nested Loop Left Join (cost=3645798.54..3750412.91 rows=3 width=2980)
-> Nested Loop Left Join (cost=3645798.10..3750388.98 rows=3 width=2994)
-> Nested Loop Left Join (cost=3645797.66..3750365.05 rows=3 width=2976)
-> Nested Loop Left Join (cost=3645797.23..3750341.13 rows=3 width=2958)
-> Nested Loop Left Join (cost=3645796.79..3750317.20 rows=3 width=2940)
-> Nested Loop Left Join (cost=3645796.35..3750293.27 rows=3 width=2922)
-> Nested Loop Left Join (cost=3645795.91..3750269.35 rows=3 width=2904)
-> Nested Loop Left Join (cost=3645795.48..3750245.42 rows=3 width=2886)
-> Nested Loop Left Join (cost=3645795.04..3750221.49 rows=3 width=2868)
-> Nested Loop Left Join (cost=3645794.60..3750197.57 rows=3 width=2850)
-> Nested Loop Left Join (cost=3645794.16..3750173.64 rows=3 width=2832)
-> Nested Loop Left Join (cost=3645793.73..3750149.71 rows=3 width=2814)
-> Hash Join (cost=3645793.29..3750125.79 rows=3 width=2796)
Hash Cond: (c.category_id = ct.id)
-> Hash Join (cost=3645792.24..3750124.70 rows=3 width=2578)
Hash Cond: (c.company_id = cp.id)
-> Hash Join (cost=3645791.18..3750123.59 rows=3 width=2360)
Hash Cond: (c_1.id = c.id)
-> HashAggregate (cost=3645772.11..3695454.20 rows=3974567 width=8)
-> Hash Right Join (cost=2178696.20..3595882.33 rows=9977957 width=8)
Hash Cond: (u.library_id = c_1.id)
Join Filter: (u.period_start_date >= ((max(u_1.period_start_date)) - '1 year'::interval))
-> Seq Scan on myapp_book u (cost=0.00..594004.70 rows=29933870 width=12)
-> Hash (cost=2129014.12..2129014.12 rows=3974567 width=8)
-> Hash Join (cost=1960095.02..2129014.12 rows=3974567 width=8)
Hash Cond: (c_2.id = c_1.id)
-> HashAggregate (cost=1780709.26..1820454.93 rows=3974567 width=8)
-> Hash Right Join (cost=288688.46..1556205.24 rows=29933870 width=8)
Hash Cond: (u_1.library_id = c_2.id)
-> Seq Scan on myapp_book u_1 (cost=0.00..594004.70 rows=29933870 width=8)
-> Hash (cost=239006.38..239006.38 rows=3974567 width=4)
-> Hash Join (cost=2.11..239006.38 rows=3974567 width=4)
Hash Cond: (c_2.category_id = ct_1.id)
-> Hash Join (cost=1.07..184355.03 rows=3974567 width=8)
Hash Cond: (c_2.company_id = cp_1.id)
-> Seq Scan on myapp_library c_2 (cost=0.00..129703.67 rows=3974567 width=12)
-> Hash (cost=1.03..1.03 rows=3 width=4)
-> Seq Scan on myapp_company cp_1 (cost=0.00..1.03 rows=3 width=4)
-> Hash (cost=1.02..1.02 rows=2 width=4)
-> Seq Scan on myapp_category ct_1 (cost=0.00..1.02 rows=2 width=4)
-> Hash (cost=129703.67..129703.67 rows=3974567 width=4)
-> Seq Scan on myapp_library c_1 (cost=0.00..129703.67 rows=3974567 width=4)
-> Hash (cost=19.02..19.02 rows=3 width=2328)
-> Index Scan using myapp_library_pkey on myapp_library c (cost=0.43..19.02 rows=3 width=2328)
Index Cond: (id = ANY ('{5495060,5495059,5495048}'::integer[]))
-> Hash (cost=1.03..1.03 rows=3 width=222)
-> Seq Scan on myapp_company cp (cost=0.00..1.03 rows=3 width=222)
-> Hash (cost=1.02..1.02 rows=2 width=222)
-> Seq Scan on myapp_category ct (cost=0.00..1.02 rows=2 width=222)
-> Index Scan using myapp_book_pkey on myapp_book u_01 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[1])
-> Index Scan using myapp_book_pkey on myapp_book u_02 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[2])
-> Index Scan using myapp_book_pkey on myapp_book u_03 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[3])
-> Index Scan using myapp_book_pkey on myapp_book u_04 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[4])
-> Index Scan using myapp_book_pkey on myapp_book u_05 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[5])
-> Index Scan using myapp_book_pkey on myapp_book u_06 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[6])
-> Index Scan using myapp_book_pkey on myapp_book u_07 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[7])
-> Index Scan using myapp_book_pkey on myapp_book u_08 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[8])
-> Index Scan using myapp_book_pkey on myapp_book u_09 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[9])
-> Index Scan using myapp_book_pkey on myapp_book u_10 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[10])
-> Index Scan using myapp_book_pkey on myapp_book u_11 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[11])
-> Index Scan using myapp_book_pkey on myapp_book u_12 (cost=0.44..7.97 rows=1 width=22)
Index Cond: (id = ((array_agg(u.id)))[12])
The RDS instance has 3.75 GB of memory, but RDS appears to limit work_mem to at most 2 GB.
My query is contained in a view, so if I want to target specific libraries, I query the view with those IDs, as you see above. The problem seems to be how Postgres plans using the view. If I run the raw query, without the view, the results return instantly. It's only when I wrap my query in a view, and query IDs from that that I get a memory error.
Best Answer
First, let's assume that
work_memis at1024MB, and not the impossible1024GBreported (impossible with a total of3GBon the machine).Anyway it's much too high. As said in Resource Consumption in PostgreSQL documentation, with some emphasis added:
The query whose plan is shown is complex and requires several levels of hashing, so you're clearly in the case the doc is warning against.
The default
1Mbis conservative, but I wouldn't raisework_memabove128MBfor a3GBinstance.shared_bufferson the other hand could be set to1024MB: this one is allocated only once and kept for the entire instance's lifetime.