On 04/23/2013 04:23 AM, Daniel Cristian Cruz wrote:
2013/4/22 Daniel Cristian Cruz <danielcristian@xxxxxxxxx
<mailto:danielcristian@xxxxxxxxx>>
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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
