postgresql - 在 WHERE 子句中对包含大量列的查询建立索引

Question

为公寓网站构建搜索引擎，但我不确定如何为 apartments 表编制索引。

查询示例:

• ...WHERE city_id = 1 AND size > 500 AND rooms = 2
• ...WHERE area_id = 2 AND ad_type = 'agent' AND price BETWEEN AND 14000
• ...WHERE area_id = 2 OR area_id = 4 AND published_at > '2016-01-01' AND ad_type = 1

如您所见，列可以变化很大，WHERE 子句中的列数最多可达 10，甚至可能更多。

• 我应该将所有这些都编入索引吗？
• 只有最常见的？

Answer 1

您必须弄清楚要在此查询中使用哪些 WHERE 子句，每个子句出现的频率以及每个条件的选择性。

• 除非迫不得已，否则不要为很少发生的查询建立索引。

• 使用多列索引，从那些将出现在 = 比较中的列开始。

• 关于多列索引中列的顺序，从那些将在查询中单独使用的列开始(索引可以用于仅包含其部分列的查询，前提是它们位于索引的开头)。

• 您可以忽略选择性较低的列，例如 gender。

例如，对于您的上述查询，如果它们都是频繁的并且所有列都是选择性的，那么这些索引会很好:

... ON apartments (city_id, rooms, size)

... ON apartments (area_id, ad_type, price)

... ON apartments (area_id, ad_type, published_at)

这些索引也可以用于只有 area_id 或 city_id 的 WHERE 子句。

索引太多不好。

如果上述方法会导致索引过多，例如因为用户可以为 WHERE 子句选择任意列，所以最好索引单个列或偶尔索引经常在一起的列对。

这样 PostgreSQL 就可以选择一个位图索引扫描来为一个查询组合多个索引。这比常规索引扫描效率低，但通常比顺序扫描好。