sql - 导致 cpu 达到 100% 的 PostgreSQL 查询。给定 90k 条记录，成本为 7000 可以吗？

Question

我正在努力了解应该如何使用成本和实际时间来优化查询。我的应用程序是带有 PostgreSQL 9.1 数据库的 Rails 3。我的查询被 Delayed_job 使用:

EXPLAIN ANALYZE SELECT  "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2011-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2011-01-25 11:05:28.077144') OR locked_by = 'host:foo pid:2') AND failed_at IS NULL AND queue = 'authentication_emails')
ORDER BY priority ASC, run_at ASC LIMIT 5

或者:

EXPLAIN ANALYZE SELECT  "delayed_jobs".*
FROM "delayed_jobs"
WHERE ((run_at <= '2011-05-23 15:16:43.180810' AND (locked_at IS NULL OR locked_at < '2011-01-25 11:05:28.077144') OR locked_by = 'host:foo pid:2') AND failed_at IS NULL )
ORDER BY priority ASC, run_at ASC LIMIT 5

对于第一个查询，输出等于:

Limit  (cost=7097.57..7097.57 rows=1 width=1008) (actual time=35.657..35.657 rows=0 loops=1)
  ->  Sort  (cost=7097.57..7097.57 rows=1 width=1008) (actual time=35.655..35.655 rows=0 loops=1)
        Sort Key: priority, run_at
        Sort Method: quicksort  Memory: 25kB
        ->  Seq Scan on delayed_jobs  (cost=0.00..7097.56 rows=1 width=1008) (actual time=35.648..35.648 rows=0 loops=1)
              Filter: ((failed_at IS NULL) AND ((queue)::text = 'authentication_emails'::text) AND (((run_at <= '2011-05-23 15:16:43.18081'::timestamp without time zone) AND ((locked_at IS NULL) OR (locked_at < '2011-01-25 11:05:28.077144'::timestamp without time zone))) OR (locked_by = 'host:foo pid:2'::text)))
Total runtime: 35.695 ms

该表当前有 90k 条记录，范围为 0-200k。我们注意到此查询导致 CPU 峰值并导致瓶颈。从上面的解释信息中可以了解到什么。如果有索引应该添加到哪里？谢谢

DB Schema.. 表有 0 个索引。

  create_table "delayed_jobs", :force => true do |t|
    t.integer  "priority",   :default => 0
    t.integer  "attempts",   :default => 0
    t.text     "handler"
    t.text     "last_error"
    t.datetime "run_at"
    t.datetime "locked_at"
    t.datetime "failed_at"
    t.text     "locked_by"
    t.datetime "created_at",                :null => false
    t.datetime "updated_at",                :null => false
    t.string   "queue"
  end

Answer 1

分析

如果你会去this section of the PostgreSQL documentation ，您将了解计划员如何使用统计数据来估算成本。这是非常有用的信息!

如果您说该表有大约 90k 条记录(并使用 default costs )，那么行处理的成本将为:

90000 * (cpu_tuple_cost + cpu_operator_cost) = 90000 * 0.0125 = 1125

我们现在可以估算出您的表格占用了多少页:

(7097.56-1125)/seq_page_cost = 5972.56

这使得它大约为 46Mb(默认页面大小为 8k)。因此我假设你的 table 适合 shared_buffers ，甚至是默认值。

查看我还假设的平均行宽，该表是 mostly stored as MAIN .

接下来，您将使用 text 和 string 类型的字段作为谓词。不确定它们如何映射到 PostgreSQL 内部类型，但我假设它是 text。这种类型默认是可压缩的，因此 PostgreSQL 必须对每一行进行解压缩以检查谓词。我不确定在哪个阈值压缩后开始，请查看 this message (以及整个线程)。

结论

1. 您没有向我们展示真实的EXPLAIN(分析) 输出，因为我也不认为 35 毫秒查询会导致瓶颈，除了...
2. 您没有提到在瓶颈时刻有多少 session 在使用您的数据库，也不清楚该查询的运行频率。我认为它很受欢迎。
3. 您的表似乎适合内存，因此所有操作都将在任何情况下都受 CPU 限制。
4. 谓词中使用的值是可压缩的并且看起来是经过压缩的。

因此我说瓶颈来自于对数据并行运行的查询的峰值数量，这需要额外的 CPU 周期来解压缩。

怎么办？

1. 规范化您的表格。感觉“队列”列的选择性很低。考虑创建 external type (如 ENUM)，或使用适当的外键组织字典表。我也不确定 locked_by 列，它可以标准化吗？
2. 在 run_at 和 locked_at 列上创建索引。
3. Index ON priority, run_at 列对您的排序有益，但我怀疑它在这种情况下是否有用。我假设 priority 列的选择性较低，因此计划者更愿意在 run_at 上使用 Bitmap And 而不是 Index Scans > 和 locked_at 列。

我希望我在这里没有大错特错 :) 欢迎评论/更正!

附言让我知道您的情况如何。