小开

假设您询问的是许多数据库中常见的“索引提示”特性，PostgreSQL 不提供这样的特性。这是 PostgreSQL 团队有意做出的决定。一个很好的概述为什么和什么你可以做而不是可以找到给你。原因基本上是，这是一种性能黑客行为，随着数据的更改，这种行为往往会在以后引发更多问题，而 PostgreSQL 的优化器可以根据统计数据重新评估计划。换句话说，今天可能是一个好的查询计划可能不会一直是一个好的查询计划，而索引提示会一直强制执行特定的查询计划。

作为一个非常钝的锤子，对于测试非常有用，你可以使用 enable_seqscan和 enable_indexscan参数。参见:

这些是 不适合正在进行的生产使用。如果查询计划选择有问题，您应该看到跟踪查询性能问题的文档。不要只是设置 enable_参数然后走开。

除非你有一个很好的理由使用索引，Postgres 可能是正确的选择。为什么？

对于小型表，执行顺序扫描更快。
当数据类型不匹配时，Postgres 不使用索引，您可能需要包含适当的强制转换。
您的计划设置可能会导致问题。

参见这个旧的新闻组帖子。

小开

这个问题本身是非常无效的。强制(例如，通过启用 _ seqcan = off)是一个非常糟糕的主意。检查它是否会更快可能是有用的，但是生产代码永远不应该使用这样的技巧。

相反，请解释分析您的查询，阅读它，并找出为什么 PostgreSQL 选择了糟糕的(在您看来)计划。

在网上有一些工具可以帮助阅读解释分析输出-其中之一是 Explain.depesz.com-我写的。

另一个选择是加入 Freenode irc 网络上的 # postgreql 通道，并与那里的家伙交谈，以帮助你-因为优化查询不是一个“问一个问题，得到答案快乐”的问题。它更像是一次对话，有很多东西需要检查，很多东西需要学习。

小开

也许这是唯一合理的理由

set enable_seqscan=false

当您正在编写查询时，希望快速查看如果表中有大量数据，查询计划实际上会是什么。当然，如果仅仅因为数据集太小而需要快速确认查询没有使用索引，也可以这样做。

小开

有时，PostgreSQL 无法为特定条件选择最佳索引。例如，假设有一个事务表，它有几百万行，其中每天有几百行，该表有四个索引: action _ id、 client _ id、 date 和 description。要运行以下查询:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
description = 'Refund'
GROUP BY client_id

PostgreSQL 可能会选择使用索引 transactions _ description _ idx 而不是 actions _ date _ idx，这可能会导致查询花费几分钟而不是一秒钟。如果是这种情况，您可以通过如下修改条件来强制使用日期索引:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
description||'' = 'Refund'
GROUP BY client_id

小开

有一个技巧可以推送 postgres，使其更喜欢在子查询中添加一个 OFFSET 0的 seqscanner

当您只需要 n 个前/后元素时，这对于优化链接大/大表的请求非常方便。

假设您正在寻找前/后20个元素，这些元素涉及多个具有100k (或更多)条目的表，如果您要寻找的是前100或1000个条目，那么在所有数据上建立/链接所有查询就没有意义了。例如，在这个场景中，进行顺序扫描的速度要快10倍以上。

见如何防止 Postgres 内联子查询？

小开

DR

运行以下三个命令并检查问题是否已修复:

ANALYZE;
SET random_page_cost = 1.0;
SET effective_cache_size = 'X GB';    # replace X with total RAM size minus 2 GB

请继续阅读以获取更多的细节和背景信息。

步骤1: 分析表

作为修复这个问题的第一个简单尝试，作为数据库超级用户运行 ANALYZE;命令以更新所有表统计信息。来自文件:

查询规划器使用这些统计信息来帮助确定最有效的查询执行计划。

步骤2: 设置正确的随机页面开销

索引扫描需要非顺序的磁盘页提取。PostgreSQL 使用 random_page_cost配置参数来估计这种非顺序获取相对于顺序获取的成本。来自文件:

降低这个值[ ... ]将导致系统更喜欢索引扫描; 提高它将使索引扫描看起来相对更昂贵。

默认值是 4.0，因此假设与顺序获取相比，一般的成本系数为4，同时考虑到缓存效果。但是，如果数据库存储在 固态硬盘上，那么实际上应该根据文档将 random_page_cost设置为 1.1:

与顺序存储相比，具有较低随机读取成本的存储，例如固态驱动器，也可以用较低的 random_page_cost值(例如 1.1)来更好地建模。

另外，如果索引大部分(甚至全部)缓存在 RAM 中，那么索引扫描总是比磁盘服务的顺序扫描快。但是，查询规划程序不知道索引的哪些部分已经被缓存，因此可能做出不正确的决策。

如果经常使用数据库索引，并且系统有足够的 RAM，那么索引最终可能会被缓存。在这种情况下，可以将 random_page_cost设置为 1.0，甚至设置为低于 1.0的值，以便积极地更喜欢使用索引扫描(尽管文档建议不要这样做)。你将不得不尝试不同的价值观，看看什么适合你。

另外，您还可以考虑使用预热扩展来显式地将索引缓存到 RAM 中。

你可以这样设置 random_page_cost:

SET random_page_cost = 1.0;

步骤3: 设置正确的缓存大小

在拥有8 GB 或更多 RAM 的系统上，应该将 effective_cache_size配置参数设置为 PostgreSQL 通常可用于数据缓存的内存量。来自文件:

较高的值更有可能使用索引扫描，较低的值更有可能使用顺序扫描。

注意，这个参数不会改变 PostgreSQL 实际分配的内存量，而只是用于计算成本估算。一个合理的值(至少在专用的数据库服务器上)是总 RAM 大小减去2GB。默认值是 4 GB。

你可以这样设置 effective_cache_size:

SET effective_cache_size = '14 GB';   # e.g. on a dedicated server with 16 GB RAM

第四步: 永久性地解决问题

您可能希望使用 ALTER SYSTEM SET ...或 ALTER DATABASE db_name SET ...永久地设置新的配置参数值(全局或每个数据库)。有关设置参数的详细信息，请参阅文件。

步骤5: 额外资源

如果它仍然不工作，那么您可能还想看看有关服务器调优的 PostgreSQLWiki 页面。

小开

显然，在某些情况下，Postgre 可以通过重复相似的条件两次来暗示使用索引。

我观察到的具体情况是使用 PostGIS gin索引和内谓词，如下所示:

select *
from address
natural join city
natural join restaurant
where st_within(address.location, restaurant.delivery_area)
and restaurant.delivery_area ~ address.location

请注意，PostGIS 会自动将第一个谓词 st_within(address.location, restaurant.delivery_area)分解为 (restaurant.delivery_area ~ address.location) AND _st_contains(restaurant.delivery_area, address.location)，因此添加第二个谓词 restaurant.delivery_area ~ address.location是完全冗余的。然而，第二个谓词说服了规划者在 address.location上使用空间索引，在我需要的特定情况下，将运行时间提高了8倍。

小开

索引只能在某些情况下使用。

例如，值的类型与列的类型相匹配。
在与值进行比较之前，不对列进行操作。

给定一个客户表，其中有3列，所有列上都有3个索引。

create table customer(id numeric(10), age int, phone varchar(200))

数据库可能会尝试使用索引 idx _ age，而不是使用电话号码。

你可以通过对年龄进行操作来破坏索引年龄的使用:

 select * from customer where phone = '1235' and age+1 = 24

(尽管你要找的是23岁)

这当然是一个非常简单的例子，而且后进生的智力可能足以做出正确的选择。但有时没有其他办法，然后欺骗系统。

另一个例子是

select * from customer where phone = '1235' and age::varchar = '23'

但这可能比上述选择更为昂贵。

不幸的是，您不能像在 MSSQL 或 Sybase 中那样将索引的名称设置到查询中。

select * from customer (index idx_phone) where phone = '1235' and age = 23.

这将大大有助于避免这样的问题。

小开

在 PostgreSQL 中需要注意的一点是，如果您期望使用索引，但索引没有被使用，那么就需要对表进行 VACUUM 分析。

VACUUM ANALYZE schema.table;

这将更新计划程序使用的统计信息，以确定执行查询的最有效方法。这可能导致使用索引。

另一个需要检查的是类型。

是在一个 int8列上的索引，并且您正在使用数字查询吗？查询将工作，但不会使用索引。

如何强制 Postgres 使用特定的索引？

DR

步骤1: 分析表

步骤2: 设置正确的随机页面开销

步骤3: 设置正确的缓存大小

第四步: 永久性地解决问题

步骤5: 额外资源