Postgres组合条件关联查询CPU占100%问题排查求助

执行包含两个WHERE条件的关联查询时，PostgreSQL服务器CPU占用率达100%，耗时极长；但移除任一条件后，查询可快速返回。涉及两张表：asset_asset（180万行）、asset_assetclassification（7000行），查询由Django自动生成。

待排查的慢查询语句 #

select count(1) from asset_asset INNER JOIN "asset_assetclassification" ON ("asset_asset"."classification_id" = "asset_assetclassification"."id") WHERE ("asset_assetclassification"."company_id" = 1 AND "asset_asset"."deleted_at" IS NULL);

慢查询执行计划 #

Aggregate  (cost=1684.74..1684.75 rows=1 width=8) (actual time=382010.081..382010.084 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=36981708
   ->  Nested Loop  (cost=381.65..1684.64 rows=41 width=0) (actual time=367.455..381892.265 rows=1836169 loops=1)
         Buffers: shared hit=36981708
         ->  Bitmap Heap Scan on public.asset_assetclassification  (cost=4.30..15.73 rows=3 width=4) (actual time=0.440..8.577 rows=7305 loops=1)
               Output: asset_assetclassification.id, asset_assetclassification.name, asset_assetclassification.image, asset_assetclassification.instance_id, asset_assetclassification.template_id, asset_assetclassification.company_id, asset_assetclassification.ecn, asset_assetclassification.ecn_status, asset_assetclassification.ecr, asset_assetclassification.ecr_status, asset_assetclassification.snapshot_date, asset_assetclassification.snapshot_model_id, asset_assetclassification.parent_id, asset_assetclassification.unique_field, asset_assetclassification.serial_format, asset_assetclassification.serial_help_text, asset_assetclassification.overlam_proof_id, asset_assetclassification.proof_id, asset_assetclassification.eci_code, asset_assetclassification.construct_group_id, asset_assetclassification.snapshot_version, asset_assetclassification.status_note, asset_assetclassification.asssetplatform_id, asset_assetclassification.sets, asset_assetclassification.change_reason, asset_assetclassification.generated_marking_spec, asset_assetclassification.serial_prefix, asset_assetclassification.tagging_image, asset_assetclassification.packaging_label_config_id, asset_assetclassification.pdf_file_name, asset_assetclassification.is_being_edited, asset_assetclassification.ecn_status_pre_on_hold, asset_assetclassification.ecr_status_pre_on_hold, asset_assetclassification.internal_serial_rules, asset_assetclassification.email_activity_email, asset_assetclassification.email_activity_name, asset_assetclassification.custom_data
               Recheck Cond: (asset_assetclassification.company_id = 1)
               Heap Blocks: exact=419
               Buffers: shared hit=510
               ->  Bitmap Index Scan on asset_assetclassificatio_company_id_template_id_e_809bdeb8_uniq  (cost=0.00..4.30 rows=3 width=0) (actual time=0.401..0.401 rows=7305 loops=1)
                     Index Cond: (asset_assetclassification.company_id = 1)
                     Buffers: shared hit=91
         ->  Bitmap Heap Scan on public.asset_asset  (cost=377.36..555.84 rows=46 width=4) (actual time=52.067..52.116 rows=251 loops=7305)
               Output: asset_asset.classification_id
               Recheck Cond: ((asset_asset.deleted_at IS NULL) AND (asset_asset.classification_id = asset_assetclassification.id))
               Heap Blocks: exact=143442
               Buffers: shared hit=36981198
               ->  BitmapAnd  (cost=377.36..377.36 rows=46 width=0) (actual time=51.999..51.999 rows=0 loops=7305)
                     Buffers: shared hit=36837756
                     ->  Bitmap Index Scan on asset_asset_deleted_at_db53c84c  (cost=0.00..173.40 rows=9196 width=0) (actual time=51.341..51.341 rows=1836169 loops=7305)
                           Index Cond: (asset_asset.deleted_at IS NULL)
                           Buffers: shared hit=36809895
                     ->  Bitmap Index Scan on asset_asset_classification_id_02396f18  (cost=0.00..201.40 rows=9196 width=0) (actual time=0.015..0.015 rows=252 loops=7305)
                           Index Cond: (asset_asset.classification_id = asset_assetclassification.id)
                           Buffers: shared hit=27861
 Planning Time: 0.255 ms
 Execution Time: 382010.270 ms
(28 rows)

仅保留asset_assetclassification.company_id = 1条件的查询及执行计划 #

查询语句 #

select count(1) from asset_asset INNER JOIN "asset_assetclassification" ON ("asset_asset"."classification_id" = "asset_assetclassification"."id") WHERE ("asset_assetclassification"."company_id" = 1);

执行计划 #

Aggregate  (cost=14072.34..14072.35 rows=1 width=8) (actual time=508.861..508.864 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=77728
   ->  Nested Loop  (cost=4.73..14051.69 rows=8260 width=0) (actual time=0.466..401.478 rows=1839134 loops=1)
         Buffers: shared hit=77728
         ->  Bitmap Heap Scan on public.asset_assetclassification  (cost=4.30..15.73 rows=3 width=4) (actual time=0.438..1.582 rows=7305 loops=1)
               Output: asset_assetclassification.id, asset_assetclassification.name, asset_assetclassification.image, asset_assetclassification.instance_id, asset_assetclassification.template_id, asset_assetclassification.company_id, asset_assetclassification.ecn, asset_assetclassification.ecn_status, asset_assetclassification.ecr, asset_assetclassification.ecr_status, asset_assetclassification.snapshot_date, asset_assetclassification.snapshot_model_id, asset_assetclassification.parent_id, asset_assetclassification.unique_field, asset_assetclassification.serial_format, asset_assetclassification.serial_help_text, asset_assetclassification.overlam_proof_id, asset_assetclassification.proof_id, asset_assetclassification.eci_code, asset_assetclassification.construct_group_id, asset_assetclassification.snapshot_version, asset_assetclassification.status_note, asset_assetclassification.asssetplatform_id, asset_assetclassification.sets, asset_assetclassification.change_reason, asset_assetclassification.generated_marking_spec, asset_assetclassification.serial_prefix, asset_assetclassification.tagging_image, asset_assetclassification.packaging_label_config_id, asset_assetclassification.pdf_file_name, asset_assetclassification.is_being_edited, asset_assetclassification.ecn_status_pre_on_hold, asset_assetclassification.ecr_status_pre_on_hold, asset_assetclassification.internal_serial_rules, asset_assetclassification.email_activity_email, asset_assetclassification.email_activity_name, asset_assetclassification.custom_data
               Recheck Cond: (asset_assetclassification.company_id = 1)
               Heap Blocks: exact=419
               Buffers: shared hit=510
               ->  Bitmap Index Scan on asset_assetclassificatio_company_id_template_id_e_809bdeb8_uniq  (cost=0.00..4.30 rows=3 width=0) (actual time=0.398..0.398 rows=7305 loops=1)
                     Index Cond: (asset_assetclassification.company_id = 1)
                     Buffers: shared hit=91
         ->  Index Only Scan using asset_asset_classification_id_02396f18 on public.asset_asset  (cost=0.43..4586.69 rows=9196 width=4) (actual time=0.002..0.033 rows=252 loops=7305)
               Output: asset_asset.classification_id
               Index Cond: (asset_asset.classification_id = asset_assetclassification.id)
               Heap Fetches: 301998
               Buffers: shared hit=77218
 Planning Time: 1.152 ms
 Execution Time: 508.979 ms
(20 rows)

仅保留asset_asset.deleted_at IS NULL条件的查询 #

查询语句 #

select count(1) from asset_asset INNER JOIN "asset_assetclassification" ON ("asset_asset"."classification_id" = "asset_assetclassification"."id") WHERE ("asset_asset"."deleted_at" IS NULL);

该查询可快速返回结果。

问题原因分析 #

1. 行数预估严重偏差：
   慢查询中，PostgreSQL预估asset_assetclassification符合条件的行数仅为3行，但实际返回7305行；同时预估关联后的asset_asset行数为46行，实际却有183万行。这种偏差直接导致优化器选择了低效的执行路径。

2. 重复的BitmapAnd操作开销：
   慢查询针对asset_asset的每个分类ID，都执行一次BitmapAnd操作——将deleted_at IS NULL的全表索引（183万行）和classification_id = ?的索引（约250行）做交集。该操作被重复执行7305次，累计的IO和CPU开销直接导致CPU满载、耗时剧增。

3. 执行路径选择差异：
   单条件查询时，优化器选择了更高效的Index Only Scan（仅索引扫描），无需每次做Bitmap交集操作，因此性能正常。

4. 统计信息过时：
   行数预估偏差通常源于统计信息不准确，PostgreSQL未及时更新表的统计数据，导致优化器无法判断实际数据分布，进而选择错误的执行计划。

内容的提问来源于stack exchange，提问作者Greek Freak