pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: vjoshi@zetainteractive.com
To: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Tue, 17 Mar 2015 16:28:03 +0100
Message-ID: <55084803.9030508@2ndquadrant.com> (raw)
In-Reply-To: <087a57caebe1d71091670f3435d2c1e5@mail.gmail.com>
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>
	<61a7 a27428e588411e8ac1817043205c@mail.gmail.com>
	<550813CD.7040605@2ndquadrant.com>
	<acf5780f0e3c7d42d5f6499830378008@mail.gmail.com>
	<55083236.8070900@2ndquadrant.com>
	<me9d4u$9b5$1@ger.gmane.org>
	<55083D74.4000601@2ndquadrant.com>
	<087a57caebe1d71091670f3435d2c1e5@mail.gmail.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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



view thread (59+ messages)  latest in thread

Message-ID: <55084803.9030508@2ndquadrant.com>
Permalink:  ../55084803.9030508@2ndquadrant.com/
Also on:    postgresql.org/message-id/55084803.9030508@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, vjoshi@zetainteractive.com
  Subject: Re: Performance issues
  In-Reply-To: <55084803.9030508@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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