Re: [GENERAL] Bad planning data resulting in OOM killing of postgres - Mailing list pgsql-general

I managed to get this version to finish:

psql:postgres@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@cipafilter.com> wrote:
> 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)



-- 
David Hinkle

Senior Software Developer

Phone:  800.243.3729x3000

Email:  hinkle@cipafilter.com

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