rendered paste bodySELECT 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 50explain analyze output:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1771864.90..1771864.94 rows=16 width=42) (actual time=231233.299..231233.338 rows=50 loops=1) -> Sort (cost=1771864.90..1771864.94 rows=16 width=42) (actual time=231233.297..231233.315 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=1771864.34..1771864.58 rows=16 width=42) (actual time=231232.670..231232.853 rows=133 loops=1) -> Merge Join (cost=1668080.32..1771845.15 rows=1919 width=42) (actual time=143418.179..231230.540 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=1664600.77..1763435.02 rows=1960612 width=42) (actual time=140815.174..230531.083 rows=1947114 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=830777.20..902806.18 rows=1960612 width=25) (actual time=71583.365..127267.700 rows=1947114 loops=1) 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) (actual time=11.919..20127.718 rows=1947032 loops=1) -> Sort (cost=830777.20..834400.82 rows=1449448 width=25) (actual time=71571.434..104661.819 rows=1943115 loops=1) Sort Key: t0._message_ident -> Bitmap Heap Scan on prelude_address t0 (cost=32246.36..517087.41 rows=1449448 width=25) (actual time=25295.354..61881.225 rows=1953299 loops=1) 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) (actual time=25265.752..25265.752 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Sort (cost=833823.57..837474.20 rows=1460249 width=25) (actual time=69231.799..101040.433 rows=1944996 loops=1) Sort Key: t1._message_ident -> Bitmap Heap Scan on prelude_address t1 (cost=32486.56..517675.26 rows=1460249 width=25) (actual time=21513.229..59289.124 rows=1958487 loops=1) 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) (actual time=21466.907..21466.907 rows=3916974 loops=1) Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0)) -> Sort (cost=3479.56..3484.49 rows=1972 width=16) (actual time=126.249..126.599 rows=1556 loops=1) Sort Key: t2._message_ident -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3371.63 rows=1972 width=16) (actual time=85.597..123.163 rows=1556 loops=1) 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)) Total runtime: 231399.145 msexplain analyze with work_mem=8192, effective_cache_size=100000 and shared_buffers=16384----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1545902.98..1545903.02 rows=17 width=42) (actual time=280342.025..280352.585 rows=50 loops=1) -> Sort (cost=1545902.98..1545903.02 rows=17 width=42) (actual time=280342.023..280342.038 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=1545902.37..1545902.63 rows=17 width=42) (actual time=280341.298..280341.424 rows=133 loops=1) -> Merge Join (cost=1439580.89..1545884.65 rows=1772 width=42) (actual time=193234.235..280339.028 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=1436389.69..1537765.10 rows=1960612 width=42) (actual time=190628.151..279633.910 rows=1947114 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=738814.84..812865.50 rows=1960612 width=25) (actual time=98436.798..151293.804 rows=1947114 loops=1) 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) (actual time=27.367..21083.326 rows=1947032 loops=1) -> Sort (cost=738814.84..742776.04 rows=1584479 width=25) (actual time=98409.408..127868.644 rows=1943115 loops=1) Sort Key: t0._message_ident -> Bitmap Heap Scan on prelude_address t0 (cost=35710.67..525563.46 rows=1584479 width=25) (actual time=48565.877..86570.402 rows=1953299 loops=1) 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..35710.67 rows=2952445 width=0) (actual time=48413.923..48413.923 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Sort (cost=697574.85..701312.65 rows=1495120 width=25) (actual time=92191.341..126094.592 rows=1944996 loops=1) Sort Key: t1._message_ident -> Bitmap Heap Scan on prelude_address t1 (cost=33696.63..520635.54 rows=1495120 width=25) (actual time=45640.668..80507.115 rows=1958487 loops=1) 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..33696.63 rows=2785938 width=0) (actual time=45480.201..45480.201 rows=3916974 loops=1) Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0)) -> Sort (cost=3191.19..3195.75 rows=1821 width=16) (actual time=156.087..156.458 rows=1556 loops=1) Sort Key: t2._message_ident -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3092.58 rows=1821 width=16) (actual time=125.140..154.208 rows=1556 loops=1) 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)) Total runtime: 280496.491 msanother query:prelude=> explain analyze SELECT t0.address, t1.address, COUNT(t2.time), MAX(t2.time) FROM Prelude_CreateTime as t2 JOIN Prelude_Alert as top_table ON (t2._parent_type='A' AND t2._message_ident=top_table._ident) 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) 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1460538.30..1460538.34 rows=17 width=42) (actual time=189181.325..189202.431 rows=50 loops=1) -> Sort (cost=1460538.30..1460538.34 rows=17 width=42) (actual time=189181.323..189181.332 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=1460537.70..1460537.95 rows=17 width=42) (actual time=189180.935..189181.060 rows=133 loops=1) -> Merge Left Join (cost=1445088.23..1460519.98 rows=1772 width=42) (actual time=184401.247..189179.057 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=747513.38..755455.16 rows=1772 width=33) (actual time=93014.156..95242.059 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Sort (cost=8698.54..8702.97 rows=1772 width=16) (actual time=16.586..16.907 rows=1556 loops=1) Sort Key: top_table._ident -> Nested Loop (cost=0.00..8602.93 rows=1772 width=16) (actual time=0.048..13.885 rows=1556 loops=1) -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3092.58 rows=1821 width=16) (actual time=0.035..2.088 rows=1556 loops=1) 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)) -> Index Scan using prelude_alert_pkey on prelude_alert top_table (cost=0.00..3.01 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1556) Index Cond: ("outer"._message_ident = top_table._ident) -> Sort (cost=738814.84..742776.04 rows=1584479 width=25) (actual time=92897.548..94659.217 rows=1943115 loops=1) Sort Key: t0._message_ident -> Bitmap Heap Scan on prelude_address t0 (cost=35710.67..525563.46 rows=1584479 width=25) (actual time=44639.243..81020.246 rows=1953299 loops=1) 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..35710.67 rows=2952445 width=0) (actual time=44417.835..44417.835 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Sort (cost=697574.85..701312.65 rows=1495120 width=25) (actual time=91250.030..93371.417 rows=1944914 loops=1) Sort Key: t1._message_ident -> Bitmap Heap Scan on prelude_address t1 (cost=33696.63..520635.54 rows=1495120 width=25) (actual time=44966.371..79724.554 rows=1958487 loops=1) 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..33696.63 rows=2785938 width=0) (actual time=44820.608..44820.608 rows=3916974 loops=1) Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0)) Total runtime: 189369.618 msAfter:CREATE INDEX prelude_address_message_ident ON prelude_address (_message_ident);ANALYZE prelude_address ;prelude=> explain analyze SELECT t0.address, t1.address, COUNT(t2.time), MAX(t2.time) FROM Prelude_CreateTime as t2 JOIN Prelude_Alert as top_table ON (t2._parent_type='A' AND t2._message_ident=top_table._ident) 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) 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1410419.02..1410419.06 rows=15 width=42) (actual time=188928.052..188940.803 rows=50 loops=1) -> Sort (cost=1410419.02..1410419.06 rows=15 width=42) (actual time=188928.050..188928.061 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=1410418.50..1410418.73 rows=15 width=42) (actual time=188927.616..188927.736 rows=133 loops=1) -> Merge Left Join (cost=1395769.87..1410400.78 rows=1772 width=42) (actual time=184155.707..188925.690 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=701648.28..708949.86 rows=1772 width=33) (actual time=92119.610..94510.632 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Sort (cost=8698.54..8702.97 rows=1772 width=16) (actual time=349.206..349.580 rows=1556 loops=1) Sort Key: top_table._ident -> Nested Loop (cost=0.00..8602.93 rows=1772 width=16) (actual time=115.706..346.537 rows=1556 loops=1) -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3092.58 rows=1821 width=16) (actual time=71.373..105.114 rows=1556 loops=1) 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)) -> Index Scan using prelude_alert_pkey on prelude_alert top_table (cost=0.00..3.01 rows=1 width=8) (actual time=0.153..0.154 rows=1 loops=1556) Index Cond: ("outer"._message_ident = top_table._ident) -> Sort (cost=692949.74..696590.85 rows=1456442 width=25) (actual time=91670.988..93602.243 rows=1943115 loops=1) Sort Key: t0._message_ident -> Bitmap Heap Scan on prelude_address t0 (cost=33800.12..520874.65 rows=1456442 width=25) (actual time=45975.617..79513.842 rows=1953299 loops=1) 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..33800.12 rows=2793687 width=0) (actual time=45756.060..45756.060 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Sort (cost=694121.59..697778.76 rows=1462868 width=25) (actual time=91967.779..93843.871 rows=1944914 loops=1) Sort Key: t1._message_ident -> Bitmap Heap Scan on prelude_address t1 (cost=33949.07..521239.28 rows=1462868 width=25) (actual time=44827.943..80523.262 rows=1958487 loops=1) 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..33949.07 rows=2806012 width=0) (actual time=44688.665..44688.665 rows=3916974 loops=1) Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0)) Total runtime: 189099.611 msprelude=> set enable_mergejoin = false;SETprelude=> explain analyze SELECT t0.address, t1.address, COUNT(t2.time), MAX(t2.time) FROM Prelude_CreateTime as t2 JOIN Prelude_Alert as top_table ON (t2._parent_type='A' AND t2._message_ident=top_table._ident) 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) 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2510931.93..2510931.97 rows=15 width=42) (actual time=969.464..969.492 rows=50 loops=1) -> Sort (cost=2510931.93..2510931.97 rows=15 width=42) (actual time=969.461..969.470 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=2510931.41..2510931.64 rows=15 width=42) (actual time=969.054..969.164 rows=133 loops=1) -> Nested Loop Left Join (cost=0.00..2510913.69 rows=1772 width=42) (actual time=367.862..965.314 rows=1556 loops=1) -> Nested Loop Left Join (cost=0.00..1256440.14 rows=1772 width=33) (actual time=324.393..773.315 rows=1556 loops=1) -> Nested Loop (cost=0.00..8602.93 rows=1772 width=16) (actual time=212.878..407.414 rows=1556 loops=1) -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3092.58 rows=1821 width=16) (actual time=143.668..168.926 rows=1556 loops=1) 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)) -> Index Scan using prelude_alert_pkey on prelude_alert top_table (cost=0.00..3.01 rows=1 width=8) (actual time=0.151..0.151 rows=1 loops=1556) Index Cond: ("outer"._message_ident = top_table._ident) -> Index Scan using prelude_address_pkey on prelude_address t0 (cost=0.00..702.97 rows=98 width=25) (actual time=0.232..0.233 rows=1 loops=1556) Index Cond: (((t0._parent_type)::text = 'S'::text) AND (t0._message_ident = "outer"._ident) AND (t0._parent0_index = 0)) Filter: (_index <> -1) -> Index Scan using prelude_address_pkey on prelude_address t1 (cost=0.00..706.72 rows=98 width=25) (actual time=0.120..0.122 rows=1 loops=1556) Index Cond: (((t1._parent_type)::text = 'T'::text) AND (t1._message_ident = "outer"._ident) AND (t1._parent0_index = 0)) Filter: (_index <> -1) Total runtime: 969.677 msdefault_statistics_target to 300ANALYZE prelude_address ;prelude=> explain analyze SELECT t0.address, t1.address, COUNT(t2.time), MAX(t2.time) FROM Prelude_CreateTime as t2 JOIN Prelude_Alert as top_table ON (t2._parent_type='A' AND t2._message_ident=top_table._ident) 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) 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=94559.82..94559.95 rows=50 width=42) (actual time=876.450..922.695 rows=50 loops=1) -> Sort (cost=94559.82..94564.25 rows=1772 width=42) (actual time=876.447..876.458 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=94437.63..94464.21 rows=1772 width=42) (actual time=875.958..876.076 rows=133 loops=1) -> Nested Loop Left Join (cost=0.00..94419.91 rows=1772 width=42) (actual time=269.929..872.068 rows=1556 loops=1) -> Nested Loop Left Join (cost=0.00..51511.39 rows=1772 width=33) (actual time=222.666..698.576 rows=1556 loops=1) -> Nested Loop (cost=0.00..8602.93 rows=1772 width=16) (actual time=120.250..298.895 rows=1556 loops=1) -> Index Scan using prelude_createtime_index on prelude_createtime t2 (cost=0.00..3092.58 rows=1821 width=16) (actual time=75.111..100.747 rows=1556 loops=1) 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)) -> Index Scan using prelude_alert_pkey on prelude_alert top_table (cost=0.00..3.01 rows=1 width=8) (actual time=0.125..0.125 rows=1 loops=1556) Index Cond: ("outer"._message_ident = top_table._ident) -> Index Scan using prelude_address_pkey on prelude_address t0 (cost=0.00..24.18 rows=3 width=25) (actual time=0.254..0.255 rows=1 loops=1556) Index Cond: (((t0._parent_type)::text = 'S'::text) AND (t0._message_ident = "outer"._ident) AND (t0._parent0_index = 0)) Filter: (_index <> -1) -> Index Scan using prelude_address_pkey on prelude_address t1 (cost=0.00..24.18 rows=3 width=25) (actual time=0.108..0.110 rows=1 loops=1556) Index Cond: (((t1._parent_type)::text = 'T'::text) AND (t1._message_ident = "outer"._ident) AND (t1._parent0_index = 0)) Filter: (_index <> -1) Total runtime: 923.612 ms