Re: Out of memory on SELECT (from sort?) in 8.3 - Mailing list pgsql-general

From Matt Magoffin
Subject Re: Out of memory on SELECT (from sort?) in 8.3
Date
Msg-id 49669.192.168.1.106.1221514089.squirrel@msqr.us
Whole thread Raw
In response to Re: Out of memory on SELECT (from sort?) in 8.3  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Out of memory on SELECT (from sort?) in 8.3  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
>> 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@


pgsql-general by date:

Previous
From: "Merlin Moncure"
Date:
Subject: Re: Oracle and Postgresql
Next
From: Christophe
Date:
Subject: Re: Oracle and Postgresql