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(at)gmail(dot)com
> <mailto: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
>
> 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(dot)klaver(at)gmail(dot)com