A query of multiple joins spends a lot of time on nested loops. I disabled the nested loops and the query ran much faster. However, I have been told that nested loops might give me wrong results by skipping some tuples. Is this true?


Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug.

However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the correct plan node for best performance in a part of a query, and forcing PostgreSQL to prefer other options might make queries significantly slower.

Instead, you should generally adjust the cost parameters like random_page_cost to encourge PostgreSQL to choose a plan that performs better on your hardware and workload. For more information see the tuning advice on the PostgreSQl wiki and in the main documentation.

  • Thanks. I already manipulated the random_page_cost. What if I disable nestloops only for that query? Postgresql already does that. I can only disable nestloops for the duration of that query. Also if I had table with too many columns, would dividing it improve the performance?
    – user54969
    Dec 13 '14 at 19:18
  • If the planner is choosing a slower plan than the best possible, then either the cost parameters aren't correct for your system, the statistics don't reflect the actual data, or there's a planner bug/limitation preventing choice of the best plan. If you've conclusively eliminated the first two, then you could choose to force a join type. I don't do that myself, as I find that it becomes a problem later when the data has changed/grown because it prevents the planner from adapting to changes in the data. Dec 14 '14 at 6:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy