PostgreSQL slow-query diagnosis: gather evidence before adding indexes
A tutorial on diagnosing PostgreSQL slow queries recommends enabling pg_stat_statements, logging slow statements and examining plans with EXPLAIN (ANALYZE, BUFFERS) before changing indexes. It advises ranking queries by cumulative execution time and notes that PostgreSQL does not support query hints, so a sequential scan may be appropriate for queries returning a large share of a table. The tutorial also covers stale statistics, low-selectivity predicates, unsuitable indexes and other issues. Its latency incident and three-second example are illustrative, and its claim that sequential scans become faster above a 30% threshold is not backed by a cited universal benchmark.
The tutorial provides a concrete workflow for teams running AI-generated SQL: identify queries with high cumulative execution time, inspect their execution plans and determine whether an index is appropriate instead of changing indexes based on intuition.
摘要:AI 帮你写的 SQL 跑得飞起,某天接口却突然变慢超时。这篇讲清:为什么"直接加索引"在 Postgres 上反而危险,以及用 pg_stat_statements 和 EXPLAIN ANALYZE 把慢查询揪出来的固定路线。
你没改过业务代码。那条列表查询平时几十毫秒,某天突然三秒,接口开始超时,PgBouncer 后面的 Postgres 连接被占满,整个服务跟着抖。SQL 甚至不是你写的——是 AI 帮写的。
你盯着监控,第一反应大概是:加个索引吧。
先别急。为什么加索引这件天经地义的事,在 Postgres 上会翻车?Postgres 刻意不提供查询 hint,你没法强制它用某个索引;更隐蔽的是,当一条查询要返回表里三成以上的数据时,全表扫描本来就比走索引快——你加了索引,优化器照样不理你,白加,写入反而变慢。那正确的起点是什么?不是"加索引",是"先拿到证据"。
为什么这几年 Postgres 越来越常见?AI 应用栈把它推成了新项目的默认库之一:pgvector 把向量检索塞进了数据库,Supabase、Neon 这类托管默认就是它,RAG、语义搜索不用再起一套专门的向量库。但它和老牌的 MySQL,底牌其实不一样:
#PostgreSQL#pg_stat_statements#EXPLAIN ANALYZE#PgBouncer#pgvector#pgBadger#auto_explain#Supabase#Neon