PostgreSQL 查询调优指南？

Guides for PostgreSQL query tuning?

提问by Joe

I've found a number of resources that talk about tuning the database server, but I haven't found much on the tuning of the individual queries.

我找到了许多关于调整数据库服务器的资源，但我没有找到太多关于调整单个查询的内容。

For instance, in Oracle, I might try adding hints to ignore indexes or to use sort-merge vs. correlated joins, but I can't find much on tuning Postgres other than using explicit joinsand recommendations when bulk loading tables.

例如，在 Oracle 中，我可能会尝试添加提示以忽略索引或使用排序合并与相关连接，但除了在批量加载表时使用显式连接和建议之外，我找不到太多关于调整 Postgres 的内容。

Do any such guides exist so I can focus on tuning the most run and/or underperforming queries, hopefully without adversely affecting the currently well-performing queries?

是否存在任何此类指南，以便我可以专注于调整最常运行和/或性能不佳的查询，希望不会对当前性能良好的查询产生不利影响？

I'd even be happy to find something that compared how certain types of queries performed relative to other databases, so I had a better clue of what sort of things to avoid.

我什至很乐意找到可以比较某些类型的查询相对于其他数据库的执行情况的内容，因此我对应该避免的事情有了更好的线索。

update:

更新：

I should've mentioned, I took all of the Oracle DBA classes along with their data modeling and SQL tuning classes back in the 8i days ... so I know about 'EXPLAIN', but that's more to tell you what's going wrong with the query, not necessarily how to make it better. (eg, are 'while var=1 or var=2' and 'while var in (1,2)' considered the same when generating an execution plan? What if I'm doing it with 10 permutations? When are multi-column indexes used? Are there ways to get the planner to optimize for fastest start vs. fastest finish? What sort of 'gotchas' might I run into when moving from mySQL, Oracle or some other RDBMS?)

我应该提到过，我在 8i 年代学习了所有 Oracle DBA 类及其数据建模和 SQL 调优类……所以我知道“EXPLAIN”，但这更多是为了告诉您查询，不一定是如何使它变得更好。（例如，在生成执行计划时，'while var=1 或 var=2' 和 'while var in (1,2)' 是否被认为是相同的？如果我用 10 个排列来做呢？什么时候是多列使用的索引？有没有办法让规划器针对最快的开始和最快的完成进行优化？从 mySQL、Oracle 或其他一些 RDBMS 迁移时，我可能会遇到什么样的“问题”？）

I could write any complex query dozens if not hundreds of ways, and I'm hoping to not have to try them all and find which one works best through trial and error. I've already found that 'SELECT count(*)' won't use an index, but 'SELECT count(primary_key)' will ... maybe a 'PostgreSQL for experienced SQL users' sort of document that explained sorts of queries to avoid, and how best to re-write them, or how to get the planner to handle them better.

我可以用几十种甚至数百种方式编写任何复杂的查询，我希望不必尝试所有这些，然后通过反复试验找到最有效的方法。我已经发现 'SELECT count(*)' 不会使用索引，但是 'SELECT count(primary_key)' 会......也许是一个 'PostgreSQL for经验丰富的 SQL 用户' 类型的文档，它解释了各种查询避免，以及如何最好地重新编写它们，或者如何让计划者更好地处理它们。

update 2:

更新2：

I found a Comparison of different SQL Implementationswhich covers PostgreSQL, DB2, MS-SQL, mySQL, Oracle and Informix, and explains if, how, and gotchas on things you might try to do, and his references section linked to Oracle / SQL Server / DB2 / Mckoi /MySQL Database Equivalents(which is what its title suggests) and to the wikibook SQL Dialects Referencewhich covers whatever people contribute (includes some DB2, SQLite, mySQL, PostgreSQL, Firebird, Vituoso, Oracle, MS-SQL, Ingres, and Linter).

我找到了一篇涵盖 PostgreSQL、DB2、MS-SQL、mySQL、Oracle 和 Informix的不同 SQL 实现的比较，并解释了您可能尝试做的事情的是否、如何和遇到的问题，以及他的参考资料部分链接到Oracle/SQL Server / DB2 / Mckoi /MySQL Database Equivalents（这就是它的标题所暗示的）和维基书SQL Dialects Reference，其中涵盖了人们贡献的任何内容（包括一些 DB2、SQLite、mySQL、PostgreSQL、Firebird、Vituoso、Oracle、MS-SQL、Ingres ，和短绒）。

采纳答案by synecdoche

http://www.postgresql.org/docs/current/static/indexes-examine.html

http://www.postgresql.org/docs/current/static/indexes-examine.html

You can give hints: SET enable_indexscan TO false;would make PostgreSQL try to not use indexes

你可以给出提示：SET enable_indexscan TO false;会让 PostgreSQL 尽量不使用索引

回答by Joe

As for badly performing queries - do explain analyze and read it.

至于性能不佳的查询 - 请解释分析并阅读它。

You can put explain analyze output on site like explain.depesz.com- it will help you find the elements that really take the most time.

您可以在网站上放置解释分析输出，例如explain.depesz.com- 它会帮助您找到真正花费最多时间的元素。

回答by Marco Mariani

There is a nice online tool that takes the output of EXPLAIN ANALYZE, and graphically shows you critical parts (e.g. wrong estimates, hot spots, etc)

有一个不错的在线工具，可以获取 的输出EXPLAIN ANALYZE，并以图形方式向您显示关键部分（例如错误估计、热点等）

http://explain.depesz.com/help

http://explain.depesz.com/help

Btw, I think posted queries become public, and the "previous explains" link has been hit by spambots.

顺便说一句，我认为发布的查询会公开，并且“以前的解释”链接已被垃圾邮件机器人攻击。

回答by Will Hartung

To address your point, unfortunately the only way to tune a query in Postgres is pretty much to tune the database underlying it. In oracle, you can set all of those options on a query by query basis, trump the optimizers plan in the process, but in Postgres, you're pretty much at the mercy of the optimizer, for good and ill.

为了解决您的问题，不幸的是，在 Postgres 中调整查询的唯一方法几乎是调整其底层的数据库。在 oracle 中，您可以在逐个查询的基础上设置所有这些选项，在此过程中胜过优化器计划，但在 Postgres 中，无论好坏，您几乎都受优化器的支配。

回答by monksy

The PGAdmin3 tool includes a graphical explanation tool for breaking down how a query is handled. It also is especially helpful for showing where table scans occur.

PGAdmin3 工具包括一个图形解释工具，用于分解查询的处理方式。它对于显示表扫描发生的位置也特别有用。

回答by hgmnz

Best I've seen are in here: http://wiki.postgresql.org/wiki/Using_EXPLAIN, but the latest PDF in there is from 2008, so there may be something more recent. I'm interested to hear other user's answers.

我见过的最好的在这里：http: //wiki.postgresql.org/wiki/Using_EXPLAIN，但最新的 PDF 是从 2008 年开始的，所以可能会有更新的内容。我很想听听其他用户的回答。

Also, something's brewing in the contrib packages: http://www.sai.msu.su/~megera/wiki/plantuner

此外，contrib 包中正在酝酿一些东西：http: //www.sai.msu.su/~megera/wiki/plantuner