Re: Performance issues

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