Message-ID: <55084803.9030508@2ndquadrant.com>
Date: Tue, 17 Mar 2015 16:28:03 +0100
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Organization: 2ndQuadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: vjoshi@zetainteractive.com, pgsql-performance@postgresql.org
Subject: Re: Performance issues
References: <84a6fe4be04c3ef0a714851977860ac8@mail.gmail.com>
	<CANwLwatODrcwDgO+JXG-L=jJJQTucvUrur01E-y_tE_PT8i7kQ@mail.gmail.com>
	<55034A48.4040409@2ndquadrant.com>
	<CANwLwavsL-+SDRB7VKxQ_OJq-T=MVQV6T6qH5Aug9PaSWt2C0w@mail.gmail.com>
	<CACKkDGHOnb2efYvLj557E5pSuzS+Wmu9dqG3Z=b_dbYhpi7muQ@mail.gmail.com>
 <CAOR=d=2L86fB+HWX8h078tYsd7LVKKFPQb=jfd8YNRU1Fc7mUA@mail.gmail.com>
 <849e75d9463938372dd4c516f5c5d89c@mail.gmail.com>
 <55037CEA.5050906@2ndquadrant.com> <55073272.20209@BlueTreble.com>
 <55074424.9050506@2ndquadrant.com> <55076FFB.9090105@BlueTreble.com>
 c5231342781287ff8cbc9ac1f20ff036@mail.gmail.com <61a7
 a27428e588411e8ac1817043205c@mail.gmail.com>
 <550813CD.7040605@2ndquadrant.com>
 d6a86358c5e8d7ffe923ff307c3e8c7e@mail.gmail.com
 <acf5780f0e3c7d42d5f6499830378008@mail.gmail.com>
 <55083236.8070900@2ndquadrant.com> <me9d4u$9b5$1@ger.gmane.org>
 <55083D74.4000601@2ndquadrant.com>
 <087a57caebe1d71091670f3435d2c1e5@mail.gmail.com>
In-Reply-To: <087a57caebe1d71091670f3435d2c1e5@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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