Yikes: ERROR: out of memory

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