为何PostgreSQL中WHERE过滤器的简单值变更会致查询性能降5000倍？
2026-6-12
问题：查询条件仅修改
=0为=1后性能暴跌5000倍的原因 原始查询（执行耗时~9ms）
EXPLAIN ANALYZE SELECT DISTINCT project_sessions.id AS id FROM project_sessions JOIN traces ON project_sessions.id = traces.project_session_rowid JOIN spans ON traces.id = spans.trace_rowid WHERE project_sessions.project_id = 38::INTEGER AND project_sessions.start_time >= NOW() - INTERVAL '7 days' AND spans.parent_id IS NULL AND strpos(CAST((spans.attributes -> 'input' ->> 'value') AS VARCHAR), 'Hello'::VARCHAR) = 0::INTEGER;
执行计划：
Unique (cost=1.00..985.78 rows=88 width=4) (actual time=6.230..9.056 rows=116 loops=1) -> Merge Join (cost=1.00..983.36 rows=968 width=4) (actual time=6.229..9.020 rows=412 loops=1) Merge Cond: (traces.project_session_rowid = project_sessions.id) -> Nested Loop (cost=0.85..518491.15 rows=4303 width=4) (actual time=0.020..8.789 rows=1537 loops=1) -> Index Scan using ix_traces_project_session_rowid on traces (cost=0.42..24617.57 rows=879524 width=8) (actual time=0.007..0.782 rows=1565 loops=1) -> Index Scan using ix_spans_trace_rowid on spans (cost=0.43..0.55 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1565) Index Cond: (trace_rowid = traces.id) Filter: ((parent_id IS NULL) AND (strpos(((attributes -> 'input'::text) ->> 'value'::text), 'Hello'::text) = 0)) Rows Removed by Filter: 6 -> Index Scan using pk_project_sessions on project_sessions (cost=0.15..18.28 rows=88 width=4) (actual time=0.065..0.120 rows=116 loops=1) Filter: ((project_id = 38) AND (start_time >= (now() - '7 days'::interval))) Rows Removed by Filter: 278 Planning Time: 0.414 ms Execution Time: 9.262 ms (14 rows)
修改后的查询（执行耗时~45s）
仅将最后一个过滤条件的=0改为=1：
EXPLAIN ANALYZE SELECT DISTINCT project_sessions.id AS id FROM project_sessions JOIN traces ON project_sessions.id = traces.project_session_rowid JOIN spans ON traces.id = spans.trace_rowid WHERE project_sessions.project_id = 38::INTEGER AND project_sessions.start_time >= NOW() - INTERVAL '7 days' AND spans.parent_id IS NULL AND strpos(CAST((spans.attributes -> 'input' ->> 'value') AS VARCHAR), 'Hello'::VARCHAR) = 1::INTEGER;
执行计划：
Unique (cost=1.00..985.78 rows=88 width=4) (actual time=12.451..45195.655 rows=1 loops=1) -> Merge Join (cost=1.00..983.36 rows=968 width=4) (actual time=12.449..45195.653 rows=1 loops=1) Merge Cond: (traces.project_session_rowid = project_sessions.id) -> Nested Loop (cost=0.85..518491.15 rows=4303 width=4) (actual time=1.484..45195.479 rows=29 loops=1) -> Index Scan using ix_traces_project_session_rowid on traces (cost=0.42..24617.57 rows=879524 width=8) (actual time=0.008..272.550 rows=811778 loops=1) -> Index Scan using ix_spans_trace_rowid on spans (cost=0.43..0.55 rows=1 width=4) (actual time=0.055..0.055 rows=0 loops=811778) Index Cond: (trace_rowid = traces.id) Filter: ((parent_id IS NULL) AND (strpos(((attributes -> 'input'::text) ->> 'value'::text), 'Hello'::text) = 1)) Rows Removed by Filter: 1 -> Index Scan using pk_project_sessions on project_sessions (cost=0.15..18.28 rows=88 width=4) (actual time=0.097..0.158 rows=96 loops=1) Filter: ((project_id = 38) AND (start_time >= (now() - '7 days'::interval))) Rows Removed by Filter: 275 Planning Time: 0.553 ms Execution Time: 45195.707 ms
执行计划差异对比
PostgreSQL版本信息
SELECT version();
输出：
PostgreSQL 15.7 (Debian 15.7-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
涉及表结构
表project_sessions结构
phoenix=> \d+ project_sessions Table "public.project_sessions" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ------------+--------------------------+-----------+----------+----------------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('project_sessions_id_seq'::regclass) | plain | | | session_id | character varying | | not null | | extended | | | project_id | integer | | not null | | plain | | | start_time | timestamp with time zone | | not null | | plain | | | end_time | timestamp with time zone | | not null | | plain | | | Indexes: "pk_project_sessions" PRIMARY KEY, btree (id) "ix_project_sessions_end_time" btree (end_time) "ix_project_sessions_project_id" btree (project_id) "ix_project_sessions_start_time" btree (start_time) "uq_project_sessions_session_id" UNIQUE CONSTRAINT, btree (session_id) Foreign-key constraints: "fk_project_sessions_project_id_projects" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE Referenced by: TABLE "traces" CONSTRAINT "fk_traces_project_session_rowid_project_sessions" FOREIGN KEY (project_session_rowid) REFERENCES project_sessions(id) ON DELETE CASCADE Access method: heap
表traces结构
phoenix=> \d+ traces; Table "public.traces" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description -----------------------+--------------------------+-----------+----------+------------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('traces_id_seq'::regclass) | plain | | | project_rowid | integer | | not null | | plain | | | trace_id | character varying | | not null | | extended | | | start_time | timestamp with time zone | | not null | | plain | | | end_time | timestamp with time zone | | not null | | plain | | | project_session_rowid | integer | | | | plain | | | Indexes: "pk_traces" PRIMARY KEY, btree (id) "ix_traces_project_rowid" btree (project_rowid) "ix_traces_project_session_rowid" btree (project_session_rowid) "ix_traces_start_time" btree (start_time) "uq_traces_trace_id" UNIQUE CONSTRAINT, btree (trace_id) Foreign-key constraints: "fk_traces_project_rowid_projects" FOREIGN KEY (project_rowid) REFERENCES projects(id) ON DELETE CASCADE "fk_traces_project_session_rowid_project_sessions" FOREIGN KEY (project_session_rowid) REFERENCES project_sessions(id) ON DELETE CASCADE Referenced by: TABLE "spans" CONSTRAINT "fk_spans_trace_rowid_traces" FOREIGN KEY (trace_rowid) REFERENCES traces(id) ON DELETE CASCADE TABLE "trace_annotations" CONSTRAINT "fk_trace_annotations_trace_rowid_traces" FOREIGN KEY (trace_rowid) REFERENCES traces(id) ON DELETE CASCADE Access method: heap
表spans结构
phoenix=> \d+ spans; Table "public.spans" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ---------------------------------------+--------------------------+-----------+----------+-----------------------------------+----------+-------------+--------------+------------- id | integer | | not null | nextval('spans_id_seq'::regclass) | plain | | | trace_rowid | integer | | not null | | plain | | | span_id | character varying | | not null | | extended | | | parent_id | character varying | | | | extended | | | name | character varying | | not null | | extended | | | span_kind | character varying | | not null | | extended | | | start_time | timestamp with time zone | | not null | | plain | | | end_time | timestamp with time zone | | not null | | plain | | | attributes | jsonb | | not null | | extended | | | events | jsonb | | not null | | extended | | | status_code | character varying | | not null | 'UNSET'::character varying | extended | | | status_message | character varying | | not null | | extended | | | cumulative_error_count | integer | | not null | | plain | | | cumulative_llm_token_count_prompt | integer | | not null | | plain | | | cumulative_llm_token_count_completion | integer | | not null | | plain | | | llm_token_count_prompt | integer | | | | plain | | | llm_token_count_completion | integer | | | | plain | | | Indexes: "pk_spans" PRIMARY KEY, btree (id) "ix_cumulative_llm_token_count_total" btree ((cumulative_llm_token_count_prompt + cumulative_llm_token_count_completion)) "ix_latency" btree ((end_time - start_time)) "ix_spans_parent_id" btree (parent_id) "ix_spans_start_time" btree (start_time) "ix_spans_trace_rowid" btree (trace_rowid) "uq_spans_span_id" UNIQUE CONSTRAINT, btree (span_id) Check constraints: "ck_spans_`valid_status`" CHECK (status_code::text = ANY (ARRAY['OK'::character varying, 'ERROR'::character varying, 'UNSET'::character varying]::text[])) Foreign-key constraints: "fk_spans_trace_rowid_traces" FOREIGN KEY (trace_rowid) REFERENCES traces(id) ON DELETE CASCADE Referenced by: TABLE "dataset_examples" CONSTRAINT "fk_dataset_examples_span_rowid_spans" FOREIGN KEY (span_rowid) REFERENCES spans(id) ON DELETE SET NULL TABLE "document_annotations" CONSTRAINT "fk_document_annotations_span_rowid_spans" FOREIGN KEY (span_rowid) REFERENCES spans(id) ON DELETE CASCADE TABLE "span_annotations" CONSTRAINT "fk_span_annotations_span_rowid_spans" FOREIGN KEY (span_rowid) REFERENCES spans(id) ON DELETE CASCADE Access method: heap
原因分析
统计信息偏差导致执行计划选择错误
PostgreSQL查询优化器依赖表统计信息评估过滤条件的选择性（过滤后保留行数的比例）。strpos(...) = 0条件过滤后保留行数较多，优化器选择了高效路径：先从project_sessions取116个符合条件的会话，关联traces仅获取1565行，再关联spans过滤，整体计算量极小。
但strpos(...) = 1条件的选择性极高（最终仅返回1行），优化器的统计信息未准确预估这一点，仍沿用原计划：扫描81万多行traces，逐个关联spans过滤，绝大多数关联因不满足条件被丢弃，产生大量无效IO和计算，直接导致性能暴跌。缺乏针对性索引
spans表的attributes为jsonb类型，当前无针对attributes->'input'->>'value'的表达式索引，无法快速定位符合strpos(...) =1的行，只能对每个关联行进行全字段计算过滤，进一步放大性能损耗。
解决方案
更新统计信息
执行ANALYZE spans;让优化器获取该表最新统计数据，准确评估过滤条件的选择性，从而选择更优执行计划。创建表达式索引
针对查询过滤条件创建专用索引，例如：CREATE INDEX ix_spans_parent_input_value_prefix ON spans USING btree ((attributes->'input'->>'value')) WHERE parent_id IS NULL;该索引可快速定位
parent_id IS NULL且input.value前缀为Hello的行，大幅减少过滤计算量。强制调整执行顺序（临时方案）
修改查询表连接顺序，让优化器先从spans过滤符合条件的行，再关联traces和project_sessions：SELECT DISTINCT project_sessions.id AS id FROM spans JOIN traces ON spans.trace_rowid = traces.id JOIN project_sessions ON traces.project_session_rowid = project_sessions.id WHERE project_sessions.project_id = 38::INTEGER AND project_sessions.start_time >= NOW() - INTERVAL '7 days' AND spans.parent_id IS NULL AND strpos((spans.attributes -> 'input' ->> 'value'), 'Hello') = 1;此方式强制优化器优先处理选择性最高的
spans过滤条件，避免扫描大量无效traces行。
内容的提问来源于stack exchange，提问作者zyxue
相关产品推荐
相关产品推荐
