What are the pitfalls of setting enable_nestloop t

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 set enable_nestloop to off?

Other info: PostgreSQL 8.2.6, running on Windows.

Answer 1

I have the exact same experience. Some queries on a large database were executed using nested loops and that took 12 hours!!! when it runs in 30 seconds when turning off nested loops or removing the indexes.

Having hints would be really nice here, but I tried

...
SET ENABLE_NESTLOOP TO FALSE;
... critical query
SET ENABLE_NESTLOOP TO TRUE;
...

to deal with this matter. So you can definitely disable and re-enable nested loop use, and you can't argue with a 9000-fold speed increase :)

One problem I have is to do the change of ENABLE_NESTLOOP in a PgSQL/PL procedure. I can run an SQL script in Aqua Data Studio doing everything right, but when I put it in a PgSQL/PL procedure, it then still takes 12 hours. Apparently it was ignoring the change.

Answer 2

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 3

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.