Re: Bad planning data resulting in OOM killing of postgres

I managed to get this version to finish:

psql:postgres(at)cipafilter = explain (ANALYZE, BUFFERS) select count(*)
from (select titleid from log_raw group by titleid) as a;
QUERY PLAN
─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Aggregate (cost=14099827.15..14099827.16 rows=1 width=0) (actual
time=248805.549..248805.549 rows=1 loops=1)
Buffers: shared hit=598 read=7324082 dirtied=34
-> HashAggregate (cost=14099820.80..14099823.62 rows=282 width=4)
(actual time=248504.756..248760.382 rows=874750 loops=1)
Group Key: log_raw.titleid
Buffers: shared hit=598 read=7324082 dirtied=34
-> Seq Scan on log_raw (cost=0.00..12744792.64
rows=542011264 width=4) (actual time=0.002..145554.907 rows=544654818
loops=1)
Buffers: shared hit=598 read=7324082 dirtied=34
Planning time: 0.072 ms
Execution time: 248807.285 ms
(9 rows)

On Mon, Feb 13, 2017 at 3:47 PM, David Hinkle <hinkle(at)cipafilter(dot)com> wrote:
> psql:postgres(at)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(dot)janes(at)gmail(dot)com> wrote:
>> On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle <hinkle(at)cipafilter(dot)com>
>> wrote:
>>>
>>> Thanks Jeff,
>>>
>>> No triggers or foreign key constrains:
>>>
>>> psql:postgres(at)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(at)cipafilter(dot)com
>
> Hours: Mon-Fri 8:00AM-5:00PM (CT)

--
David Hinkle

Senior Software Developer

Phone: 800.243.3729x3000

Email: hinkle(at)cipafilter(dot)com

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