Hi,
I am running on postgres 7.4.6.
I did a vacuum analyze on the database but there was no change.
I Attached here a file with details about the tables, the queries and
the Explain analyze plans.
Hope this can be helpful to analyze my problem
10x
Doron
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)
