Something

SELECT t0.address, t1.address, COUNT(t2.time), MAX(t2.time) FROM Prelude_Alert AS top_table LEFT JOIN Prelude_Address AS t0 ON (t0._parent_type='S' AND t0._message_ident=top_table._ident AND t0._parent0_index = 0 AND t0._index != -1) LEFT JOIN Prelude_Address AS t1 ON (t1._parent_type='T' AND t1._message_ident=top_table._ident AND t1._parent0_index = 0 AND t1._index != -1) LEFT JOIN Prelude_CreateTime AS t2 ON (t2._parent_type='A' AND t2._message_ident=top_table._ident) WHERE t2.time >= '2007-03-09 10:52:13' AND t2.time < '2007-03-09 11:52:13' GROUP BY 1, 2 ORDER BY 4 DESC LIMIT 50



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1771864.90..1771864.94 rows=16 width=42)
   ->  Sort  (cost=1771864.90..1771864.94 rows=16 width=42)
         Sort Key: max(t2."time")
         ->  HashAggregate  (cost=1771864.34..1771864.58 rows=16 width=42)
               ->  Merge Join  (cost=1668080.32..1771845.15 rows=1919 width=42)
                     Merge Cond: ("outer"._ident = "inner"._message_ident)
                     ->  Merge Left Join  (cost=1664600.77..1763435.02 rows=1960612 width=42)
                           Merge Cond: ("outer"._ident = "inner"._message_ident)
                           ->  Merge Left Join  (cost=830777.20..902806.18 rows=1960612 width=25)
                                 Merge Cond: ("outer"._ident = "inner"._message_ident)
                                 ->  Index Scan using prelude_alert_pkey on prelude_alert top_table  (cost=0.00..45385.73 rows=1960612 width=8)
                                 ->  Sort  (cost=830777.20..834400.82 rows=1449448 width=25)
                                       Sort Key: t0._message_ident
                                       ->  Bitmap Heap Scan on prelude_address t0  (cost=32246.36..517087.41 rows=1449448 width=25)
                                             Recheck Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0))
                                             Filter: (_index <> -1)
                                             ->  Bitmap Index Scan on prelude_address_index_address  (cost=0.00..32246.36 rows=2666060 width=0)
                                                   Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0))
                           ->  Sort  (cost=833823.57..837474.20 rows=1460249 width=25)
                                 Sort Key: t1._message_ident
                                 ->  Bitmap Heap Scan on prelude_address t1  (cost=32486.56..517675.26 rows=1460249 width=25)
                                       Recheck Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0))
                                       Filter: (_index <> -1)
                                       ->  Bitmap Index Scan on prelude_address_index_address  (cost=0.00..32486.56 rows=2685926 width=0)
                                             Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0))
                     ->  Sort  (cost=3479.56..3484.49 rows=1972 width=16)
                           Sort Key: t2._message_ident
                           ->  Index Scan using prelude_createtime_index on prelude_createtime t2  (cost=0.00..3371.63 rows=1972 width=16)
                                 Index Cond: (((_parent_type)::text = 'A'::text) AND ("time" >= '2007-03-09 10:52:13'::timestamp without time zone) AND ("time" < '2007-03-09 11:52:13'::timestamp without time zone))