>> 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@
