Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

psql:postgres@cipafilter = EXPLAIN (ANALYZE, BUFFERS) select titleid
from titles WHERE NOT EXISTS ( SELECT 1 FROM log_raw WHERE
log_raw.titleid = titles.titleid );
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Nope, that pops too.  The query runs for a long time at a somewhat
normal rate of ram consumption, using ~1G of RSS then suddenly spikes
to about 6G, at which point the OOM killer pops it.  Box has 8G of ram
and 4G of swap.

On Mon, Feb 13, 2017 at 3:21 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle <hinkle@cipafilter.com>
> wrote:
>>
>> Thanks Jeff,
>>
>> No triggers or foreign key constrains:
>>
>> psql:postgres@cipafilter = \d+ titles
>>                                                      Table "public.titles"
>>  Column  │       Type        │                        Modifiers
>>                  │ Storage  │ Stats target │ Description
>>
>>
─────────┼───────────────────┼──────────────────────────────────────────────────────────┼──────────┼──────────────┼─────────────
>>  title   │ character varying │
>>                  │ extended │              │
>>  titleid │ integer           │ not null default
>> nextval('titles_titleid_seq'::regclass) │ plain    │              │
>> Indexes:
>>     "titles_pkey" PRIMARY KEY, btree (titleid)
>>     "titles_md5_title_idx" btree (md5(title::text))
>>
>> Do you see anything in there that would be problematic?
>
>
>
> I'm out of ideas here.  What happens if you just select the rows, rather
> than deleting them?  Does it have memory problems then?  If not, can you
> post the explain (analyze, buffers) of doing that?
>
> Cheers,
>
> Jeff



-- 
David Hinkle

Senior Software Developer

Phone:  800.243.3729x3000

Email:  hinkle@cipafilter.com

Hours:  Mon-Fri   8:00AM-5:00PM (CT)