Attaching explain analyze file as well.
Vivek
-----Original Message-----
From: Vivekanand Joshi [mailto:vjoshi@xxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, March 17, 2015 5:36 PM
To: 'Tomas Vondra'; 'Jim Nasby'; 'Scott Marlowe'; 'Varadharajan Mukundan'
Cc: 'pgsql-performance@xxxxxxxxxxxxxx'
Subject: RE: Performance issues
Hi Tomas,
This is what I am getting,
EXPLAIN ANALYZE SELECT * FROM s_f_promotion_history WHERE (send_dt >=
'2014-03-13 00:00:00');
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on s_f_promotion_history (cost=0.00..283333.66 rows=1 width=74)
(actual time=711.023..1136.393 rows=1338 loops=1)
Filter: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time zone)
AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 9998662
Total runtime: 1170.682 ms
CREATE INDEX idx_pr_history ON
S_F_PROMOTION_HISTORY(touchpoint_execution_id, send_dt);
After Creating Index:
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_pr_history on s_f_promotion_history
(cost=0.43..254028.45 rows=1 width=74) (actual time=375.796..604.587
rows=1338 loops=1)
Index Cond: ((send_dt >= '2014-03-13 00:00:00'::timestamp without time
zone) AND (send_dt <= '2015-03-14 00:00:00'::timestamp without time zone))
Total runtime: 604.733 ms
The query I gave you is the smallest query, it is using two views and both
the views I have changed by using subqueries instead of CTEs. When I join
these two views, it is not getting completed at all.
Explain analyze plan for view s_v_f_promotion_history_email:
http://explain.depesz.com/s/ure Explain analyze plan for view
s_v_d_campaign_hierarchy : http://explain.depesz.com/s/WxI
Regards,
Vivek
-----Original Message-----
From: Tomas Vondra [mailto:tomas.vondra@xxxxxxxxxxxxxxx]
Sent: Tuesday, March 17, 2015 5:15 PM
To: vjoshi@xxxxxxxxxxxxxxxxxxx; Jim Nasby; Scott Marlowe; Varadharajan
Mukundan
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Performance issues
On 17.3.2015 12:07, Vivekanand Joshi wrote:
> EXPLAIN ANALYZE didn't give result even after three hours.
In that case the only thing you can do is 'slice' the query into smaller
parts (representing subtrees of the plan), and analyze those first. Look for
misestimates (significant differences between estimated and actual row
counts, and very expensive parts).
We can't do that, because we don't have your data or queries, and without
the explain analyze it's difficult to give advices.
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
s_v_f_promotion_history_email
Hash Left Join (cost=34679.90..37396.37 rows=11644 width=148) (actual time=609.472..9070.675 rows=4559289 loops=1)
Hash Cond: ((base.promo_hist_id = email.promo_hist_id) AND (base.audience_member_id = email.audience_member_id))
-> Nested Loop (cost=32782.62..35004.21 rows=11644 width=74) (actual time=567.441..4096.969 rows=4559289 loops=1)
Join Filter: (s_f_touchpoint_execution_status_history.touchpoint_execution_id = base.touchpoint_execution_id)
-> Nested Loop (cost=32782.19..34504.16 rows=1 width=16) (actual time=337.484..884.438 rows=46454 loops=1)
-> Nested Loop (cost=32781.90..34503.83 rows=1 width=24) (actual time=337.462..682.943 rows=71892 loops=1)
-> Unique (cost=32781.61..34495.50 rows=1 width=8) (actual time=337.428..478.619 rows=76088 loops=1)
-> Merge Join (cost=32781.61..34495.50 rows=1 width=8) (actual time=337.427..454.249 rows=77090 loops=1)
Merge Cond: ((s_f_touchpoint_execution_status_history.touchpoint_execution_id = s_f_touchpoint_execution_status_history_1.touchpoint_ex
ecution_id) AND (s_f_touchpoint_execution_status_history.creation_dt = (max(s_f_touchpoint_execution_status_history_1.creation_dt))))
-> Sort (cost=19697.87..20098.14 rows=160107 width=16) (actual time=132.938..155.325 rows=160898 loops=1)
Sort Key: s_f_touchpoint_execution_status_history.touchpoint_execution_id, s_f_touchpoint_execution_status_history.creation_dt
Sort Method: quicksort Memory: 13687kB
-> Seq Scan on s_f_touchpoint_execution_status_history (cost=0.00..5857.68 rows=160107 width=16) (actual time=0.012..60.421 row
s=160898 loops=1)
Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))
Rows Removed by Filter: 130206
-> Sort (cost=13083.74..13254.76 rows=68410 width=16) (actual time=204.476..227.483 rows=77456 loops=1)
Sort Key: s_f_touchpoint_execution_status_history_1.touchpoint_execution_id, (max(s_f_touchpoint_execution_status_history_1.creat
ion_dt))
Sort Method: quicksort Memory: 6656kB
-> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16) (actual time=140.992..169.426 rows=76454 loops=1)
-> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows
=291104 width=16) (actual time=0.013..39.851 rows=291104 loops=1)
-> Index Scan using idx_s_d_touchpoint_execution_id on s_d_touchpoint_execution tpe (cost=0.29..8.31 rows=1 width=16) (actual time=0.002..0.002 r
ows=1 loops=76088)
Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)
-> Index Only Scan using s_d_touchpoint_pkey on s_d_touchpoint tp (cost=0.29..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=71892)
Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))
Heap Fetches: 46454
-> Index Scan using idx_pr_history on s_f_promotion_history base (cost=0.43..311.52 rows=15083 width=74) (actual time=0.002..0.033 rows=98 loops=46454)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Hash (cost=1192.71..1192.71 rows=46971 width=90) (actual time=41.978..41.978 rows=46971 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 4775kB
-> Seq Scan on s_f_promotion_history_email email (cost=0.00..1192.71 rows=46971 width=90) (actual time=0.010..19.620 rows=46971 loops=1)
s_v_d_campaign_hierarchy
Nested Loop Left Join (cost=18485.07..20366.44 rows=1 width=894) (actual time=2656.451..4041.952 rows=45360 loops=1)
-> Nested Loop (cost=18484.94..20366.29 rows=1 width=776) (actual time=2445.487..3741.049 rows=45360 loops=1)
-> Nested Loop Left Join (cost=18484.81..20366.12 rows=1 width=658) (actual time=2445.471..3648.007 rows=48926 loops=1)
-> Hash Join (cost=18484.68..20365.96 rows=1 width=340) (actual time=2267.997..3359.148 rows=48926 loops=1)
Hash Cond: ((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id) A
ND ((max(s_f_touchpoint_execution_status_history_1.creation_dt)) = s_f_touchpoint_execution_status_history.creation_dt))
-> HashAggregate (cost=6221.56..6905.66 rows=68410 width=16) (actual time=140.079..181.465 rows=76454 loops=1)
-> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows=291104 width=16)
(actual time=0.010..38.894 rows=291104 loops=1)
-> Hash (cost=12263.09..12263.09 rows=2 width=356) (actual time=2127.869..2127.869 rows=131427 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 37990kB
-> Hash Join (cost=6259.72..12263.09 rows=2 width=356) (actual time=1357.652..1990.177 rows=131427 loops=1)
Hash Cond: (s_f_touchpoint_execution_status_history.touchpoint_execution_id = tp_exec.touchpoint_execution_id)
-> Seq Scan on s_f_touchpoint_execution_status_history (cost=0.00..5493.80 rows=135878 width=16) (actual time=0.011..52.932 rows=1362
80 loops=1)
Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))
Rows Removed by Filter: 154824
-> Hash (cost=6259.71..6259.71 rows=1 width=340) (actual time=1357.605..1357.605 rows=72427 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 19510kB
-> Nested Loop Left Join (cost=1955.40..6259.71 rows=1 width=340) (actual time=158.779..1269.938 rows=72427 loops=1)
-> Nested Loop Left Join (cost=1955.27..6259.55 rows=1 width=222) (actual time=31.836..998.297 rows=72427 loops=1)
-> Nested Loop (cost=1954.99..6259.24 rows=1 width=197) (actual time=31.826..829.278 rows=72427 loops=1)
-> Nested Loop (cost=1954.71..6258.92 rows=1 width=173) (actual time=31.816..636.122 rows=72427 loops=1)
Join Filter: (camp_exec.campaign_id = wave.campaign_id)
Rows Removed by Join Filter: 243
-> Nested Loop (cost=1954.42..6254.67 rows=13 width=167) (actual time=31.804..435.461 rows=72670 loops=
1)
-> Hash Join (cost=1954.13..6249.67 rows=13 width=108) (actual time=31.777..210.346 rows=72670 lo
ops=1)
Hash Cond: ((tp_exec.touchpoint_id = tp.touchpoint_id) AND (wave_exec.wave_id = tp.wave_id))
-> Hash Join (cost=1576.83..4595.51 rows=72956 width=90) (actual time=25.984..141.564 rows=
72956 loops=1)
Hash Cond: (tp_exec.wave_execution_id = wave_exec.wave_execution_id)
-> Seq Scan on s_d_touchpoint_execution tp_exec (cost=0.00..1559.56 rows=72956 width=
42) (actual time=0.005..14.945 rows=72956 loops=1)
-> Hash (cost=1001.37..1001.37 rows=46037 width=56) (actual time=25.922..25.922 rows=
46037 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 4104kB
-> Seq Scan on s_d_wave_execution wave_exec (cost=0.00..1001.37 rows=46037 widt
h=56) (actual time=0.007..10.424 rows=46037 loops=1)
-> Hash (cost=212.72..212.72 rows=10972 width=26) (actual time=5.773..5.773 rows=10972 loop
s=1)
Buckets: 2048 Batches: 1 Memory Usage: 645kB
-> Seq Scan on s_d_touchpoint tp (cost=0.00..212.72 rows=10972 width=26) (actual time
=0.007..2.350 rows=10972 loops=1)
-> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec (cost=0.29..0.
37 rows=1 width=67) (actual time=0.002..0.002 rows=1 loops=72670)
Index Cond: (campaign_execution_id = wave_exec.campaign_execution_id)
-> Index Scan using s_d_wave_pkey on s_d_wave wave (cost=0.29..0.31 rows=1 width=22) (actual time=0.002
..0.002 rows=1 loops=72670)
Index Cond: (wave_id = wave_exec.wave_id)
-> Index Scan using s_d_campaign_pkey on s_d_campaign camp (cost=0.29..0.32 rows=1 width=40) (actual time=0.0
02..0.002 rows=1 loops=72427)
Index Cond: (campaign_id = camp_exec.campaign_id)
-> Index Scan using s_d_content_pkey on s_d_content content (cost=0.28..0.30 rows=1 width=33) (actual time=0.001..0
.002 rows=1 loops=72427)
Index Cond: (tp_exec.content_id = content_id)
-> Index Scan using s_d_message_type_pkey on s_d_message_type message_type (cost=0.13..0.15 rows=1 width=120) (actual tim
e=0.003..0.003 rows=1 loops=72427)
Index Cond: (tp_exec.message_type_id = message_type_id)
-> Index Scan using s_d_group_pkey on s_d_group grup (cost=0.13..0.15 rows=1 width=320) (actual time=0.005..0.005 rows=1 loops=48926)
Index Cond: (camp_exec.group_id = group_id)
-> Index Scan using d_channel_pk on s_d_channel_type channel (cost=0.13..0.15 rows=1 width=120) (actual time=0.001..0.001 rows=1 loops=48926)
Index Cond: (channel_type_id = tp.channel_type_id)
-> Index Scan using s_d_category_pkey on s_d_category "CATEGORY" (cost=0.13..0.15 rows=1 width=120) (actual time=0.006..0.006 rows=1 loops=45360)
Index Cond: (camp.category_id = category_id)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
