主页 / dba / 问题 / 187923
Accepted
Hassan Baig
Asked: 2017-10-08 05:02:28 +0800 CST

优化简单 SELECT 查询的缓慢性能

  • 772

我有一个名为“链接”的应用程序，其中 1）用户聚集在组中并添加其他用户，2）在所述组中互相发布内容。组由links_group我的 postgresql 9.6.5 DB 中的表定义，而他们在其中发布的回复由links_reply表定义。总体而言，数据库的性能很棒。

然而，表SELECT上的一个查询links_reply始终显示在 slow_log 中。它花费的时间超过 500 毫秒，并且比我在大多数其他 postgresql 操作中遇到的慢约 10 倍。

我使用 Django ORM 来生成查询。这是 ORM 调用replies = Reply.objects.select_related('writer__userprofile').filter(which_group=group).order_by('-submitted_on')[:25]：本质上，这是为给定的组对象选择最新的 25 个回复。它还选择关联对象user和userprofile对象。

这是我的慢日志中相应 SQL 的示例：LOG: duration: 8476.309 ms 语句： 

SELECT

    "links_reply"."id",             "links_reply"."text", 
    "links_reply"."which_group_id", "links_reply"."writer_id",
    "links_reply"."submitted_on",   "links_reply"."image",
    "links_reply"."device",         "links_reply"."category", 

    "auth_user"."id",               "auth_user"."username", 

    "links_userprofile"."id",       "links_userprofile"."user_id",
    "links_userprofile"."score",    "links_userprofile"."avatar" 

FROM 

    "links_reply" 
    INNER JOIN "auth_user" 
        ON ("links_reply"."writer_id" = "auth_user"."id") 
    LEFT OUTER JOIN "links_userprofile" 
        ON ("auth_user"."id" = "links_userprofile"."user_id") 
WHERE "links_reply"."which_group_id" = 124479 
ORDER BY "links_reply"."submitted_on" DESC 
LIMIT 25

在此处查看解释分析结果：https ://explain.depesz.com/s/G4X索引扫描（向后）似乎一直在吃光。

这是输出\d links_reply：

Table "public.links_reply"
     Column     |           Type           |                        Modifiers                         
----------------+--------------------------+----------------------------------------------------------
 id             | integer                  | not null default nextval('links_reply_id_seq'::regclass)
 text           | text                     | not null
 which_group_id | integer                  | not null
 writer_id      | integer                  | not null
 submitted_on   | timestamp with time zone | not null
 image          | character varying(100)   | 
 category       | character varying(15)    | not null
 device         | character varying(10)    | default '1'::character varying
Indexes:
    "links_reply_pkey" PRIMARY KEY, btree (id)
    "category_index" btree (category)
    "links_reply_submitted_on" btree (submitted_on)
    "links_reply_which_group_id" btree (which_group_id)
    "links_reply_writer_id" btree (writer_id)
    "text_index" btree (text)
Foreign-key constraints:
    "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
    "links_reply_writer_id_fkey" FOREIGN KEY (writer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_groupseen" CONSTRAINT "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

这是一张大桌子（约 2500 万行）。它运行的硬件有 16 个内核和 60 GB 内存。它与 python 应用程序共享这台机器。但是我一直在监控服务器的性能，我没有看到那里的瓶颈。

有什么办法可以提高这个查询的性能吗？请就我在这里的所有选项（如果有）提供建议。

请注意，直到上周，此查询的性能都非常好。从那以后发生了什么变化？我执行了数据库（在单独的虚拟机上），然后从 Postgresql 9.3.10 升级到 9.6.5 pg_dump。pg_restore我还使用了一个之前调用的连接池pgbouncer，我还没有在我迁移到的新 VM 上配置它。而已。

最后，我还注意到（从用户体验）到上周创建的所有组对象，查询仍然执行得很快。但是现在正在创建的所有新对象都在产生缓慢的日志。这可能是某种索引问题，特别是索引问题links_reply_submitted_on吗？

更新：规定的优化确实扭转了局面。看一看：

在此处输入图像描述

postgresql performance

1 个回答

  1. Best Answer

    怀疑的主要问题（概要）

    1. 您需要ANALYZE在使用pg_upgrade. 不复制表统计信息。也可能调整 autovacuum 设置。

    2. 多列索引应该更好(which_group_id, submitted_on DESC)地服务于这个查询。

    询问

    格式化查询没有噪音，并带有表别名以获得更好的可读性：

    SELECT lr.id, lr.text, lr.which_group_id, lr.writer_id
     , lr.submitted_on, lr.image, lr.device, lr.category
     , au.id, au.username
     , lu.id, lu.user_id, lu.score, lu.avatar
FROM   links_reply            lr
JOIN   auth_user              au ON au.id = lr.writer_id
LEFT   JOIN links_userprofile lu ON lu.user_id = au.id
WHERE  lr.which_group_id = 119287
ORDER  BY lr.submitted_on DESC
LIMIT  25;

    我认为查询本身没有任何问题。

    索引损坏？

    （我不这么认为。）

    这可能是某种索引问题吗？

    如果您怀疑腐败，请运行REINDEX. 手册建议：

    REINDEX INDEX 如果您怀疑用户表上的索引损坏，您可以简单地使用或重建该索引或表上的所有索引REINDEX TABLE。

    在并发访问的情况下：锁定在几个方面不同于从头开始删除和重新创建索引。手册：

    REINDEX类似于索引的删除和重新创建，因为索引内容是从头开始重建的。然而，锁定的考虑是相当不同的。REINDEX锁定索引的父表的写入但不读取。它还对正在处理的特定索引进行排他锁，这将阻止尝试使用该索引的读取。相反，DROP INDEX暂时在父表上获得排他锁，阻止写入和读取。随后CREATE INDEX锁定写入但不读取；由于索引不存在，没有读取会尝试使用它，这意味着不会出现阻塞，但读取可能会被迫进行昂贵的顺序扫描。

    如果这仍然是并发操作的问题，请考虑CREATE INDEX CONCURRENTLY创建新的重复索引，然后在单独的事务中删除旧索引。

    表统计

    但是，看起来很像表统计信息是实际问题。从您的查询计划中引用：

    在 links_reply 上使用 links_reply_submitted_on 向后索引扫描
（成本=0.44..1,664,030.07行=2,001宽度=50）
（实际时间=522.811..716.414行=25循环=1）
    过滤器：（which_group_id = 119287）
    过滤器删除的行：1721320

    大胆强调我的。看起来 Postgres 将此查询计划基于误导性统计信息。它预计会有更多的命中，并且可能还低估了谓词的选择性which_group_id = 119287。最终过滤了 170 万行。这充满了不准确的表统计信息。还有一个可能的解释：

    升级主要版本时pg_upgrade不会将现有统计信息复制到新版本的数据库中。建议运行VACUUM ANALYZE或至少ANALYZE在pg_upgrade. 该工具甚至会提示提醒您。手册：

    由于优化器统计信息不是由 传输的pg_upgrade，因此将指示您运行命令以在升级结束时重新生成该信息。您可能需要设置连接参数以匹配您的新集群。

    如果您不这样做，则表将没有当前统计信息，直到对表的足够写入（或其他一些实用程序命令，例如CREATE INDEX或ALTER TABLE动态更新某些统计信息）触发自动清理。

    对于任何转储/恢复周期（在您的情况下使用pg_dump& pg_restore）也是如此。转储中不包含表统计信息。

    你的桌子很大(~25M rows)。autovacuum 的默认设置将阈值定义为 row_count 加上固定偏移量的百分比。有时这不适用于大表，下一次自动分析需要相当长的时间。

    ANALYZE在表或整个数据库上运行手册。

    有关的：

    更好的索引

    ...索引问题，特别是links_reply_submitted_on索引？

    是的，那也是。索引"links_reply_submitted_on" btree (submitted_on)未针对查询中的模式进行优化：

    SELECT ...
FROM   links_reply            lr
JOIN   ...
WHERE  lr.which_group_id = 119287
ORDER  BY lr.submitted_on DESC
LIMIT  25

    就像我们在上面的查询计划中看到的那样，Postgres 使用索引扫描，从底部读取索引并过滤不匹配项。which_group_id如果所有（少数！）选择在最近有 25 行，这种方法可以相当快。which_group_id

    这个多列索引更合适：

    links_reply__which_group_id__submitted_on btree (which_group_id, submitted_on DESC)

    现在，Postgres 可以只为 selected 选择前 25 行，而which_group_id不管数据分布如何。

    有关的：

    更多解释

    关于你的观察：

    最后，我还注意到（从用户体验）到上周创建的所有组对象，查询仍然执行得很快。但是现在正在创建的所有新对象都在产生缓慢的日志。

    为什么？新对象可能还没有25个条目，因此 Postgres 必须不断扫描整个大索引以希望找到更多。虽然这对于您的旧索引和查询计划来说非常昂贵，但对于新索引（和更新的表统计信息）来说也是非常便宜的。

    此外，有了准确的表统计信息，Postgres 很可能会使用您的其他索引"links_reply_which_group_id" btree (which_group_id)快速获取少数现有行（如果超过 25 行，则进行排序）。但无论如何，我的新索引提供了更可靠的查询计划。

    小事

    您还可以做很多其他（次要）的事情，比如优化表格布局或调整 autovacuum 设置，但这个答案已经足够长了。有关的：

    你后来评论说：

    还删除了无关字段...

    仅检索您实际需要的列当然会有所帮助。另外，这样做。但这不是这里的主要问题。

    • 9

相关问题