postgresql - 优化 PostgreSQL 只读表

Question

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

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

Answer 1

用于过滤 或连接(或者，在较小程度上，排序)的列对索引很重要。刚刚选择的列几乎不相关!对于以下查询，只有 a 和 e 上的索引可能有用:

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

在这里，f 和可能的 c 也是候选对象:

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

创建索引后(可能在表上运行ANALYZE 或什至VACUUM ANALYZE)，测试它们是否真的对EXPLAIN有用或 EXPLAIN ANALYZE .还要比较使用和不使用索引的执行时间。删除和重新创建索引既快速又容易。 experiment 也有参数使用 EXPLAIN ANALYZE。差异可能是惊人的或根本不存在。
由于您的表是只读的，因此在初始创建后索引维护很便宜。这只是磁盘空间的问题。

如果你真的想知道你在做什么，start by reading the docs .

如果您不知道期望的查询...

1. 尝试记录足够的查询以找到典型的用例。使用参数 log_statement = all 记录查询为了那个原因。或者只使用 log_min_duration_statement 记录慢速查询.

2. 创建 indexes这可能很有用，一段时间后检查统计数据以查看实际使用的内容。 PostgreSQL 为 monitoring statistics 准备了完整的基础设施.研究统计(以及许多其他任务)的一种便捷方法是 pgAdmin您可以在其中选择表/函数/索引，并在对象浏览器(主窗口)的“统计”选项卡上获取所有数据。

3. 按照上面的描述进行操作，看看使用的索引是否真的加快了速度。

4. 如果查询规划器应该选择使用您的一个或多个索引但没有或产生不利影响，那么您的设置可能有问题，您需要研究 the basics of performance optimization: vacuum, analyze, cost parameters, memory usage, ...