postgresql - 使用 Postgresql 和 Flask-SQLAlchemy 提高 COUNT(*) WHERE 的数据库性能

Question

我有一个运行 SQLAlchemy 和 PostgresQL 的 Flask 应用程序，可以处理大量数据。我们在前端显示的内容之一是仪表板，其中包含给定组织的多个汇总统计数据。最近这个端点运行速度非常慢，所以我一直在尝试优化它并提高性能。

我首先对 BaseQuery 进行子类化，并实现 SQLAlchemy 内置 .count() 的精简版本，该版本无需使用子查询即可进行计数。

优化查询

from sqlalchemy import func
from sqlalchemy.orm import lazyload
from flask_sqlalchemy import BaseQuery

class OptimisedQuery(BaseQuery):
    def optimised_count(query):
        count_query = query.options(lazyload('*')).statement.with_only_columns([func.count()]).order_by(None)
        return query.session.execute(count_query).scalar()

api/dashboard.py

@dashboards.route("/api/dashboard/stats", methods=["GET"])
    @authentication_required
    def stats(current_user):
        org = current_user.organization

        total_subscribers = Subscriber.query.filter_by(
            unsubscribed=False, organization=org
        ).optimised_count()

        total_conversations = SubscriberConversationState.query.filter_by(
            organization=org
        ).optimised_count()

        total_messages = Message.query.filter_by(
            organization=org
        ).optimised_count()

        total_unsubscribers = Subscriber.query.filter_by(
            unsubscribed=True, organization=org
        ).optimised_count()

        return jsonify(
            dict(
                total_subscribers=total_subscribers,
                total_conversations=total_conversations,
                total_messages=total_messages,
                total_unsubscribers=total_unsubscribers,
            )
        )

这绝对是朝着正确方向迈出的一步，显着减少了端点延迟。话虽这么说，加载仍然需要 9 到 15 秒，所以我深入研究 New Relic，发现其中一个查询(根据消息表计算)的性能仍然非常糟糕。下面的屏幕截图实际上是我能找到的最好的，大约 1.5 秒，但有时需要长达 6 秒。

这并不那么令人惊讶，因为 messages 表是其中最大的表(4,090,065 行)。然而，看看这个查询，它似乎尽可能精简，即使我要放弃 SQLAlchemy 并只编写纯 SQL。更奇怪的是，将 prod 数据库克隆到本地计算机并使用 pgbench 分析相同的查询后，查询运行速度快如闪电，平均延迟为 86.9 毫秒。

问题

• 最终的精简查询 SELECT count(*) AS count_1 FROM message WHERE %(param_1)s = messages.organization_id 是查询可以获得的最精简查询吗？
• 我发现了大量关于 COUNT 性能以及提高性能的策略的文章，但没有一篇针对 COUNT WHERE 查询的文章。是我可以在数据库构建中做一些事情来加快速度这个案例？ (索引等)
• 什么可能导致 pgbench 86.9 毫秒的延迟与超过 6 秒的生产运行时间之间存在差异？作为引用，应用程序托管在 Heroku 上，并利用 3 个 Standard-2X Web dynos 和一个Postgres Standard-0 插件。

Answer 1

pgbench 运行不断重复请求相同的 organization_id，因此数据可能已被缓存。此外，可能还有一些组织有更多的消息。因此，我对运行时的不同并不感到惊讶。

既然您已经阅读了the performance of count(*) ，我就不告诉你细节了。我看到两个选项:

1. 使用定期刷新的物化 View :

   CREATE MATERIALIZED VIEW message_count AS
   SELECT organization_id, count(*) AS c
   FROM messages
   GROUP BY organization_id;
   
   CREATE UNIQUE INDEX ON message_count (organization_id);
   

   然后你会得到稍微陈旧的数据，但你会很快。

2. 如果您需要精确的计数，请将 message_count 设为常规表，并向 messages 添加触发器，以便在任何时候更新相应的 message_count 行messages 已修改。

   这会减慢消息上的数据修改速度，但如果您确实需要频繁且快速地进行计数，那么可能值得付出努力。