Apply optimization to only compute unique edges in CTE
@@ -30,13 +30,13 @@ select * from (
weights(we, score) as (
select e, sum(score) from (
select e, pid1, pid2, max(score) as score from (
- select e, 1 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from edges, message p1, message p2 where (p1.m_creatorid=e[1] and p2.m_creatorid=e[2] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is null)
+ select e, 1 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from unique_edges, message p1, message p2 where (p1.m_creatorid=e[1] and p2.m_creatorid=e[2] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is null)
union all
- select e, 1 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from edges, message p1, message p2 where (p1.m_creatorid=e[2] and p2.m_creatorid=e[1] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is null)
+ select e, 1 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from unique_edges, message p1, message p2 where (p1.m_creatorid=e[2] and p2.m_creatorid=e[1] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is null)
union all
- select e, 0.5 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from edges, message p1, message p2 where (p1.m_creatorid=e[1] and p2.m_creatorid=e[2] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is not null)
+ select e, 0.5 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from unique_edges, message p1, message p2 where (p1.m_creatorid=e[1] and p2.m_creatorid=e[2] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is not null)
union all
- select e, 0.5 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from edges, message p1, message p2 where (p1.m_creatorid=e[2] and p2.m_creatorid=e[1] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is not null)
+ select e, 0.5 as score, p1.m_messageid as pid1, p2.m_messageid as pid2 from unique_edges, message p1, message p2 where (p1.m_creatorid=e[2] and p2.m_creatorid=e[1] and p2.m_c_replyof=p1.m_messageid and p1.m_c_replyof is not null)
) pps group by e, pid1, pid2
) tmp
group by e
