优化PostgreSQL只读表 [英] Optimize PostgreSQL read-only tables

问题描述

我在Postgres数据库中有很多只读表。可以使用任何列组合查询所有这些表。

I have many read-only tables in a Postgres database. All of these tables can be queried using any combination of columns.

我可以做些什么来优化查询？向所有表的所有列添加索引是一个好主意吗？

What can I do to optimize queries? Is it a good idea to add indexes to all columns to all tables?

推荐答案

用于过滤<的列/ strong>或加入（或者，在较小程度上，排序）对索引感兴趣。刚刚选择的列几乎没有相关性！
对于以下查询，只有 a 和 e 上的索引可能有用：

Columns that are used for filtering or joining (or, to a lesser degree, sorting) are of interest for indexing. Columns that are just selected are barely relevant! For the following query only indexes on a and e may be useful:

SELECT a,b,c,d
  FROM tbl_a
 WHERE a = $some_value
   AND e < $other_value;

此处， f 和可能 c 是候选人：

Here, f and possibly c are candidates, too:

SELECT a,b,c,d
  FROM tbl_a
  JOIN tbl_b USING (f)
 WHERE a = $some_value
   AND e < $other_value
 ORDER BY c;

创建索引后，测试它们是否真的对 EXPLAIN ANALYZE 。还可以比较有和没有索引的执行时间。删除和重新创建索引非常简单快捷。 实验还有<$ c $的参数c> EXPLAIN ANALYZE 。差异可能是惊人的或不存在的。

由于您的表是只读的，索引维护很便宜。这只是光盘空间的问题。

After creating indexes, test to see if they are actually useful with EXPLAIN ANALYZE. Also compare execution times with and without the indexes. Deleting and recreating indexes is fast and easy. There are also parameters to experiment with EXPLAIN ANALYZE. The difference may be staggering or nonexistent.
As your tables are read only, index maintenance is cheap. It's merely a question of disc space.

如果你真的想知道你在做什么，首先阅读文档。

If you really want to know what you are doing, start by reading the docs.

1. 尝试记录足够的查询以查找典型用例。使用参数 log_statement = all <记录查询/ code> 。或者只使用 log_min_duration_statement 。

1. Try logging enough queries to find typical use cases. Log queries with the parameter log_statement = all for that. Or just log slow queries using log_min_duration_statement.

创建索引可能有用，并在一段时间后检查统计信息以查看实际使用的内容。 PostgreSQL为监控统计提供了完整的基础架构。研究统计（以及许多其他任务）的一种便捷方法是 pgAdmin ，您可以在其中选择您的表/函数/索引并获取对象浏览器（主窗口）中统计选项卡上的所有数据。

Create indexes that might be useful and check the statistics after some time to see what actually gets used. PostgreSQL has a whole infrastructure in place for monitoring statistics. One convenient way to study statistics (and many other tasks) is pgAdmin where you can chose your table / function / index and get all the data on the "statistics" tab in the object browser (main window).

按上述步骤查看正在使用的索引是否真正加快了速度。

Proceed as described above to see if indexes in use actually speed things up.

如果查询计划者应该选择使用一个或多个索引但是没有或产生不利影响那么你的设置可能有问题，你需要研究性能优化的基础知识：真空，分析，成本参数，内存使用情况，...

If the query planner should chose to use one or more of your indexes but to no or adverse effect then something is probably wrong with your setup and you need to study the basics of performance optimization: vacuum, analyze, cost parameters, memory usage, ...

这篇关于优化PostgreSQL只读表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！