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: Wed, 18 Mar 2015 20:23:27 +0100
Message-ID: <5509D0AF.9080305@2ndquadrant.com> (raw)
In-Reply-To: <9650933705b1a197888f77acbef26728@mail.gmail.com>
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>
	<61a7 a27428e588411e8ac1817043205c@mail.gmail.com>
	<550813CD.7040605@2ndqu adrant.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>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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



view thread (59+ messages)  latest in thread

Message-ID: <5509D0AF.9080305@2ndquadrant.com>
Permalink:  ../5509D0AF.9080305@2ndquadrant.com/
Also on:    postgresql.org/message-id/5509D0AF.9080305@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: <5509D0AF.9080305@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