TABLES
********

                                        Table "log.msg_info"
   Column   |            Type             |                        Modifiers
------------+-----------------------------+----------------------------------------------------------
 msgid      | bigint                      | not null default nextval('log.msg_info_msgid_seq'::text)
 sender     | character varying(255)      |
 subject    | text                        |
 size       | bigint                      |
 entry_time | timestamp without time zone | default now()
 source_ip  | cidr                        |
 origin     | smallint                    |
Indexes:
    "msg_info_pkey" primary key, btree (msgid)
    "ddindx" btree (date(entry_time))
    "msg_info_entry_time" btree (entry_time)
    "msg_info_sender_index" btree (sender)
    "msg_info_size" btree (size)
    "msg_info_subject" btree (subject)

                                         Table "log.msg_fate"
   Column    |          Type          |                           Modifiers
-------------+------------------------+----------------------------------------------------------------
 msgid       | bigint                 | not null default nextval('log.msg_fate_msgid_seq'::text)
 grp_fate_id | bigint                 | not null default nextval('log.msg_fate_grp_fate_id_seq'::text)
 modid       | integer                |
 description | character varying(255) |
 rule_origin | bigint                 |
 action      | smallint               |
 ruleid      | integer                |
Indexes:
    "msg_fate_pkey" primary key, btree (grp_fate_id)
    "msg_fate_action" btree ("action")
    "msg_fate_description" btree (description)
    "msg_fate_modid" btree (modid)
    "msg_fate_msgid" btree (msgid)
Foreign-key constraints:
    "msgid_fkey" FOREIGN KEY (msgid) REFERENCES log.msg_info(msgid) ON UPDATE CASCADE ON DELETE CASCADE

              Table "log.msg_fate_recipients"
   Column    |            Type             |   Modifiers
-------------+-----------------------------+---------------
 grp_fate_id | bigint                      |
 recipient   | character varying(255)      |
 update_at   | timestamp without time zone | default now()
 last_action | integer                     |
 zone_id     | integer                     |
 direction   | smallint                    | default 7
Indexes:
    "msg_fate_recipients_grp_fate_id" btree (grp_fate_id)
    "msg_fate_recipients_last_action_idx" btree (last_action)
    "msg_fate_recipients_recipient_idx" btree (recipient)
    "msg_fate_recipients_update_at" btree (update_at)
    "msg_fate_recipients_zone_id" btree (zone_id)
Triggers:
    stats_for_domain AFTER INSERT ON log.msg_fate_recipients FOR EACH ROW EXECUTE PROCEDURE log.collect_stats_for_domain()
    stats_for_object AFTER INSERT ON log.msg_fate_recipients FOR EACH ROW EXECUTE PROCEDURE log.collect_stats_for_object()
    update_timestamp_last_action BEFORE UPDATE ON log.msg_fate_recipients FOR EACH ROW EXECUTE PROCEDURE log.recipients_status_changed_update()

                                      Table "pineapp.zones"
  Column   |          Type          |                          Modifiers
-----------+------------------------+-------------------------------------------------------------
 zone_id   | integer                | not null default nextval('pineapp.zones_zone_id_seq'::text)
 zone_name | character varying(20)  |
 zone_desc | character varying(255) |
 zone_type | smallint               |
Indexes:
    "zones_pkey" primary key, btree (zone_id)
    "zones_zone_id" btree (zone_id)

QUERIES
***********


1)
explain analyze SELECT date_trunc('hour'::text, i.entry_time) AS datetime,
COUNT(fr.grp_fate_id) ,
SUM(i.size)
FROM log.msg_info as i,log.msg_fate as f, log.msg_fate_recipients as fr
WHERE i.origin = 1
AND i.msgid=f.msgid
AND i.entry_time > '2006-01-25'
AND f.grp_fate_id=fr.grp_fate_id
GROUP BY datetime
order by datetime;

                                                                         QUERY PLAN                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1355984.84..1417243.22 rows=1485233 width=24) (actual time=257433.784..269102.088 rows=623 loops=1)
   ->  Sort  (cost=1355984.84..1368514.62 rows=5011913 width=24) (actual time=257349.038..261012.595 rows=5160187 loops=1)
         Sort Key: date_trunc('hour'::text, i.entry_time)
         ->  Hash Join  (cost=256729.52..667400.86 rows=5011913 width=24) (actual time=63133.140..208966.342 rows=5160187 loops=1)
               Hash Cond: ("outer".grp_fate_id = "inner".grp_fate_id)
               ->  Seq Scan on msg_fate_recipients fr  (cost=0.00..178230.71 rows=9022771 width=8) (actual time=30.347..59826.978 rows=9022771 loops=1)
               ->  Hash  (cost=243787.17..243787.17 rows=1548139 width=24) (actual time=62780.964..62780.964 rows=0 loops=1)
                     ->  Hash Join  (cost=95375.28..243787.17 rows=1548139 width=24) (actual time=13791.952..61022.913 rows=1601121 loops=1)
                           Hash Cond: ("outer".msgid = "inner".msgid)
                           ->  Seq Scan on msg_fate f  (cost=0.00..55203.60 rows=2787060 width=16) (actual time=15.690..17470.531 rows=2787060 loops=1)
                           ->  Hash  (cost=82959.20..82959.20 rows=1485233 width=24) (actual time=13166.273..13166.273 rows=0 loops=1)
                                 ->  Seq Scan on msg_info i  (cost=0.00..82959.20 rows=1485233 width=24) (actual time=0.133..11450.846 rows=1487886 loops=1)
                                       Filter: ((origin = 1) AND (entry_time > '2006-01-25 00:00:00'::timestamp without time zone))
 Total runtime: 269486.952 ms
(14 rows)



2)
explain analyze SELECT date_trunc('hour'::text, i.entry_time) AS datetime, 
COUNT(fr.grp_fate_id) , 
SUM(i.size) 
FROM log.msg_info as i,log.msg_fate as f, log.msg_fate_recipients as fr 
WHERE i.msgid=f.msgid 
AND f.grp_fate_id=fr.grp_fate_id
AND i.entry_time > '2006-01-25'
AND f.modid = -2 
AND i.origin=1
GROUP BY datetime order by datetime

                                                                          QUERY PLAN                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=1357798.20..1418772.58 rows=1485233 width=24) (actual time=244424.064..255666.899 rows=623 loops=1)
   ->  Sort  (cost=1357798.20..1370256.98 rows=4983513 width=24) (actual time=244400.858..247951.856 rows=5135031 loops=1)
         Sort Key: date_trunc('hour'::text, i.entry_time)
         ->  Hash Join  (cost=263144.62..673408.98 rows=4983513 width=24) (actual time=61486.605..194229.987 rows=5135031 loops=1)
               Hash Cond: ("outer".grp_fate_id = "inner".grp_fate_id)
               ->  Seq Scan on msg_fate_recipients fr  (cost=0.00..178230.71 rows=9022771 width=8) (actual time=29.106..47411.313 rows=9022771 loops=1)
               ->  Hash  (cost=250276.20..250276.20 rows=1539367 width=24) (actual time=61213.847..61213.847 rows=0 loops=1)
                     ->  Hash Join  (cost=95375.28..250276.20 rows=1539367 width=24) (actual time=13756.430..59486.997 rows=1586472 loops=1)
                           Hash Cond: ("outer".msgid = "inner".msgid)
                           ->  Seq Scan on msg_fate f  (cost=0.00..62171.25 rows=2771267 width=16) (actual time=23.550..12853.429 rows=2768210 loops=1)
                                 Filter: (modid = -2)
                           ->  Hash  (cost=82959.20..82959.20 rows=1485233 width=24) (actual time=13405.290..13405.290 rows=0 loops=1)
                                 ->  Seq Scan on msg_info i  (cost=0.00..82959.20 rows=1485233 width=24) (actual time=41.076..11777.694 rows=1487886 loops=1)
                                       Filter: ((entry_time > '2006-01-25 00:00:00'::timestamp without time zone) AND (origin = 1))
 Total runtime: 255981.096 ms
(15 rows)



3)
explain analyze SELECT COUNT(*) 
FROM  log.msg_info 
LEFT JOIN log.msg_fate USING (msgid)
LEFT JOIN log.msg_fate_recipients USING (grp_fate_id) 
LEFT JOIN pineapp.zones USING (zone_id)  
WHERE (7 & direction) != 0;

                                                                        QUERY PLAN                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=939475.09..939475.09 rows=1 width=0) (actual time=327972.053..327972.053 rows=1 loops=1)
   ->  Hash Left Join  (cost=267118.42..917030.95 rows=8977658 width=0) (actual time=73025.109..321925.128 rows=9022752 loops=1)
         Hash Cond: ("outer".zone_id = "inner".zone_id)
         ->  Hash Join  (cost=267117.41..827253.35 rows=8977658 width=4) (actual time=72975.297..308006.291 rows=9022752 loops=1)
               Hash Cond: ("outer".grp_fate_id = "inner".grp_fate_id)
               ->  Seq Scan on msg_fate_recipients  (cost=0.00..245901.49 rows=8977658 width=12) (actual time=29.999..62156.847 rows=9022771 loops=1)
                     Filter: ((7 & (direction)::integer) <> 0)
               ->  Hash  (cost=249262.76..249262.76 rows=2787060 width=8) (actual time=72928.544..72928.544 rows=0 loops=1)
                     ->  Hash Join  (cost=86719.66..249262.76 rows=2787060 width=8) (actual time=12380.175..69636.360 rows=2787060 loops=1)
                           Hash Cond: ("outer".msgid = "inner".msgid)
                           ->  Seq Scan on msg_fate  (cost=0.00..55203.60 rows=2787060 width=16) (actual time=24.202..21522.896 rows=2787060 loops=1)
                           ->  Hash  (cost=69590.13..69590.13 rows=2673813 width=8) (actual time=12355.098..12355.098 rows=0 loops=1)
                                 ->  Seq Scan on msg_info  (cost=0.00..69590.13 rows=2673813 width=8) (actual time=31.687..9685.591 rows=2673813 loops=1)
         ->  Hash  (cost=1.01..1.01 rows=1 width=4) (actual time=26.508..26.508 rows=0 loops=1)
               ->  Seq Scan on zones  (cost=0.00..1.01 rows=1 width=4) (actual time=26.478..26.480 rows=1 loops=1)
 Total runtime: 327984.784 ms
(16 rows)