Re: Out of memory on SELECT (from sort?) in 8.3

>> I have a SELECT query that causes an out-of-memory error on my
>> production
>> Postgres 8.3 server.
>
> 8.3.which, and what exactly is the query and its EXPLAIN plan?
>
>> I believe the culprit is from the sort in the query,
>
> No, the problem seems to be here
>
>> ExecutorState: 841031232 total in 51159 blocks; 1712232 free (56
>> chunks); 839319000 used
>
> and it's impossible to guess what that's about without a lot more
> details than you provided.

I see, I was looking at the log entries wrong, sorry. This is Postgres
8.3.3 + an xml.c patch (from Kris -
http://archives.postgresql.org/pgsql-general/2008-07/msg00110.php) to fix
a memory leak there. The EXPLAIN for this query was this:

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=238861.67..239149.85 rows=115274 width=70)
Sort Key: id
-> Bitmap Heap Scan on lead (cost=3828.21..229170.17 rows=115274
width=70)
Recheck Cond: (((date(timezone('-06:00:00'::interval, created))
>= '2008-08-15'::date) AND (date(timezone('-06:00:00'::interval,
created)) <= '2008-08-15'::date)) OR
((date(timezone('-06:00:00'::interval, modified)) >=
'2008-08-15'::date) AND (date(timezone('-06:00:00'::interval,
modified)) <= '2008-08-15'::date)))
-> BitmapOr (cost=3828.21..3828.21 rows=115274 width=0)
-> Bitmap Index Scan on reporting_date_idx
(cost=0.00..4.91 rows=1 width=0)
Index Cond: ((date(timezone('-06:00:00'::interval,
created)) >= '2008-08-15'::date) AND
(date(timezone('-06:00:00'::interval, created)) <=
'2008-08-15'::date))
-> Bitmap Index Scan on reporting_modified_idx
(cost=0.00..3765.67 rows=115274 width=0)
Index Cond: ((date(timezone('-06:00:00'::interval,
modified)) >= '2008-08-15'::date) AND
(date(timezone('-06:00:00'::interval, modified)) <=
'2008-08-15'::date))
(9 rows)

This particular query fails with this out-of-memory error when the result
size is about 300k or so.

I'm also seeing this TopMemoryContext output in the logs from some other
situations (sorry I'm not familiar with what/how this gets logged), like
this autovacuum task that appears to dump this into the log:

2008-07-17 00:19:26 CDT ERROR: canceling autovacuum task
2008-07-17 00:19:26 CDT CONTEXT: automatic vacuum of table
"lms_infiniti._lms.sl_log_2"
TopMemoryContext: 129952 total in 15 blocks; 32088 free (139 chunks);
97864 used
LibxmlContext: 8380416 total in 10 blocks; 3123904 free (0 chunks);
5256512 used
TopTransactionContext: 8192 total in 1 blocks; 7648 free (0 chunks); 544
used
PL/PgSQL function context: 8192 total in 1 blocks; 4400 free (3 chunks);
3792 used
PL/PgSQL function context: 8192 total in 1 blocks; 5512 free (4 chunks);
2680 used
Type information cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks);
12688 used
Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512
used
PL/PgSQL function context: 24576 total in 2 blocks; 21000 free (10
chunks); 3576 used
CFuncHash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
Rendezvous variable hash: 8192 total in 1 blocks; 1680 free (0 chunks);
6512 used
PLpgSQL function cache: 24224 total in 2 blocks; 3744 free (0 chunks);
20480 used
Record information cache: 24576 total in 2 blocks; 15984 free (5
chunks); 8592 used
Prepared Queries: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
MessageContext: 8192 total in 1 blocks; 6976 free (1 chunks); 1216 used
smgr relation table: 24576 total in 2 blocks; 9776 free (4 chunks);
14800 used
TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks);
32 used
Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used
PortalMemory: 8192 total in 1 blocks; 7616 free (0 chunks); 576 used
PortalHeapMemory: 2048 total in 1 blocks; 360 free (0 chunks); 1688 used
ExecutorState: 1019641424 total in 92998 blocks; 6834024 free (66
chunks); 1012807400 used
TIDBitmap: 4186112 total in 9 blocks; 1774784 free (26 chunks);
2411328 used
TupleSort: 31449136 total in 13 blocks; 3451184 free (12 chunks);
27997952 used
ExprContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96 used
ExprContext: 8192 total in 1 blocks; 8096 free (0 chunks); 96 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 1024 total in 1 blocks; 944 free (0 chunks); 80 used
Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used
CacheMemoryContext: 1341680 total in 21 blocks; 620264 free (1633
chunks); 721416 used
pg_toast_16580_index: 2048 total in 1 blocks; 608 free (0 chunks);
1440 used
CachedPlan: 15360 total in 4 blocks; 1528 free (0 chunks); 13832 used
CachedPlanSource: 15360 total in 4 blocks; 1440 free (0 chunks); 13920
used
reporting_modified_idx: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
reporting_last_processed_date_idx: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
reporting_date_idx: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
lead_created_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
lead_processing_step_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
lead_destination_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
lead_modified_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
lead_source_idx: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
processing_state_idx: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
lead_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
unnamed prepared statement: 8192 total in 1 blocks; 3856 free (2
chunks); 4336 used
locks_pkey: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
CachedPlan: 3072 total in 2 blocks; 648 free (0 chunks); 2424 used
pg_language_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
xslt_style_sheet_pkey: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
offline_report_key_key: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
offline_report_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
pg_type_typname_nsp_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_statistic_relid_att_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_amop_opr_fam_index: 2048 total in 1 blocks; 608 free (0 chunks);
1440 used
external_system_user_name_key: 2048 total in 1 blocks; 752 free (0
chunks); 1296 used
external_system_name_key: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
external_system_pkey: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_cast_source_target_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_operator_oprname_l_r_n_index: 2048 total in 1 blocks; 392 free (0
chunks); 1656 used
pg_class_relname_nsp_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
pg_type_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_proc_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
pg_proc_proname_args_nsp_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
pg_namespace_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
pg_namespace_nspname_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
CachedPlan: 3072 total in 2 blocks; 1160 free (0 chunks); 1912 used
CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
CachedPlanSource: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 3072 total in 2 blocks; 1984 free (0 chunks); 1088 used
CachedPlanSource: 3072 total in 2 blocks; 1792 free (1 chunks); 1280 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 3072 total in 2 blocks; 1984 free (0 chunks); 1088 used
CachedPlanSource: 3072 total in 2 blocks; 1792 free (1 chunks); 1280 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 3072 total in 2 blocks; 1984 free (0 chunks); 1088 used
CachedPlanSource: 3072 total in 2 blocks; 1792 free (1 chunks); 1280 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 3072 total in 2 blocks; 1984 free (0 chunks); 1088 used
CachedPlanSource: 3072 total in 2 blocks; 1792 free (1 chunks); 1280 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
CachedPlanSource: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlan: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
CachedPlanSource: 1024 total in 1 blocks; 80 free (0 chunks); 944 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlanSource: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
CachedPlan: 1024 total in 1 blocks; 312 free (0 chunks); 712 used
CachedPlanSource: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
SPI Plan: 1024 total in 1 blocks; 832 free (0 chunks); 192 used
CachedPlanSource: 3072 total in 2 blocks; 1248 free (1 chunks); 1824 used
SPI Plan: 1024 total in 1 blocks; 808 free (0 chunks); 216 used
CachedPlan: 1024 total in 1 blocks; 816 free (0 chunks); 208 used
CachedPlanSource: 1024 total in 1 blocks; 664 free (1 chunks); 360 used
pg_opclass_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
pg_amop_fam_strat_index: 3072 total in 2 blocks; 1384 free (2 chunks);
1688 used
pg_trigger_tgrelid_tgname_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_attribute_relid_attnum_index: 3072 total in 2 blocks; 1600 free (2
chunks); 1472 used
pg_class_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks); 1376
used
pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1384 free (2
chunks); 1688 used
pg_index_indexrelid_index: 3072 total in 2 blocks; 1696 free (2
chunks); 1376 used
pg_rewrite_rel_rulename_index: 3072 total in 2 blocks; 1648 free (2
chunks); 1424 used
pg_operator_oid_index: 3072 total in 2 blocks; 1696 free (2 chunks);
1376 used
MdSmgr: 8192 total in 1 blocks; 7296 free (54 chunks); 896 used
LOCALLOCK hash: 24576 total in 2 blocks; 15984 free (5 chunks); 8592 used
Timezones: 53584 total in 2 blocks; 3744 free (0 chunks); 49840 used
ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used

If there is any other information I can provide, please let me know.

-- m@