rendered paste body Sort (cost=33417.68..33417.69 rows=4 width=98)
Sort Key: (now() - (events.createdat)::timestamp with time zone)
-> Nested Loop (cost=427.13..33417.64 rows=4 width=98)
-> Hash Join (cost=427.13..33415.56 rows=1 width=102)
Hash Cond: ("outer".eventid = "inner".parentid)
-> Hash Join (cost=2.83..15988.66 rows=242894 width=76)
Hash Cond: ("outer".statusid = "inner".id)
-> Hash Join (cost=1.73..12344.15 rows=242894 width=69)
Hash Cond: ("outer".regionid = "inner".id)
-> Seq Scan on mortgages (cost=0.00..8693.32 rows=244032 width=67)
-> Hash (cost=1.58..1.58 rows=58 width=10)
-> Seq Scan on regions (cost=0.00..1.58 rows=58 width=10)
-> Hash (cost=1.08..1.08 rows=8 width=15)
-> Seq Scan on statustypes (cost=0.00..1.08 rows=8 width=15)
-> Hash (cost=423.64..423.64 rows=263 width=38)
-> Hash Join (cost=411.09..423.64 rows=263 width=38)
Hash Cond: ("outer".createdbyid = "inner".id)
-> Merge Join (cost=399.36..407.97 rows=263 width=32)
Merge Cond: ("outer".parentid = "inner".parentid)
-> Sort (cost=210.00..211.69 rows=677 width=20)
Sort Key: events.parentid
-> Hash Join (cost=11.94..178.17 rows=677 width=20)
Hash Cond: ("outer".associateid = "inner".id)
-> Seq Scan on events (cost=0.00..147.46 rows=2401 width=20)
Filter: (typeid = 2)
-> Hash (cost=11.72..11.72 rows=84 width=8)
-> Seq Scan on users (cost=0.00..11.72 rows=84 width=8)
Filter: (25 = associationid)
-> Sort (cost=189.36..190.66 rows=519 width=12)
Sort Key: count.parentid
-> Subquery Scan count (cost=159.47..165.96 rows=519 width=12)
-> HashAggregate (cost=159.47..160.77 rows=519 width=4)
-> Seq Scan on events (cost=0.00..147.46 rows=2401 width=4)
Filter: (typeid = 2)
-> Hash (cost=10.98..10.98 rows=298 width=14)
-> Seq Scan on users source (cost=0.00..10.98 rows=298 width=14)
-> Seq Scan on associations (cost=0.00..2.00 rows=2 width=4)
Filter: (id = 25)
(38 rows)
SELECT
'1.'||leads.mortgages.id AS tab_leadid,
(now()-leads.events.createdat)::interval AS age,
leads.mortgages.firstname AS first,
leads.mortgages.lastname AS last,
leads.mortgages.number AS num,
core.regions.name AS st,
leads.mortgages.loan_amount AS loan,
leads.mortgages.interest_rate AS interest,
leads.statustypes.name AS stat,
leads.events.createdat AS date,
source.username AS src,
(count.count - 1) AS count
FROM leads.events
INNER JOIN core.users
ON core.users.id = leads.events.associateid
INNER JOIN core.associations
ON core.associations.id = core.users.associationid
INNER JOIN leads.mortgages
ON leads.mortgages.eventid = leads.events.parentid
INNER JOIN core.users AS source
ON source.id = leads.events.createdbyid
INNER JOIN core.regions
ON core.regions.id = leads.mortgages.regionid
INNER JOIN leads.statustypes
ON leads.statustypes.id = leads.mortgages.statusid
INNER JOIN (
SELECT count(*), parentid FROM leads.events
WHERE leads.events.typeid = 2
GROUP BY leads.events.parentid
) AS count
ON count.parentid = leads.mortgages.eventid
WHERE leads.events.typeid = 2
AND core.associations.id = 25 -- assigned to winnipeg
ORDER BY age;