如果 PostgreSQL count(*) 总是很慢，如何对复杂查询进行分页？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16916633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If PostgreSQL count(*) is always slow how to paginate complex queries?
提问by Daniil Ryzhkov
If PostgreSQL's count(*)is always slowhow to paginate complex queries?
如果PostgreSQL的count(*)是始终慢如何分页复杂的查询？
Making triggers doesn't seem to be a good solution as long as in this case we have a lot of pages (for example different categories, filters, etc).
只要在这种情况下我们有很多页面（例如不同的类别、过滤器等），制作触发器似乎不是一个好的解决方案。
What to do if VACUUM/VACUUM ANALYZE/ANALYZE/VACUUM FULLdoesn't help? What are the best practices to use count(*)with postgresql?
如果VACUUM/VACUUM ANALYZE/ANALYZE/VACUUM FULL没有帮助怎么办？count(*)与 postgresql一起使用的最佳实践是什么？
采纳答案by Craig Ringer
Did you read the heading on that article?
你读过那篇文章的标题吗？
Note that the following article only applies to versions of PostgreSQL prior to 9.2. Index-only scans are now implemented.
请注意，以下文章仅适用于 9.2 之前的 PostgreSQL 版本。现在实现了仅索引扫描。
Use 9.2 and you'll generally find you get much better results. Read the index-only scans wiki pagefor details.
使用 9.2，您通常会发现获得更好的结果。阅读仅索引扫描 wiki 页面了解详细信息。
That said, on older versions using LIMITand OFFSETgenerally works fine. You can estimate rowcounts (and therefore pagecounts) using the table statistics if you don't mind a bit of variation. See "Estimating row count" in the article you already linked to.
也就是说，在旧版本上使用LIMIT并且OFFSET通常可以正常工作。如果您不介意一点变化，您可以使用表统计信息来估计行数（以及页数）。请参阅您已链接到的文章中的“估计行数” 。
Paginating using LIMITand OFFSETis, IMO, an anti-pattern anyway. A lot of the time you can rephrase your pagination code so it uses sort_column > 'last_seen_value' LIMIT 100, i.e. it avoids the offset. This can sometimes result in very large performance gains.
无论如何，使用LIMIT和分页OFFSET是 IMO 的反模式。很多时候你可以改写你的分页代码，以便它使用sort_column > 'last_seen_value' LIMIT 100，即它避免了偏移。这有时会导致非常大的性能提升。
回答by Mark Selby
If you're doing SELECT count(*) FROM table and have pg stats enabled you can use the lower example, which in this case goes from 13ms down to 0.05ms.
如果您正在执行 SELECT count(*) FROM table 并启用了 pg stats，您可以使用下面的示例，在这种情况下，它从 13 毫秒下降到 0.05 毫秒。
SELECT count(*) FROM news;
26171
26171
EXPLAIN ANALYZE SELECT count(*) FROM news;
Total runtime: 13.057 ms
总运行时间：13.057 毫秒
SELECT reltuples::bigint AS count FROM pg_class WHERE oid = 'public.news'::regclass;
26171
26171
EXPLAIN ANALYZE SELECT reltuples::bigint AS count FROM pg_class WHERE oid = 'public.news'::regclass;
Total runtime: 0.053 ms
总运行时间：0.053 毫秒
