Re: Memory usage after upgrade to 9.2.4

On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote:
> 2013/4/22 Daniel Cristian Cruz <danielcristian@gmail.com
> <mailto:danielcristian@gmail.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
>
>     server 9.2:
>     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'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'?

>
> Just for information, my schema uses one table that is inherited by all
> others tables; it is an audit record: creator, creation time, creator
> application, updater, update time, updater application, table name and
> record id.
>
> Thanks,
> --
> Daniel Cristian Cruz
> クルズ クリスチアン ダニエル


--
Adrian Klaver
adrian.klaver@gmail.com