sql - Postgres 忽略日期查询的聚集索引

Question

我有一个大表，我定期运行诸如 select date_att > date '2001-01-01' 之类的查询。我试图通过在 date_att 上对表进行聚类来提高这些查询的速度，但是当我通过解释分析运行这些查询时，它仍然选择顺序扫描表，即使是像 SELECT date_att from table where date_att > date 这样简单的查询'2001-01-01'。为什么会这样？我知道由于查询返回表的很大一部分，优化器将忽略索引，但由于表是由该属性聚集的，它不应该能够真正快速地对表进行二进制搜索到 date 的位置> '2001-01-01' 并返回之后的所有结果？此查询仍然需要与没有集群时一样多的时间。

Answer 1

您似乎混淆了两个概念:

表的 PostgreSQL 聚类

在 PostgreSQL 中根据索引对表进行聚类会将表行的顺序(存储在堆表中)与索引中的顺序在聚类时对齐。来自文档:

Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. http://www.postgresql.org/docs/9.3/static/sql-cluster.html

集群可能(经常)提高范围查询的查询速度，因为所选行存储在堆表附近巧合。没有任何东西可以保证这个顺序!因此，优化器不能假设它是真的。

例如如果您插入一个新行来满足您的 where 子句，它可能会被插入到表中的任何位置——例如存储 1990 年行的位置。因此，这个假设不成立:

but since the table is clustered by that attribute, shouldn't it be able to really quickly binary > search through the table to the point where date > '2001-01-01' and return all results after that?

这让我们想到了您提到的另一个概念:

聚集索引

这是完全不同的东西，PostgreSQL 根本不支持，但许多其他数据库(SQL Server、带有 InnoDB 的 MySQL 以及 Oracle，其中它被称为“索引组织表”)支持。

在那种情况下，表数据本身存储在一个索引结构中——没有单独的堆结构!因为它是一个索引，所以每次insert/update/delete 也会维护顺序。因此，您的假设是正确的，而且我确实希望上述数据库的行为符合您的预期(假设 date 列是聚类键!)。

希望澄清一下。