Thread: Large aggregate query running out of memory in ExecutorState

I have some nightly statisics queries that runs against a view which
unions several large tables. Recently one of these queries started
running into out of memory errors. This is on postgresql 8.1.8
running on 32-bit Debian Linux.

Here is the error in the log including the query (excluding memory
detail):

2007-03-02 05:20:46.302 PST [d:reports u:hits s:45e81fd1.635a 3]
ERROR:  out of memory
2007-03-02 05:20:46.302 PST [d:reports u:hits s:45e81fd1.635a 4]
DETAIL:  Failed on request of size 16.
2007-03-02 05:20:46.302 PST [d:reports u:hits s:45e81fd1.635a 5]
STATEMENT:  select count(case when is_positive then true end),
         count(case when not is_positive then true end),
         count(distinct st_id)",
         count(distinct st_id & X'ffffffff'::bigint)
         from fb_packed;

Here is the query plan for the above:

                                        QUERY PLAN
------------------------------------------------------------------------
-----------------
Aggregate  (cost=20393102.13..20393102.14 rows=1 width=9)
    ->  Append  (cost=0.00..12015096.06 rows=418900303 width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..20.30 rows=1030
width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..1411898.37
rows=75468337 width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..1675027.88
rows=89690588 width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..1553071.43
rows=83061343 width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..1632686.20
rows=87618920 width=45)
          ->  Seq Scan on fb_packed  (cost=0.00..1553388.85
rows=83060085 width=45)
(8 rows)

Not very complicated at all, just lots of rows to process. The
estimates for row quantity and width are roughly correct above.

Here is the relevant bits from the memory detail in the logs. Notice
the first ExecutorState value, which doesn't seem reasonable:

TopMemoryContext: 8241296 total in 12 blocks; 5528 free (13 chunks);
8235768 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks);
336 used
Local Buffer Lookup Table: 57344 total in 3 blocks; 23096 free (7
chunks); 34248 used
Type information cache: 8192 total in 1 blocks; 1864 free (0 chunks);
6328 used
Operator class cache: 8192 total in 1 blocks; 4936 free (0 chunks);
3256 used
MessageContext: 122880 total in 4 blocks; 59280 free (3 chunks);
63600 used
smgr relation table: 24576 total in 2 blocks; 9952 free (3 chunks);
14624 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 848 free (0 chunks); 176 used
ExecutorState: -1525554452 total in 337 blocks; 1476423576 free
(20505908 chunks); 1292989268 used
ExecutorState: 24576 total in 2 blocks; 15560 free (3 chunks); 9016 used
[...Remainder elided]

This just started recently, with no changes to the query itself.
Seems like we just crossed some size threshold that triggered this
condition. I tried analyzing all of the tables again just in case,
but it didn't help.

TIA,

-Casey

Casey Duncan <casey@pandora.com> writes:
> I have some nightly statisics queries that runs against a view which
> unions several large tables. Recently one of these queries started
> running into out of memory errors. This is on postgresql 8.1.8
> running on 32-bit Debian Linux.

What have you got work_mem set to, and what's the actual process size
limit the kernel is enforcing?  It looks to me like your query will
try to eat at least twice work_mem for the two COUNT DISTINCT operations.
Another issue is that the measurement of how much space is really being
used is not necessarily very accurate --- I don't recall exactly how
good 8.1 is about that, but it wouldn't surprise me too much if the
actual net memory demand was about twice as much again.

Anyway the short answer is probably "reduce work_mem".

            regards, tom lane