What are the pitfalls of setting enable_nestloop to OFF

Question

I have a query in my application that runs very fast when there are large number of rows in my tables. But when the number of rows is a moderate size (neither large nor small) - the same query runs as much as 15 times slower.

The explain plan shows that the query on a medium sized data set is using nested loops for its join algorithm. The large data set uses hashed joins.

I can discourage the query planner from using nested loops either at the database level (postgresql.conf) or per session (SET enable_nestloop TO off).

What are the potential pitfalls of setting enable_nestloop to off?

Other info: PostgreSQL 8.2.6, running on Windows.

Answer 1

What are the potential pitfalls of setting enable_nestloop to off?

This means that you will never be able to use indexes efficiently.

And it seems that you don't use them now.

The query like this:

SELECT u.name, p.name
FROM users u
JOIN profiles p ON p.id = u.profile_id
WHERE u.id = :id

will most probably use NESTED LOOPS with an INDEX SCAN on user.id and an INDEX SCAN on profile.id, provided that you have built indices on these fields.

Queries with low selectivity filters (that is, queries that need more than 10% of data from tables they use) will benefit from MERGE JOINS and HASH JOINS.

But the queries like one given above require NESTED LOOPS to run efficiently.

If you post your queries and table definitions here, probably much may be done about the indexes and queries performance.

Answer 2

A few things to consider before taking such drastic measures:

• upgrade your installation to the latest 8.2.x (which right now is 8.2.12). Even better - consider upgrading to the next stable version which is 8.3 (8.3.6).

• consider changing your production platform to something other than Windows. The Windows port of PostgreSQL, although very useful for development purpose, is still not on a par with the Un*x ones.

• read the first paragraph of "Planner Method Configuration". This wiki page probably will help too.