rendered paste bodyprelude=> SHOW default_statistics_target ; default_statistics_target--------------------------- 300prelude=> EXPLAIN ANALYZE 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1477730.84..1477730.96 rows=50 width=42) (actual time=273560.989..273561.015 rows=50 loops=1) -> Sort (cost=1477730.84..1477732.35 rows=604 width=42) (actual time=273560.987..273560.998 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=1477693.88..1477702.94 rows=604 width=42) (actual time=273560.583..273560.713 rows=133 loops=1) -> Merge Join (cost=1374841.90..1477676.16 rows=1772 width=42) (actual time=185917.302..273558.368 rows=1556 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=1371650.71..1469556.61 rows=1960612 width=42) (actual time=183416.523..272896.577 rows=1947114 loops=1) Merge Cond: ("outer"._ident = "inner"._message_ident) -> Merge Left Join (cost=682394.59..753756.76 rows=1960612 width=25) (actual time=90345.508..144142.370 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=0.015..21448.606 rows=1947032 loops=1) -> Sort (cost=682394.59..685907.45 rows=1405144 width=25) (actual time=90345.480..120326.794 rows=1943115 loops=1) Sort Key: t0._message_ident -> Bitmap Heap Scan on prelude_address t0 (cost=32113.33..516758.87 rows=1405144 width=25) (actual time=44973.475..78815.998 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..32113.33 rows=2654888 width=0) (actual time=44814.655..44814.655 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Sort (cost=689256.12..692863.54 rows=1442967 width=25) (actual time=93070.999..126460.013 rows=1944996 loops=1) Sort Key: t1._message_ident -> Bitmap Heap Scan on prelude_address t1 (cost=32977.11..518873.27 rows=1442967 width=25) (actual time=44788.924..81435.309 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..32977.11 rows=2726352 width=0) (actual time=44645.966..44645.966 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=130.760..131.145 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=91.182..128.873 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: 273674.921 msprelude=> set enable_mergejoin = false;SETprelude=> EXPLAIN ANALYZE 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; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=47764064.90..47764065.02 rows=50 width=42) (actual time=246770.981..246771.006 rows=50 loops=1) -> Sort (cost=47764064.90..47764066.41 rows=604 width=42) (actual time=246770.979..246770.988 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=47764027.94..47764037.00 rows=604 width=42) (actual time=246770.512..246770.643 rows=133 loops=1) -> Hash Join (cost=1068101.55..47764010.22 rows=1772 width=42) (actual time=240318.443..246768.783 rows=1556 loops=1) Hash Cond: ("outer"._ident = "inner"._message_ident) -> Hash Left Join (cost=1065004.42..47746190.77 rows=1960612 width=42) (actual time=167483.527..246029.979 rows=1960694 loops=1) Hash Cond: ("outer"._ident = "inner"._message_ident) -> Hash Left Join (cost=531249.73..24682581.36 rows=1960612 width=25) (actual time=84099.949..152252.779 rows=1960694 loops=1) Hash Cond: ("outer"._ident = "inner"._message_ident) -> Seq Scan on prelude_alert top_table (cost=0.00..35940.12 rows=1960612 width=8) (actual time=13.930..54708.636 rows=1960612 loops=1) -> Hash (cost=516758.87..516758.87 rows=1405144 width=25) (actual time=83013.800..83013.800 rows=1953299 loops=1) -> Bitmap Heap Scan on prelude_address t0 (cost=32113.33..516758.87 rows=1405144 width=25) (actual time=45529.813..79598.397 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..32113.33 rows=2654888 width=0) (actual time=45355.339..45355.339 rows=3906516 loops=1) Index Cond: (((_parent_type)::text = 'S'::text) AND (_parent0_index = 0)) -> Hash (cost=518873.27..518873.27 rows=1442967 width=25) (actual time=83383.408..83383.408 rows=1958487 loops=1) -> Bitmap Heap Scan on prelude_address t1 (cost=32977.11..518873.27 rows=1442967 width=25) (actual time=45999.049..79946.303 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..32977.11 rows=2726352 width=0) (actual time=45861.334..45861.334 rows=3916974 loops=1) Index Cond: (((_parent_type)::text = 'T'::text) AND (_parent0_index = 0)) -> Hash (cost=3092.58..3092.58 rows=1821 width=16) (actual time=114.164..114.164 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=81.311..112.626 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: 246779.774 msprelude=> EXPLAIN ANALYZE 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; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=276763301.61..276763301.74 rows=50 width=42) (actual time=258600.909..258600.935 rows=50 loops=1) -> Sort (cost=276763301.61..276763303.12 rows=604 width=42) (actual time=258600.907..258600.917 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=276763264.65..276763273.71 rows=604 width=42) (actual time=258600.513..258600.639 rows=133 loops=1) -> Nested Loop (cost=0.00..276763246.93 rows=1772 width=42) (actual time=7920.939..258597.028 rows=1556 loops=1) -> Nested Loop Left Join (cost=0.00..268237616.75 rows=1960612 width=42) (actual time=138.730..222484.933 rows=1960694 loops=1) -> Nested Loop Left Join (cost=0.00..134125309.70 rows=1960612 width=25) (actual time=101.421..147614.894 rows=1960694 loops=1) -> Index Scan using prelude_alert_pkey on prelude_alert top_table (cost=0.00..45385.73 rows=1960612 width=8) (actual time=16.370..7561.711 rows=1960612 loops=1) -> Index Scan using prelude_address_pkey on prelude_address t0 (cost=0.00..68.27 rows=9 width=25) (actual time=0.068..0.070 rows=1 loops=1960612) 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..68.28 rows=10 width=25) (actual time=0.034..0.036 rows=1 loops=1960694) Index Cond: (((t1._parent_type)::text = 'T'::text) AND (t1._message_ident = "outer"._ident) AND (t1._parent0_index = 0)) Filter: (_index <> -1) -> Index Scan using prelude_createtime_pkey on prelude_createtime t2 (cost=0.00..4.34 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1960694) Index Cond: (((t2._parent_type)::text = 'A'::text) AND (t2._message_ident = "outer"._ident)) Filter: (("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: 258601.113 msfast 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=251049.59..251049.72 rows=50 width=42) (actual time=774.624..774.650 rows=50 loops=1) -> Sort (cost=251049.59..251051.10 rows=604 width=42) (actual time=774.620..774.631 rows=50 loops=1) Sort Key: max(t2."time") -> HashAggregate (cost=251012.63..251021.69 rows=604 width=42) (actual time=774.142..774.264 rows=133 loops=1) -> Nested Loop Left Join (cost=0.00..250994.91 rows=1772 width=42) (actual time=286.576..770.174 rows=1556 loops=1) -> Nested Loop Left Join (cost=0.00..129784.29 rows=1772 width=33) (actual time=237.060..586.808 rows=1556 loops=1) -> Nested Loop (cost=0.00..8602.93 rows=1772 width=16) (actual time=110.671..227.522 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=72.934..97.417 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.081..0.082 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..68.27 rows=9 width=25) (actual time=0.228..0.229 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..68.28 rows=10 width=25) (actual time=0.115..0.116 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: 791.427 ms