On 17.3.2015 16:10, Vivekanand Joshi wrote:
> 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.
In general, yes. The problem is that the plan is constructed based on
the estimates, and those are very inaccurate in this case.
The planner may do various changes, but let's assume that does not
happen and the plans are executed and and the results are joined.
For example what might happen is this:
for each row in 's_v_d_campaign_hierarchy' (1 row expected):
execute s_v_f_promotion_history_email & join (11644 rows exp.)
But then it gets 45k rows from s_v_d_campaign_hierarchy, and ~400x more
rows from s_v_f_promotion_history_email (I'm neglecting the join
condition here, but that's not really significant). Kaboooom!
In reality, the plan is reorganized (e.g. different join order), but the
misestimates are still lurking there.
> Is there a way, we can do anything about it?
Rephrasing the query so that the planner can estimate it more accurately.
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
