pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerfrmance Problems (7.4.6)
4+ messages / 2 participants
[nested] [flat]
* Perfrmance Problems (7.4.6)
@ 2006-04-20 08:23 Doron Baranes <doron.baranes@dbnet.co.il>
2006-04-20 10:48 ` Re: Perfrmance Problems (7.4.6) Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 1 reply; 4+ messages in thread
From: Doron Baranes @ 2006-04-20 08:23 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
explain_analyze.txt (text/plain, 10.3K) download
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)
* Re: Perfrmance Problems (7.4.6)
2006-04-20 08:23 Perfrmance Problems (7.4.6) Doron Baranes <doron.baranes@dbnet.co.il>
@ 2006-04-20 10:48 ` Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ruben Rubio Rey @ 2006-04-20 10:48 UTC (permalink / raw)
To: Doron Baranes <doron.baranes@dbnet.co.il>; pgsql-performance
I think that the problem is the GROUP BY (datetime) that is
date_trunc('hour'::text, i.entry_time)
You should create an indexe with this expression (if its possible).
http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html
If is not possible, I would create a column with value
date_trunc('hour'::text, i.entry_time) of each row and then index it.
Hope this helps :)
Doron Baranes wrote:
>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
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Perfrmance Problems (7.4.6)
@ 2006-04-20 12:57 Doron Baranes <doron.baranes@dbnet.co.il>
2006-04-20 14:54 ` Re: Perfrmance Problems (7.4.6) Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 1 reply; 4+ messages in thread
From: Doron Baranes @ 2006-04-20 12:57 UTC (permalink / raw)
To: Ruben Rubio Rey <ruben@rentalia.com>; pgsql-performance
Ok. But that means I need a trigger on the original column to update the
new column on each insert/update and that overhead.
-----Original Message-----
From: Ruben Rubio Rey [mailto:ruben@rentalia.com]
Sent: Thursday, April 20, 2006 12:49 PM
To: Doron Baranes; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Perfrmance Problems (7.4.6)
I think that the problem is the GROUP BY (datetime) that is
date_trunc('hour'::text, i.entry_time)
You should create an indexe with this expression (if its possible).
http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html
If is not possible, I would create a column with value
date_trunc('hour'::text, i.entry_time) of each row and then index it.
Hope this helps :)
Doron Baranes wrote:
>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
>
>
>-----------------------------------------------------------------------
-
>
>
>---------------------------(end of
broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Perfrmance Problems (7.4.6)
2006-04-20 12:57 Re: Perfrmance Problems (7.4.6) Doron Baranes <doron.baranes@dbnet.co.il>
@ 2006-04-20 14:54 ` Ruben Rubio Rey <ruben@rentalia.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ruben Rubio Rey @ 2006-04-20 14:54 UTC (permalink / raw)
To: Doron Baranes <doron.baranes@dbnet.co.il>; pgsql-performance
Did you tried to index the expression?
Did it work?
Doron Baranes wrote:
>Ok. But that means I need a trigger on the original column to update the
>new column on each insert/update and that overhead.
>
>-----Original Message-----
>From: Ruben Rubio Rey [mailto:ruben@rentalia.com]
>Sent: Thursday, April 20, 2006 12:49 PM
>To: Doron Baranes; pgsql-performance@postgresql.org
>Subject: Re: [PERFORM] Perfrmance Problems (7.4.6)
>
>I think that the problem is the GROUP BY (datetime) that is
>date_trunc('hour'::text, i.entry_time)
>You should create an indexe with this expression (if its possible).
>
>http://www.postgresql.org/docs/7.4/interactive/indexes-expressional.html
>
>If is not possible, I would create a column with value
>date_trunc('hour'::text, i.entry_time) of each row and then index it.
>
>Hope this helps :)
>
>Doron Baranes wrote:
>
>
>
>>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
>>
>>
>>-----------------------------------------------------------------------
>>
>>
>-
>
>
>>---------------------------(end of
>>
>>
>broadcast)---------------------------
>
>
>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-20 14:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-20 08:23 Perfrmance Problems (7.4.6) Doron Baranes <doron.baranes@dbnet.co.il>
2006-04-20 10:48 ` Ruben Rubio Rey <ruben@rentalia.com>
2006-04-20 12:57 Re: Perfrmance Problems (7.4.6) Doron Baranes <doron.baranes@dbnet.co.il>
2006-04-20 14:54 ` Ruben Rubio Rey <ruben@rentalia.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox