什么类型的数据库索引有助于查询涉及比较两个字段?

Question

假设我有一个有 2 个小数字段 x 和 y 的表。 我想查询具有"x>= y"的记录。 我应该使用哪种类型的索引来提高此类查询的性能？

Answer 1

仅当条件是选择性的(即只有一小部分行满足条件时)，索引才有用。否则，顺序扫描是查找匹配行的最便宜的方法。

我可以想到两种方法来实现这一点：

1. 重写查询并创建匹配的表达式索引：

   CREATE INDEX ON mytable ((x - y));
   

   对表运行ANALYZE以收集索引表达式的统计信息。

   然后像这样重写查询：

   EXPLAIN SELECT * FROM mytable WHERE x - y >= 0;
   QUERY PLAN
   --------------------------------------------------------------------------------
   Bitmap Heap Scan on mytable  (cost=4.70..12.53 rows=55 width=20)
   Recheck Cond: ((x - y) >= '0'::double precision)
   ->  Bitmap Index Scan on mytable_expr_idx  (cost=0.00..4.69 rows=55 width=0)
   Index Cond: ((x - y) >= '0'::double precision)
   (4 rows)
   

2. 使用部分索引：

   CREATE INDEX ON mytable((bool 'TRUE')) WHERE x >= y;
   

   再次ANALYZE表以获取统计信息。

   此索引可用于原始查询：

   EXPLAIN SELECT * FROM mytable WHERE x >= y;
   QUERY PLAN
   ------------------------------------------------------------------------------------
   Index Scan using mytable_bool_idx on mytable  (cost=0.14..17.74 rows=333 width=20)
   (1 row)
   

   如果您索引选定的列而不是(小)虚拟值TRUE并且表格没有被大量修改，则可以将其设置为仅索引扫描，而根本不需要接触表。缺点是指数变大。你必须检查什么最适合你。

我的测试表包含 1000 行，其中 54 行符合条件。

所以第一种方法可能更好，因为估计值更好。 部分索引扫描仅使用表大小三分之一的愚蠢估计值。