pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Re: 2+ messages / 2 participants [nested] [flat]
* Re: @ 2006-04-10 08:30 Doron Baranes <doron.baranes@dbnet.co.il> 2006-04-10 11:59 ` Re: Ragnar <gnari@hive.is> 0 siblings, 1 reply; 2+ messages in thread From: Doron Baranes @ 2006-04-10 08:30 UTC (permalink / raw) To: pgsql-performance Hi, 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 -----Original Message----- From: Ragnar [mailto:gnari@hive.is] Sent: Sunday, April 09, 2006 2:37 PM To: Doron Baranes Subject: RE: [PERFORM] On sun, 2006-04-09 at 14:11 +0200, Doron Baranes wrote: Please reply to the list, not to me directly. this way others can help you too. > I did vacuum database analyze a few days ago. yes, I saw that in your original post. I mentioned VACUUM FULL ANALYZE , not just VACUUM ANALYZE > I'll attached a few explain plans. [explain plans deleted] These are useless. you must show us the output of EXPLAIN ANALYZE. these are output of EXPLAIN. A plan is not much use without seeing the query itself. you still have not answered the question about what indexes you have. gnari Attachments:^ permalink raw reply [nested|flat] 2+ messages in threadexplain_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: 2006-04-10 08:30 Re: Doron Baranes <doron.baranes@dbnet.co.il> @ 2006-04-10 11:59 ` Ragnar <gnari@hive.is> 0 siblings, 0 replies; 2+ messages in thread From: Ragnar @ 2006-04-10 11:59 UTC (permalink / raw) To: Doron Baranes <doron.baranes@dbnet.co.il>; +Cc: pgsql-performance On mán, 2006-04-10 at 10:30 +0200, Doron Baranes wrote: > 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 first query: > 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; if i.origin has high selectivity (if very few rows in msg_info have origin=1 in this case), an index on msg_info(orgin) can help. unfortunately, as you are using 7.4 and this is a smallint column, you would have to change the query slightly to make use of that: WHERE i.origin = 1::smallint if more than a few % or the rows have this value, then this will not help the index on msg_info(entry_time) is unlikely to be used, because a simple '>' comparison has little selectivity. try to add an upper limit to the query to make it easier for the planner so see that few rows would be returned (if that is the case) for example: AND i.entry_time BETWEEN '2006-01-25' AND '2006-05-01' this might also improve the estimated number of groups on datetime (notice: estimated rows=1485233, real=623), although I am not sure if that will help you I do now know how good the planner is with dealing with the date_trunc('hour'::text, i.entry_time), so possibly you could get some improvement with an indexed entry_hour column populated with trigger or by your application, and change your query to: explain analyze SELECT i.entry_hour, 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_hour BETWEEN '2006-01-25:00:00' AND '2006-05-01:00:00' AND f.grp_fate_id=fr.grp_fate_id GROUP BY entry_hour order by entry_hour; (adjust the upper limit to your reality) do these suggestions help at all? gnari ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-04-10 11:59 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-04-10 08:30 Re: Doron Baranes <doron.baranes@dbnet.co.il> 2006-04-10 11:59 ` Ragnar <gnari@hive.is>
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