将enable_nestloop设置为OFF的陷阱有哪些 [英] What are the pitfalls of setting enable_nestloop to OFF

问题描述

我的应用程序中有一个查询，当我的表中有很多行时，该查询运行非常快。但是，当行数适中（既不大也不小）时，同一查询的运行速度慢15倍。

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.

我可以阻止查询计划程序在数据库级别使用嵌套循环（postgresql.conf）或每个会话（ SET enable_nestloop TO off ）。

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

的潜在陷阱是什么将enable_nestloop设置为关闭？

其他信息：在Windows上运行的PostgreSQL 8.2.6。

Other info: PostgreSQL 8.2.6, running on Windows.

推荐答案

将 enable_nestloop 设置为 off的潜在陷阱是什么？ ？

这意味着您将永远无法有效地使用索引。

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

似乎您现在不使用它们。

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

像这样的查询：

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

最有可能使用嵌套圈和索引扫描 o n user.id 和 profile.id INDEX SCAN >，前提是您已在这些字段上建立了索引。

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.

具有低选择性过滤器的查询（即，查询需要的 10％以上他们使用的表中的数据）将受益于 MERGE JOINS 和 HASH JOINS 。

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.

这篇关于将enable_nestloop设置为OFF的陷阱有哪些的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！