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 set 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.