postgresql - 如何在 postgresql 8.3.7 中的分区表上使用索引

Question

我有这样的情况，运行一个按分区表中的索引列过滤的查询，执行全表扫描。

显然，这是 postgresql 中的一个已知问题，并且有详细解释 here .

除了对每个分区执行查询，然后对所有结果执行 UNION 之外，还有更优雅的解决方法吗？

Answer 1

索引可以很好地只扫描 PostgreSQL 中的相关分区。但是，您必须正确设置所有内容才能使其正常工作，并且很容易错过 http://www.postgresql.org/docs/current/static/ddl-partitioning.html 中记录的一长串内容中的一个步骤。

要意识到的主要事情是，为了避免顺序扫描，您必须向 PostgreSQL 提供足够的信息，以便它可以证明某些分区不能包含您要查找的数据；然后将它们作为查询结果的潜在来源跳过。您链接到的文章指出这是 seq 扫描问题的解决方案:“如果您向每个分区的日期字段添加范围约束，则可以将此查询优化为一个循环，您首先查询“最新”分区然后工作向后，直到找到一个高于所有剩余分区范围的单个值。”--但没有显示您在更改后看到的改进计划。

您可能犯的一些常见错误:

-postgresql.conf文件中的constraint_exclusion参数默认关闭。使用该默认设置，您将无法获得预期的效果。

- 没有使用 CHECK 创建非重叠分区，这使计划者无法知道每个分区中的内容。有可能错过这一步，但仍然可以将您的数据正确地放入正确的分区，只是规划者不会知道这一点。

-没有在每个分区上建立索引，只在主表上创建一个。这将为您提供仅在相关分区上的顺序扫描，因此不像上面那样糟糕但也不好。

在即将发布的 PostgreSQL 版本中，有一些工作可以使这一切变得更容易(在 8.4 中设置 constraint_partition 是相当自动的，并且正在进行某种分区设置自动化)。现在，如果您仔细按照说明操作并避免所有这些问题，它应该会起作用。