PostgreSQL Error: out of memory

Question

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.

Answer 1

First, let's assume that work_mem is at 1024MB, and not the impossible 1024GB reported (impossible with a total of 3GB on the machine).

Anyway it's much too high. As said in Resource Consumption in PostgreSQL documentation, with some emphasis added:

work_mem (integer)

Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.

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 1Mb is conservative, but I wouldn't raise work_mem above 128MB for a 3GB instance. shared_buffers on the other hand could be set to 1024MB: this one is allocated only once and kept for the entire instance's lifetime.