Postgres中SELECT *查询计划耗时38秒的诊断咨询

问题现象 #

有一张lead表在查询计划阶段处理异常缓慢，执行以下命令：

explain (analyze, verbose, buffers) select * from lead

生成的仅包含Seq Scan的查询计划，规划耗时长达36秒，而执行仅耗时39ms。该问题仅出现在生产环境，其他环境及其他表均无此现象。

查询计划输出 #

Seq Scan on public.lead  (cost=0.00..63.08 rows=208 width=1550) (actual time=1.455..5.881 rows=208 loops=1)
  Output: ...columns...
  Buffers: shared read=61
Query Identifier: -594205834999675794
Planning:
  Buffers: shared hit=285790 read=52222 written=4
Planning Time: 38160.641 ms
Execution Time: 39.051 ms

已尝试的操作 #

• vacuum（含analyze、full参数）
• 对该表及所有依赖表执行analyze
• reindex table
• 移除约束
• 移除索引

注意：克隆该表后，即使重新添加所有约束，查询计划也恢复正常。查询pg_locks无返回结果。

实例配置 #

使用t4g.micro Supabase实例默认配置（2核CPU、1GB内存），关键参数如下：

即使对lead.id执行index-only scan，查询计划仍耗时4秒。

表定义 #

create table
  public.lead (
    id bigint generated always as identity,
    created_at timestamp without time zone not null default now(),
    created_by uuid null,
    deleted_at timestamp without time zone null,
    reference_code text null,
    first_name text null,
    last_name text null,
    prospect_id bigint not null,
    listing_id bigint not null,
    source text not null,
    enquiry_text text null,
    phone_number text null,
    mobile_phone_number text null,
    email_contact_permission boolean not null default false,
    phone_contact_permission boolean not null default false,
    postal_contact_permission boolean not null default false,
    home_location text null,
    work_location text null,
    current_housing_status public.current_housing_status null,
    number_of_people_in_household integer null,
    has_dependents boolean null,
    date_of_birth date null,
    key_worker_role text null,
    wheelchair_access_required boolean null,
    annual_household_income bigint null,
    cash_deposit bigint null,
    source_id bigint null,
    raw_data jsonb null,
    constraint lead_pkey primary key (id),
    constraint lead_source_source_id_unique unique (source, source_id),
    constraint lead_created_by_fkey foreign key (created_by) references auth.users (id),
    constraint lead_listing_id_fkey foreign key (listing_id) references listing (id),
    constraint lead_prospect_id_fkey foreign key (prospect_id) references prospect (id)
  ) tablespace pg_default;

create index if not exists lead_prospect_id_idx on public.lead using btree (prospect_id) tablespace pg_default;

create index if not exists lead_listing_id_idx on public.lead using btree (listing_id) tablespace pg_default;

create index if not exists lead_created_by_idx on public.lead using btree (created_by) tablespace pg_default;

create index if not exists lead_deleted_at_idx on public.lead using btree (deleted_at) tablespace pg_default;

create trigger generate_reference_code_for_lead before insert on lead for each row
execute function generate_reference_code_for_lead ();

可能的根因及解决方案 #

1. 系统目录或表元数据损坏 #

从查询计划的缓冲区统计Planning: shared hit=285790 read=52222可以看出，规划阶段读取了大量系统目录数据，而克隆表后恢复正常，说明原表元数据可能存在损坏或异常。

• 解决方案：
  1. 执行REINDEX SYSTEM重建系统索引，修复系统目录的索引损坏问题
  2. 若无效，直接将原表数据迁移到新表（已验证的克隆方式），删除原表后将新表重命名为原表名，保留所有约束和索引

2. 统计信息异常（即使执行过ANALYZE） #

虽然执行过analyze，但生产环境的并发写入可能导致统计信息不一致，或统计信息未正确更新。

• 解决方案：
  1. 强制更新表统计信息：ANALYZE VERBOSE public.lead;
  2. 检查pg_statistic表中lead表的统计条目，确认无缺失或数据错误

3. 内存不足导致频繁磁盘IO #

实例仅1GB内存，shared_buffers为256MB，规划阶段加载大量系统目录数据时内存不足，触发频繁磁盘IO，拉长规划时间。

• 解决方案：
  1. 临时调整参数：将work_mem提高到8MB，maintenance_work_mem提高到128MB（注意Supabase实例的参数调整限制）
  2. 长期来看，升级到更高配置的实例（如t4g.small，2核2GB内存）

4. 触发器函数依赖异常 #

触发器generate_reference_code_for_lead依赖的同名函数可能存在问题，比如引用损坏对象或执行效率极低，导致规划阶段解析函数耗时过长。

• 解决方案：
  1. 检查触发器函数定义，确认无异常引用
  2. 临时禁用触发器后测试规划耗时，若恢复正常则修复或重写该函数

内容的提问来源于stack exchange，提问作者isaac.harrisholt