On 06/12/2012 09:30, Matheus de Oliveira wrote:
> 2012/12/6 Thiago <[email protected]
> <mailto:[email protected]>>
>
> On 05/12/2012 17:16, Matheus de Oliveira wrote:
> >
> >
> > 2012/12/5 Thiago <[email protected]
> <mailto:[email protected]>
> > <mailto:[email protected]
> <mailto:[email protected]>>>
> >
> > Boa tarde.
> >
> > Tenho as seguintes tabelas:
> >
> > tb_mensagem
> > id
> > id_mensagem_pai
> > dth_insert
> >
> > tb_mensagem_leitura
> > id_mensagem
> > dth_insert
> >
> > A tabela de mensagens, sempre que existe uma mensagem com
> > id_mensagem_pai, significa que é uma resposta, as mensagens
> originais
> > (primeiras) contém 0 (zero) no campo id_mensagem_pai.
> >
> > Na tabela tb_mensagem_leitura eu tenho um histórico de todas
> as vezes
> > que o usuário leu a mensagem, sendo gravado no campo
> id_mensagem dessa
> > tabela sempre o id da mensagem original.
> >
> > Preciso identificar quantas mensagens não lidas existe na para o
> > usuário, sendo que uma mensagem não lida é:
> > 1) sem registro na tabela tb_mensagem_leitura;
> > 2) o registro da tabela tb_mensagem_leitura é com data menor
> que a
> > última resposta da mensagem original.
> >
> > Alguém poderia me ajudar com este select? Ou até mesmo com a
> forma em
> > que estruturei as tabelas, pois para chegar neste resultado
> está um
> > pouco complicado.
> >
> >
> > Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu
> > problema (não testado):
> >
> > WITH RECURSIVE mensagens AS (
> > SELECT id AS id_original, id, dth_insert
> > FROM tb_mensagem
> > WHERE id_mensagem_pai = 0
> > UNION ALL
> > SELECT pai.id_original, filha.id <http://filha..id>
> <http://filha.id>, filha.dth_insert
> > FROM tb_mensagem AS filha
> > INNER JOIN mensagens AS pai ON pai.id <http://pai.id>
> <http://pai.id> =
> > filha.id_mensagem_pai
> > ), ultimas AS (
> > SELECT m.id_original, MAX(m.dth_insert) AS dth_insert
> > FROM mensagens AS m
> > GROUP BY m.id_original
> > )
> > SELECT COUNT(*)
> > FROM ultimas AS u
> > LEFT JOIN tb_mensagem_leitura AS l
> > ON l.id_mensagem = u.id_original
> > WHERE l.id_mensagem IS NULL OR l.dth_insert < u.dth_insert;
> >
> >
> > Não me parece muito performático, uma forma de melhorar seria SEMPRE
> > guardar o id da mensagem original em tb_mensagem (não só a da
> pai, mas a
> > da primeira na hierarquia), o que evitaria toda essa sobrecarga
> da query
> > recursiva e da outra sub-query, que na prática só estão pegando a
> data
> > da última mensagem.
> >
> > Atenciosamente,
>
> Mateus, muito obrigado pela resposta.
>
> Acho que não expliquei direito.
>
>
> Também acho... =P
>
> Na tabela tb_mensagem, o campo
> id_mensagem_pai é sempre o id da mensagem original. Todas as respostas
> estão amarradas a uma única mensagem e não em um formato de árvore.
>
>
> Agora entendi, é mais simples então.
>
>
> Dessa forma você acha que existe uma solução melhor com uma melhor
> performance? Essa será uma leitura muitas vezes executada, pois cada
> página do portal que o usuário acessa essa consulta é refeita para
> atualizar as mensagens não lidas.
>
>
>
> Sim. A forma abaixo:
>
> SELECT COUNT(*) FROM (
> SELECT
> CASE m.id_mensagem_pai WHEN 0 THEN m.id <http://m.id> ELSE
> m.id_mensagem_pai END AS id_original,
> MAX(m.dth_insert) AS dth_insert
> FROM tb_mensagem AS m
> GROUP BY CASE m.id_mensagem_pai WHEN 0 THEN m.id <http://m.id> ELSE
> m.id_mensagem_pai END
> ) AS u
> LEFT JOIN tb_mensagem_leitura AS l
> ON l.id_mensagem = u.id_original
> WHERE l.id_mensagem IS NULL OR l.dth_insert < u.dth_insert;
Não deu certo pelo fato de que na tabela de leitura eu vou ter n
registros de leitura, por esse motivo o join está multiplicando pelas
quantidades de leitura.
> Eu modelaria na mensagem original o id_mensagem_pai = id ao invés de 0
> na mensagem original, assim tira esse CASE daí.
Puxa, essa idéia foi ótima, ajudou e muito.
Eu cheguei a duas formas de fazer tal consulta:
1)
select count(*) from (
select
cr.id_mensagem_pai as id_mensagem,
max(cr.dth_insert) as dth_insert
from portal.tb_correio cr
where cr.id_usuario_des = 2
group by cr.id_mensagem_pai
) as cr
left join (
select hl.id_mensagem,max(hl.dth_insert) as dth_insert
from portal.tb_correio_historico_leitura hl
where hl.id_usuario = 2
group by hl.id_mensagem
) as hl on hl.id_mensagem = cr.id_mensagem
where hl.id_mensagem is null or hl.dth_insert < cr.dth_insert
2)
select count(*) from (
select
cr.id_mensagem_pai as id_mensagem,
max(cr.dth_insert) as dth_insert
from portal.tb_correio cr
where cr.id_usuario_des = 2
group by cr.id_mensagem_pai
) as cr
where
(
select hl.id
from portal.tb_correio_historico_leitura hl
where
hl.id_mensagem = cr.id_mensagem and
hl.dth_insert > cr.dth_insert and
hl.id_usuario = 2
limit 1
) is null
Abaixo segue o explain de cada uma deles (coisa que eu nunca entendi).
1)
QUERY PLAN
Aggregate (cost=5.18..5.19 rows=1 width=0)
-> Hash Left Join (cost=4.68..5.16 rows=6 width=0)
Hash Cond: (cr.id_mensagem_pai = hl.id_mensagem)
Filter: ((hl.id_mensagem IS NULL) OR ((max(hl.dth_insert)) <
(max(cr.dth_insert))))
-> HashAggregate (cost=3.08..3.30 rows=17 width=12)
-> Seq Scan on tb_correio cr (cost=0.00..2.98 rows=22
width=12)
Filter: (id_usuario_des = 2)
-> Hash (cost=1.55..1.55 rows=3 width=12)
-> HashAggregate (cost=1.49..1.52 rows=3 width=12)
-> Seq Scan on tb_correio_historico_leitura hl
(cost=0.00..1.44 rows=10 width=12)
Filter: (id_usuario = 2)
2)
QUERY PLAN
Aggregate (cost=30.88..30.89 rows=1 width=0)
-> Subquery Scan cr (cost=3.08..30.88 rows=1 width=0)
Filter: ((SubPlan 1) IS NULL)
-> HashAggregate (cost=3.08..3.30 rows=17 width=12)
-> Seq Scan on tb_correio cr (cost=0.00..2.98 rows=22
width=12)
Filter: (id_usuario_des = 2)
SubPlan 1
-> Limit (cost=0.00..1.61 rows=1 width=4)
-> Seq Scan on tb_correio_historico_leitura hl
(cost=0.00..1.61 rows=1 width=4)
Filter: ((dth_insert > $1) AND (id_mensagem = $0)
AND (id_usuario = 2))
Qual das formas será que terá melhor desempenho?
Obrigado!
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
