Message-ID: <5509D0AF.9080305@2ndquadrant.com>
Date: Wed, 18 Mar 2015 20:23:27 +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>
	<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@2ndqu
 adrant.com> d6a86358c5e8d7ffe923ff307c3e8c7e@mail.gmail.com
 <acf5780f0e3c7d42d5f6499830378008@mail.gmail.com>
 <55083236.8070900@2ndquadrant.com> <me9d4u$9b5$1@ger.gmane.org>
 <55083D74.4000601@2ndquadrant.com> <me9guu$cvp$1@ger.gmane.org>
 <55084890.6000907@2ndqu adrant.com>
 <9650933705b1a197888f77acbef26728@mail.gmail.com>
In-Reply-To: <9650933705b1a197888f77acbef26728@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,

On 18.3.2015 18:31, Vivekanand Joshi wrote:
> So, here is the first taste of success and which gives me the
> confidence that if properly worked out with a good hardware and
> proper tuning, PostgreSQL could be a good replacement.
> 
> Out of the 9 reports which needs to be migrated in PostgreSQL, 3 are
> now running.
> 
> Report 4 was giving an issue and I will see it tomorrow.
> 
> Just to inform you guys that, the thing that helped most is setting 
> enable_nestloops to false worked. Plans are now not miscalculated.

The estimates are still miscalculated, but you're forcing the database
not to use the nested loop. The problem is the nested loop may be
appropriate in some cases (maybe only in a few places of the plan) so
this is really corse-grained solution.

> But this is not a production-suitable setting. So what do you think
> how to get a work around this?

(a) Try to identify why the queries are poorly estimated, and rephrase
    them somehow. This is the best solution, but takes time, expertise
    and may not be feasible in some cases.

(b) Tweak the database structure, possibly introducing intermediate
    tables, materialized views (or tables maintained by triggers - this
    might work for the 'latest record' subquery), etc.

(c) Try to tweak the cost parameters, to make the nested loops more
    expensive (and thus less likely to be selected), but in a more
    gradual way than enable_nestloops=false.

regards

-- 
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