This is the explain for a simple query:
explain Select * from S_V_F_PROMOTION_HISTORY_EMAIL a inner join
S_V_D_CAMPAIGN_HIERARCHY b on a.touchpoint_execution_id =
b.touchpoint_execution_id;
http://explain.depesz.com/s/gse
I am wondering the total cost here is less even then the result is not
coming out.
Regards,
Vivek
-----Original Message-----
From: Vivekanand Joshi [mailto:vjoshi@xxxxxxxxxxxxxxxxxxx]
Sent: Tuesday, March 17, 2015 8:40 PM
To: 'Tomas Vondra'; 'pgsql-performance@xxxxxxxxxxxxxx'
Subject: RE: Performance issues
The confusion for me here is that :
I am getting results from the view in around 3 seconds
(S_V_D_CAMPAIGN_HIERARCHY) and 25 seconds (S_V_F_PROMOTION_HISTORY_EMAIL)
But when I am using these two views in the query as the joining tables, it
doesn't give any result. As per my understanding, the planner is making new
plan and that is costly instead of using output from the view, which is
actually understandable.
Is there a way, we can do anything about it?
I hope I am making some sense here.
Regards,
Vivek
-----Original Message-----
From: pgsql-performance-owner@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Tomas Vondra
Sent: Tuesday, March 17, 2015 8:13 PM
To: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Performance issues
On 17.3.2015 15:19, Thomas Kellerer wrote:
> Tomas Vondra schrieb am 17.03.2015 um 14:55:
>> (2) using window functions, e.g. like this:
>>
>> SELECT * FROM (
>> SELECT *,
>> ROW_NUMBER() OVER (PARTITION BY touchpoint_execution_id
>> ORDER BY FROM max_creation_dt) AS rn
>> FROM s_f_touchpoint_execution_status_history
>> ) foo WHERE rn = 1
>>
>> But estimating this is also rather difficult ...
>
>
> From my experience rewriting something like the above using DISTINCT
> ON is usually faster.
How do you get the last record (with respect to a timestamp column) using a
DISTINCT ON?
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Nested Loop (cost=41187.21..45053.54 rows=1 width=403)
Join Filter: (s_f_touchpoint_execution_status_history_2.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution_id)
-> Nested Loop Left Join (cost=18488.74..20591.32 rows=1 width=271)
-> Nested Loop (cost=18488.61..20591.17 rows=1 width=263)
-> Nested Loop Left Join (cost=18488.48..20591.00 rows=1 width=255)
-> Hash Join (cost=18488.35..20590.84 rows=1 width=247)
Hash Cond: ((s_f_touchpoint_execution_status_history_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history.touchpoint_execution
_id) AND ((max(s_f_touchpoint_execution_status_history_1.creation_dt)) = s_f_touchpoint_execution_status_history.creation_dt))
-> HashAggregate (cost=6221.56..6986.10 rows=76454 width=16)
-> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1 (cost=0.00..4766.04 rows=291104 widt
h=16)
-> Hash (cost=12266.76..12266.76 rows=2 width=247)
-> Hash Join (cost=6261.89..12266.76 rows=2 width=247)
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=136280 width=16)
Filter: (touchpoint_execution_status_type_id = ANY ('{3,4}'::integer[]))
-> Hash (cost=6261.88..6261.88 rows=1 width=231)
-> Nested Loop Left Join (cost=1955.40..6261.88 rows=1 width=231)
-> Nested Loop Left Join (cost=1955.27..6261.72 rows=1 width=222)
-> Nested Loop (cost=1954.99..6261.41 rows=1 width=197)
-> Nested Loop (cost=1954.71..6261.08 rows=1 width=173)
Join Filter: (camp_exec.campaign_id = wave.campaign_id)
-> Nested Loop (cost=1954.42..6255.85 rows=16 width=167)
-> Hash Join (cost=1954.13..6249.70 rows=16 width=108)
Hash Cond: ((tp_exec.touchpoint_id = tp_1.touchpoint_id) AND (wave_exec.wave_id = tp_1.
wave_id))
-> Hash Join (cost=1576.83..4595.51 rows=72956 width=90)
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)
-> Hash (cost=1001.37..1001.37 rows=46037 width=56)
-> Seq Scan on s_d_wave_execution wave_exec (cost=0.00..1001.37 rows=4603
7 width=56)
-> Hash (cost=212.72..212.72 rows=10972 width=26)
-> Seq Scan on s_d_touchpoint tp_1 (cost=0.00..212.72 rows=10972 width=26)
-> Index Scan using s_d_campaign_execution_idx on s_d_campaign_execution camp_exec (cost=0.
29..0.37 rows=1 width=67)
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)
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)
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)
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=11)
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=10)
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=10)
Index Cond: (channel_type_id = tp_1.channel_type_id)
-> Index Scan using s_d_category_pkey on s_d_category "CATEGORY" (cost=0.13..0.15 rows=1 width=10)
Index Cond: (camp.category_id = category_id)
-> Hash Left Join (cost=22698.47..23979.56 rows=38611 width=164)
Hash Cond: ((base.promo_hist_id = email.promo_hist_id) AND (base.audience_member_id = email.audience_member_id))
-> Nested Loop (cost=20801.19..21310.02 rows=38611 width=90)
Join Filter: (s_f_touchpoint_execution_status_history_2.touchpoint_execution_id = base.touchpoint_execution_id)
-> Nested Loop (cost=20800.76..20808.85 rows=1 width=16)
-> Nested Loop (cost=20800.47..20808.52 rows=1 width=24)
-> HashAggregate (cost=20800.18..20800.19 rows=1 width=8)
-> Hash Join (cost=14492.71..20800.18 rows=1 width=8)
Hash Cond: ((s_f_touchpoint_execution_status_history_1_1.touchpoint_execution_id = s_f_touchpoint_execution_status_history_2.touc
hpoint_execution_id) AND ((max(s_f_touchpoint_execution_status_history_1_1.creation_dt)) = s_f_touchpoint_execution_status_history_2.creation_dt))
-> HashAggregate (cost=6221.56..6986.10 rows=76454 width=16)
-> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_1_1 (cost=0.00..4766.04 ro
ws=291104 width=16)
-> Hash (cost=5857.68..5857.68 rows=160898 width=16)
-> Seq Scan on s_f_touchpoint_execution_status_history s_f_touchpoint_execution_status_history_2 (cost=0.00..5857.68 rows
=160898 width=16)
Filter: (touchpoint_execution_status_type_id = ANY ('{3,4,6}'::integer[]))
-> Index Scan using idx_s_d_touchpoint_execution_id on s_d_touchpoint_execution tpe (cost=0.29..8.31 rows=1 width=16)
Index Cond: (touchpoint_execution_id = s_f_touchpoint_execution_status_history_2.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)
Index Cond: ((touchpoint_id = tpe.touchpoint_id) AND (channel_type_id = 1))
-> Append (cost=0.43..312.44 rows=15099 width=74)
-> Index Scan using idx_pr_history on s_f_promotion_history base (cost=0.43..311.52 rows=15083 width=74)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Index Scan using s_f_promotion_history_table1_teiidx on s_f_promotion_history_table1 base_1 (cost=0.15..0.23 rows=4 width=74)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Index Scan using s_f_promotion_history_table2_teiidx on s_f_promotion_history_table2 base_2 (cost=0.15..0.23 rows=4 width=74)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Index Scan using s_f_promotion_history_table3_teiidx on s_f_promotion_history_table3 base_3 (cost=0.15..0.23 rows=4 width=74)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Index Scan using s_f_promotion_history_table4_teiidx on s_f_promotion_history_table4 base_4 (cost=0.15..0.23 rows=4 width=74)
Index Cond: (touchpoint_execution_id = tpe.touchpoint_execution_id)
-> Hash (cost=1192.71..1192.71 rows=46971 width=90)
-> Seq Scan on s_f_promotion_history_email email (cost=0.00..1192.71 rows=46971 width=90)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
