PostgreSQL 9.3 – Fixing Invalid Memory Allocation Request Size

postgresqlpostgresql-9.3

Postgresql 9.3 – Debian 7 54gb RAM 8 cores (On google compute engine)

I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is:

select array_agg(st.search_token) from search_token st limit 200

This column search_token is a varchar(25) and I'm getting this error:

ERROR:  invalid memory alloc request size 1073741824

If I remove the array_agg from the query, it runs smoothly in 1ms.

It used to work fine and started to fail on last days. Nothing was changed in the config file or on OS config.

Another funny fact. If I write the query as:

select array_agg(x.search_token) from (select st.search_token from search_token st limit 100000) x;

It runs in 64ms. (Note that I also raised the limit from 200 to 100000). Why is it trying to allocate 1GB ram to process this query with limit 200 and isn't for the subquery form?

Best Answer

In your first query, you do the aggregate on the whole table, then limit the single row result to 200 rows. This is because the LIMIT is applied on the result set of its containing query.

If you did an EXPLAIN select array_agg(st.search_token) from search_token st limit 200;, it would return something like

Limit [...]
   -> [...]Aggregate [...]

Basically, the closer one node in the plan is to the bottom, the earlier it is executed (this is more complicated than that, but in this context it is true).

The good news is that you found the solution, too :)

Related Question