postgresql - 复合索引或不复合索引(postgres)

Question

我有一个加载缓慢的列表页面，我想检查我的索引是否正确。

该表有 500 万行。每个列表都属于一个 channel_id。该网页仅显示一个 channel 的列表，已分页，因此第一个“过滤器”始终为“channel_id”。在这些示例中，为了简洁起见，我将省略偏移量和限制。

select * from listings where channel_id = 5;

列表还有一个状态列，默认情况下，该页面会在“有效”列表上打开。

select * from listings where channel_id = 5 and status = 'active';

因此我们在 (channel_id, status) 上创建了一个复合索引。

但我们还希望在 UI 中根据一系列其他属性(价格、数量、类型、sku、标题、has_errors、has_warnings、created_at、updated_at ... 等)过滤和排序表列

我从所有属性的复合索引开始，但这没有多大意义，因为您一次只能从 UI 对一列进行排序。

postgres 文档说:“如果其中一种查询类型比其他查询类型少得多，您可能会满足于只创建最匹配常见类型的两个索引”

所以听起来我应该创建几个单独的索引而不是一个巨大的复合索引。我的问题是:如果我知道我一次只处理一个 channel ，那么最好只在“created_at”上创建一个索引，还是仍然为每个索引中带有 channel_id 的每个属性创建复合索引，例如:( channel_id, created_at) 和另一个 (channel_id, title) 等等？

Answer 1

I'll omit the offset and limit for brevity in these examples.

但您还省略了与索引相关的 order by。

But we also want to filter and have sortable table columns in the UI, on a bunch of other attributes

我们只能针对特定查询提供特定建议。

在这种情况下，PostgreSQL Doc 的引述是适用的:您可能无法为所有可能的组合创建索引，因此选择最常见的并只处理它们。

因此，我建议您使用所有 where 和 order by 子句获取两个最常见的查询，并将它们添加到您的问题中。我们可以查看并提供建议。

或者，实际上最好是，您开始学习如何为自己编制索引 here .

最后，请注意分页查询是一个 special field of optimization并且 offset 通常是一种性能威胁，最好避免:参见 NoOffset (幻灯片以 PostgreSQL 基准测试为特色)。