Postgres在索引扫描时不使用索引是更好的选择

索引扫描 - >位图索引扫描 - >顺序扫描

对于少数行,运行索引扫描是值得的.随着要返回的行数越多(表的百分比越高,并且取决于数据分布,值频率和行宽),就越有可能在一个数据页上找到多行.然后切换到位图索引扫描是值得的.无论如何,一旦必须访问大部分数据页,运行顺序扫描,过滤剩余行并完全跳过索引的开销会更便宜.

Postgres切换到顺序扫描,期望找到rows=263962,已经是整个表的3%.(虽然只是rows=47935实际发现,见下文.)

更多相关答案:

使用索引或位图索引扫描对时间戳进行高效的PostgreSQL查询？

小心强制查询计划

你不能直接在Postgres中强制使用某个计划程序方法,但是为了进行调试,你可以使其他方法看起来非常昂贵.请参阅手册中的" 计划员方法配置 ".

SET enable_seqscan = off(如在另一个答案中建议的那样)对顺序扫描执行此操作.但这仅用于在会话中进行调试.难道不,除非你知道自己在做什么用这个作为生产一般的设置.它可以强制荒谬的查询计划.引用手册:

这些配置参数提供了影响查询优化器选择的查询计划的粗略方法.如果优化程序为特定查询选择的默认计划不是最佳,则 临时解决方案是使用这些配置参数之一强制优化程序选择不同的计划.提高优化程序选择的计划质量的更好方法包括调整平面成本常数(请参阅第18.7.2节),ANALYZE手动运行,增加default_statistics_target配置参数的值,以及增加使用特定列收集的统计信息量ALTER TABLE SET STATISTICS.

这已经是你需要的大部分建议了.

保持PostgreSQL有时选择错误的查询计划

在这种特殊情况下,Postgres预计点击次数email_activities.email_recipient_id比实际发现次数多5-6次:

估计rows=227007与actual ... rows=40789
估计rows=263962vs.actual ... rows=47935

如果您经常运行此查询,则需要ANALYZE查看更大的样本以获得有关特定列的更准确统计信息.你的桌子很大(~10M行),所以要:

ALTER TABLE email_activities ALTER COLUMN email_recipient_id
SET STATISTICS 3000;  -- max 10000, default 100

然后 ANALYZE email_activities;

万不得已的措施

在极少数情况下,您可能会SET LOCAL enable_seqscan = off在单独的事务中或在具有自己环境的函数中强制使用索引.喜欢:

CREATE OR REPLACE FUNCTION f_count_dist_recipients(_email_campaign_id int, _limit int)
  RETURNS bigint AS
$func$
   SELECT COUNT(DISTINCT a.email_recipient_id)
   FROM   email_activities a
   WHERE  a.email_recipient_id IN (
      SELECT id
      FROM   email_recipients
      WHERE  email_campaign_id = $1
      LIMIT  $2)       -- or consider query below
$func$  LANGUAGE sql VOLATILE COST 100000 SET enable_seqscan = off;

该设置仅适用于函数的本地范围.

警告:这只是一个概念证明.从长远来看,即使是这种不那么激进的人工干预也可能会让你感到痛苦.基数,价值频率,您的架构,全局Postgres设置,一切都随着时间而变化.您将升级到新的Postgres版本.您现在强制执行的查询计划可能会在以后成为一个非常糟糕的主意.

通常,这只是解决您的设置问题的方法.更好地找到并修复它.

替代查询

问题中缺少必要的信息,但是这个等效的查询可能更快,更可能在(email_recipient_id)上使用索引- 越来越多的更大LIMIT.

SELECT COUNT(*) AS ct
FROM  (
   SELECT id
   FROM   email_recipients
   WHERE  email_campaign_id = 1607
   LIMIT  43000
   ) r
WHERE  EXISTS (
   SELECT 1
   FROM   email_activities
   WHERE  email_recipient_id = r.id);