Re: Reducing memory usage of insert into select operations?

On 1:00 pm 07/18/08 Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Are there any AFTER triggers (including foreign key constraints)

I have two foreign key constraints.

> the table being inserted into?  If so the list of pending trigger
> events might be your problem.

I guess I can try disablign the foreign key, but that would be less than
ideal for production. This is an analytics environment so all operations
are in bulk.

> If you can get Postgres to report an actual out-of-memory error (as
> opposed to crashing from OOM kill)

Disabled oom with vm.overcommit_memory=2.

>then it should dump a memory usage
>map into the postmaster log.  Looking at that would be informative.

Got it.
----------------------
AfterTriggerEvents: 10553909248 total in 1268 blocks; 20432 free (6
chunks); 10553888816 used
    ExecutorState: 122880 total in 4 blocks; 68040 free (8 chunks); 54840
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
  MessageContext: 131072 total in 5 blocks; 50712 free (291 chunks); 80360
used
  smgr relation table: 24576 total in 2 blocks; 3584 free (4 chunks); 20992
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; 7888 free (0 chunks); 304 used
    PortalHeapMemory: 1024 total in 1 blocks; 768 free (0 chunks); 256 used
      ExecutorState: 98784 total in 8 blocks; 24064 free (22 chunks); 74720
used
        ExprContext: 8192 total in 1 blocks; 8016 free (0 chunks); 176 used
        HashTableContext: 8192 total in 1 blocks; 8064 free (1 chunks); 128
used
          HashBatchContext: 532676656 total in 74 blocks; 1863936 free (5
chunks); 530812720 used
        HashTableContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext: 415227952 total in 59 blocks; 6589744 free (5
chunks); 408638208 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
Relcache by OID: 24576 total in 2 blocks; 8672 free (3 chunks); 15904 used
  CacheMemoryContext: 2390256 total in 22 blocks; 751904 free (2 chunks);
1638352 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
    CachedPlan: 7168 total in 3 blocks; 3120 free (0 chunks); 4048 used
    CachedPlanSource: 7168 total in 3 blocks; 1816 free (0 chunks); 5352 used
    SPI Plan: 1024 total in 1 blocks; 784 free (0 chunks); 240 used
    CachedPlan: 3072 total in 2 blocks; 792 free (0 chunks); 2280 used
    CachedPlanSource: 7168 total in 3 blocks; 3600 free (0 chunks); 3568 used
    SPI Plan: 1024 total in 1 blocks; 800 free (0 chunks); 224 used
    pg_cast_source_target_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_language_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    pg_toast_2619_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
    tcf_mnfoids_partid: 2048 total in 1 blocks; 752 free (0 chunks); 1296
used
    tcf_mnfoids_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    cards_cardnum_key: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    cards_pkey: 2048 total in 1 blocks; 752 free (0 chunks); 1296 used
    tcf_original_trans_partid_cardnum: 2048 total in 1 blocks; 656 free (0
chunks); 1392 used
    tcf_original_trans_yearmo: 2048 total in 1 blocks; 752 free (0 chunks);
1296 used
    pg_constraint_contypid_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
    pg_constraint_conname_nsp_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_proc_proname_args_nsp_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
    pg_proc_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344 used
    pg_shdepend_reference_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_namespace_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    pg_statistic_relid_att_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_inherits_relid_seqno_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_constraint_oid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    pg_attribute_relid_attnam_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_attrdef_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344
used
    pg_shdepend_depender_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
    pg_type_typname_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_depend_depender_index: 2048 total in 1 blocks; 584 free (0 chunks);
1464 used
    pg_depend_reference_index: 2048 total in 1 blocks; 584 free (0 chunks);
1464 used
    pg_index_indrelid_index: 2048 total in 1 blocks; 704 free (0 chunks);
1344 used
    pg_description_o_c_o_index: 2048 total in 1 blocks; 584 free (0
chunks); 1464 used
    pg_constraint_conrelid_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
    pg_attrdef_adrelid_adnum_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_class_relname_nsp_index: 2048 total in 1 blocks; 608 free (0
chunks); 1440 used
    pg_namespace_nspname_index: 2048 total in 1 blocks; 704 free (0
chunks); 1344 used
    pg_authid_oid_index: 2048 total in 1 blocks; 704 free (0 chunks); 1344
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: 24576 total in 2 blocks; 15872 free (0 chunks); 8704 used
  LOCALLOCK hash: 57344 total in 3 blocks; 36384 free (11 chunks); 20960 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
ERROR:  out of memory
DETAIL:  Failed on request of size 40.