Re: Yikes: ERROR: out of memory

Carlo Stonebanks wrote:
> 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.
>
[...]
>
> 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.

[...]

What is your work_mem setting?

At first glance it looks as if you have dangerously little free memory
and a high work_mem setting (and possibly many connections doing expensive
operation concurrently). Are you aware that work_mem limits memory use
per operation, so you need to have more than work_mem * max_connections
memory free to be on the safe side?

Yours,
Laurenz Albe