Yikes: ERROR: out of memory - Mailing list pgsql-general

Now THIS is a new one for me! I have no idea where to even start. Does
anyone know how to look for the error? Below is the query and what I believe
are the related log entries.

Any help will be rewarded with heartfelt gratitude and praise, or you can
just come to Montreal and hit me up for a beer.

Carlo


ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 134217728.

SELECT facility_id, street_address, base_zip, COUNT(*) AS provider_count
FROM (
   SELECT DISTINCT
      f.facility_id,
      p.provider_id,
      TRIM(COALESCE(a.parsed_number, '')||' '||a.parsed_street) AS
street_address,
      SUBSTR(a.postal_code, 1, 5) AS base_zip
   FROM mdx_core.provider AS p
   JOIN mdx_core.provider_practice AS pp USING (provider_id)
   JOIN mdx_core.facility_address AS fa USING (facility_address_id)
   JOIN mdx_core.facility AS f ON f.facility_id = fa.facility_id
   JOIN mdx_core.address AS a USING (address_id)
   WHERE
      p.provider_status_code = 'A'
      AND pp.practice_tier_code <= '3'
) AS p_per_addr
GROUP BY facility_id, street_address, base_zip
HAVING COUNT(*) > 1

ERROR:  out of memory
DETAIL:  Failed on request of size 134217728.

********** Error **********

TopMemoryContext: 85688 total in 10 blocks; 8952 free (25 chunks); 76736 use
  TopTransactionContext 192 total in 1 blocks; 7856 free (1 chunks); 336
used
  Record information cache 192 total in 1 blocks; 1800 free (0 chunks); 6392
used
  Local Breakpoint Count Table 192 total in 1 blocks; 4872 free (0 chunks);
3320 used
  Local Breakpoints: 8192 total in 1 blocks; 5904 free (0 chunks); 2288 use
  PL/PgSQL function context 192 total in 1 blocks; 7216 free (3 chunks); 976
used
  PLpgSQL function cache 4328 total in 2 blocks; 5904 free (0 chunks); 18424
used
  Type information cache 192 total in 1 blocks; 1800 free (0 chunks); 6392
used
  CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 use
  Operator class cache 192 total in 1 blocks; 3848 free (0 chunks); 4344
used
  Operator lookup cache 4576 total in 2 blocks; 14072 free (6 chunks); 10504
used
  MessageContext: 524288 total in 7 blocks; 246336 free (2 chunks); 277952
use
  smgr relation table: 8192 total in 1 blocks; 744 free (0 chunks); 7448 use
  TransactionAbortContext 2768 total in 1 blocks; 32752 free (0 chunks); 16
used
  Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 use
  PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 use
    PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 use
      ExecutorState: 57344 total in 3 blocks; 3240 free (6 chunks); 54104
use
        HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext 66222872 total in 77 blocks; 4824944 free (75
chunks); 561397928 used
        HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext 0847768 total in 16 blocks; 3739736 free (9
chunks); 47108032 used
        HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext 35258136 total in 26 blocks; 4958688 free (24
chunks); 130299448 used
        HashTableContext  total in 0 blocks; 0 free (0 chunks); 0 used
          HashBatchContext 20192792 total in 36 blocks; 7649816 free (29
chunks); 212542976 used
        TupleSort: 369090584 total in 46 blocks; 7648 free (25 chunks);
369082936 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 8192 total in 1 blocks; 8088 free (4 chunks); 104 use
        Unique: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        AggContext: 8192 total in 1 blocks; 8104 free (0 chunks); 88 use
          TupleHashTable 186112 total in 9 blocks; 2064088 free (25 chunks);
2122024 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 use
  Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 use
  CacheMemoryContext: 1191760 total in 21 blocks; 588472 free (751 chunks);
603288 use
    provider_input_resource_id_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_upin_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
    provider_provider_staus_code_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_provider_standing_code_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_provider_id_provider_status_code_idx 024 total in 1 blocks; 280
free (0 chunks); 744 used
    provider_provider_id_master_name_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    provider_npi_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
    provider_mid_status_code_idx 024 total in 1 blocks; 280 free (0 chunks);
744 used
    provider_master_name_idx 024 total in 1 blocks; 344 free (0 chunks); 680
used
    provider_master_id_idx 024 total in 1 blocks; 344 free (0 chunks); 680
used
    provider_ln_lower_fn_lower_status_idx 024 total in 1 blocks; 192 free (0
chunks); 832 used
    provider_ln_lower_fn_lower_standing_status_idx 024 total in 1 blocks;
128 free (0 chunks); 896 used
    provider_last_name_metaphone_name_initials_lower_idx 024 total in 1
blocks; 192 free (0 chunks); 832 used
    provider_last_name_lower_first_name_lower_idx 024 total in 1 blocks; 280
free (0 chunks); 744 used
    provider_last_name_lower_first_initial_lower_birth_data_idx 024 total in
1 blocks; 72 free (0 chunks); 952 used
    provider_birth_year_idx 024 total in 1 blocks; 344 free (0 chunks); 680
used
    provider_abms_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
    provider_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
    provider_master_name_unique_key 024 total in 1 blocks; 344 free (0
chunks); 680 used
    pg_type_typname_nsp_index 024 total in 1 blocks; 240 free (0 chunks);
784 used
    pg_statistic_relid_att_index 024 total in 1 blocks; 240 free (0 chunks);
784 used
    address_id_country_state_zip_cityzone_idx 024 total in 1 blocks; 128
free (0 chunks); 896 used
    address_country_zip_cityzone_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    address_country_zip_base_idx 024 total in 1 blocks; 280 free (0 chunks);
744 used
    address_country_state_postal_code_idx 024 total in 1 blocks; 192 free (0
chunks); 832 used
    address_country_state_city_idx 024 total in 1 blocks; 192 free (0
chunks); 832 used
    address_country_postal_code_address_idx 024 total in 1 blocks; 192 free
(0 chunks); 832 used
    address_addres_id_idx 024 total in 1 blocks; 304 free (0 chunks); 720
used
    address_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 use
    facility_name_lower_idx 024 total in 1 blocks; 344 free (0 chunks); 680
used
    facility_facility_type_code_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    facility_facility_country_state_postal_code_idx 024 total in 1 blocks;
128 free (0 chunks); 896 used
    facility_facility_country_state_city_idx 024 total in 1 blocks; 128 free
(0 chunks); 896 used
    facility_country_zip_cityzone_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    facility_country_zip_base_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    facility_country_state_postal_code_idx 024 total in 1 blocks; 192 free
(0 chunks); 832 used
    facility_country_state_city_idx 024 total in 1 blocks; 192 free (0
chunks); 832 used
    facility_country_postal_code_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    facility_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
    facility_address_facility_address_id_idx 024 total in 1 blocks; 344 free
(0 chunks); 680 used
    facility_address_facility_address_address_type_idx 024 total in 1
blocks; 152 free (0 chunks); 872 used
    facility_address_address_idx 024 total in 1 blocks; 344 free (0 chunks);
680 used
    facility_address_pkey 024 total in 1 blocks; 344 free (0 chunks); 680
used
    provider_practice_faid_record_status_idx 024 total in 1 blocks; 280 free
(0 chunks); 744 used
    provider_practice_state_code_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_practice_record_status_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_practice_provider_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_practice_practice_tier_code_idx 024 total in 1 blocks; 344 free
(0 chunks); 680 used
    provider_practice_pid_record_status_idx 024 total in 1 blocks; 280 free
(0 chunks); 744 used
    provider_practice_is_principal_record_status_idx 024 total in 1 blocks;
280 free (0 chunks); 744 used
    provider_practice_is_principal_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    provider_practice_is_primary_idx 024 total in 1 blocks; 280 free (0
chunks); 744 used
    provider_practice_facility_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_practice_facility_address_idx 024 total in 1 blocks; 304 free
(0 chunks); 720 used
    provider_practice_default_state_country_idx 024 total in 1 blocks; 280
free (0 chunks); 744 used
    provider_practice_default_city_state_country_idx 024 total in 1 blocks;
192 free (0 chunks); 832 used
    provider_practice_default_base_zip_country_idx 024 total in 1 blocks;
192 free (0 chunks); 832 used
    provider_practice_dea_number_idx 024 total in 1 blocks; 344 free (0
chunks); 680 used
    provider_practice_pkey 024 total in 1 blocks; 344 free (0 chunks); 680
used
    pg_index_indrelid_index 024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_aggregate_fnoid_index 024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_amop_opr_fam_index 024 total in 1 blocks; 240 free (0 chunks); 784
used
    pg_proc_proname_args_nsp_index 024 total in 1 blocks; 152 free (0
chunks); 872 used
    pg_cast_source_target_index 024 total in 1 blocks; 240 free (0 chunks);
784 used
    pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
    pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 use
    pg_operator_oprname_l_r_n_index 024 total in 1 blocks; 88 free (0
chunks); 936 used
    pg_attrdef_adrelid_adnum_index 024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_class_relname_nsp_index 024 total in 1 blocks; 240 free (0 chunks);
784 used
    pg_authid_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used
    pg_namespace_nspname_index 024 total in 1 blocks; 304 free (0 chunks);
720 used
    CachedPlan: 1024 total in 1 blocks; 40 free (0 chunks); 984 use
    CachedPlanSource: 3072 total in 2 blocks; 1688 free (0 chunks); 1384 use
    SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 use
    pg_opclass_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720
used
    pg_amop_fam_strat_index 024 total in 1 blocks; 88 free (0 chunks); 936
used
    pg_trigger_tgrelid_tgname_index 024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_attribute_relid_attnum_index 024 total in 1 blocks; 240 free (0
chunks); 784 used
    pg_class_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720 used
    pg_amproc_fam_proc_index 024 total in 1 blocks; 88 free (0 chunks); 936
used
    pg_index_indexrelid_index 024 total in 1 blocks; 304 free (0 chunks);
720 used
    pg_rewrite_rel_rulename_index 024 total in 1 blocks; 280 free (0
chunks); 744 used
    pg_operator_oid_index 024 total in 1 blocks; 304 free (0 chunks); 720
used
  MdSmgr: 8192 total in 1 blocks; 5488 free (164 chunks); 2704 use
  LOCALLOCK hash: 24576 total in 2 blocks; 16168 free (4 chunks); 8408 use
  Rendezvous variable hash 192 total in 1 blocks; 3848 free (0 chunks); 4344
used
  Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 use
  ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 use
2010-03-14 19:51:42 EDT ERROR out of memory
2010-03-14 19:51:42 EDT DETAIL Failed on request of size 134217728.
2010-03-14 19:51:42 EDT STATEMENT SELECT facility_id, street_address,
base_zip, COUNT(*) AS provider_count
   FROM (
      SELECT DISTINCT
         f.facility_id,
         p.provider_id,
         TRIM(COALESCE(a.parsed_number, '')||' '||a.parsed_street) AS
street_address,
         SUBSTR(a.postal_code, 1, 5) AS base_zip
      FROM mdx_core.provider AS p
      JOIN mdx_core.provider_practice AS pp USING (provider_id)
      JOIN mdx_core.facility_address AS fa USING (facility_address_id)
      JOIN mdx_core.facility AS f ON f.facility_id = fa.facility_id
      JOIN mdx_core.address AS a USING (address_id)
      WHERE
         p.provider_status_code = 'A'
         AND pp.practice_tier_code <= '3'
   ) AS p_per_addr
   GROUP BY facility_id, street_address, base_zip
   HAVING COUNT(*) > 1