Re: Memory usage after upgrade to 9.2.4

I've done an explain analyze under the test environment, and there is no
aggressive memory usage.

So I dropped the database in the new cluster and restored a fresh dump from
production (in theory, that's the difference between the two environments).

Some minutes after I got an answer: after a dump / restore, there is no
problem. The same cluster just a dump/restore.

Since I had no idea on what is the problem, and the structure from the dump
of the bad database is equal to the dump from the production (it differs
only in check constraints where "((turma.situacao)::text = ANY
((ARRAY['Aguardando Atualização'::character varying, 'Em
Andamento'::character varying])::text[])))" became "((turma.situacao)::text
= ANY (ARRAY[('Aguardando Atualização'::character varying)::text, ('Em
Andamento'::character varying)::text])))"), how can I report a issue in
pg_upgrade?

Thanks, Adrian, Tomas and Tom. It's a specific issue, it's on backup until
May, 1st. Until there, if someone would like to know something about it,
just ask me, I would like to help removing an issue.

2013/4/23 Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>

> 2013/4/23 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
>
>> On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote:
>>
>>> 2013/4/22 Daniel Cristian Cruz <danielcristian(at)gmail(dot)com
>>> <mailto:danielcristian(at)gmail(dot)**com <danielcristian(at)gmail(dot)com>>>
>>>
>>>
>>> query1:
>>> EXPLAIN ANALYZE SELECT ced.id_evento, ced.inicio, ced.termino,
>>> ced.evento, ced.id_eventos IS NOT NULL AS aula_dividida, ac.titulo,
>>> ced.id_tipo_evento, ced.tipo_evento, ac.media_referencia, p.nome,
>>> ef.nome AS nomeEspacoFisico, ( SELECT count ( pre2.presente ) > 0
>>> FROM turma.presenca pre2 WHERE pre2.id_aula = ac.id_aula AND
>>> pre2.id_evento = ac.id_evento AND pre2.id_diario = '64469' ) AS
>>> presenca, ced.id_aula FROM recurso.consulta_evento_diario ced LEFT
>>> JOIN recurso.evento e USING ( id_evento ) LEFT JOIN
>>> recurso.espaco_fisico ef USING ( id_espaco_fisico ) LEFT JOIN
>>> turma.aula_calendario ac USING ( id_aula, id_evento ) LEFT JOIN
>>> recurso.evento_participante ep USING ( id_evento ) LEFT JOIN
>>> senai.pessoa p USING ( id_pessoa ) WHERE id_diario = '64469' AND
>>> ced.id_evento NOT IN ( SELECT ec.id_evento_sobreposto FROM
>>> recurso.evento_conflito ec WHERE ec.id_evento_sobreposto =
>>> ced.id_evento AND ec.ignorado IS NULL ) AND ced.inicio BETWEEN
>>> '2013-04-14 00:00:00' AND '2013-04-20 23:59:59.999999' ORDER BY
>>> inicio;
>>>
>>> server 9.1:
>>> http://explain.depesz.com/s/**fmM <http://explain.depesz.com/s/fmM>
>>>
>>> server 9.2:
>>> http://explain.depesz.com/s/**wXm <http://explain.depesz.com/s/wXm>
>>>
>>> After run this one on server 9.2, RES memory reached 6.5GB, VIRT
>>> 15GB.
>>>
>>>
>>> Since there is no response, is this memory usage normal? The same query
>>> on version 9.1 doesn't use that much memory.
>>>
>>
>> Not sure how it applies but I noticed that a GroupAggregate in 9.1 that
>> took 1.22 secs became a a HashAggregate in the 9.2 query and took 12.54
>> secs.
>
>
> I used to read a explain and find something, but this one is huge.
> Unfortunately I'm still working on data migration from the 9.2 to 9.1 and
> didn't get time to read it in detail...
>
> I'm concerned about this because there is just only one report like
>>> that. Does someone else has the same pattern when using inherited tables?
>>>
>>
>> Also noticed that in your 9.2 production conf:
>>
>> (no constraint_exclusion set)
>>
>> Does this mean the default of 'partition' was left as is or that the
>> setting was set to 'off'?
>
>
> No, default:
>
> senai=# show constraint_exclusion ;
> constraint_exclusion
> ----------------------
> partition
> (1 row)
>
> --
> Daniel Cristian Cruz
> クルズ クリスチアン ダニエル
>

--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル