PostgreSQL复杂SELECT COUNT(*)查询性能优化与估算方案咨询

问题背景 #

当前执行一条SELECT COUNT(*)查询耗时约3.5秒，返回结果363244，目标是将耗时降至0.5秒左右，或提供精度±10%的估算结果。数据库环境为PostgreSQL 15.2 + pgroonga 2.4.7，包含约50万条products记录、55万条search记录、25万条products_locations记录。

原查询语句 #

SELECT count(*) 
FROM products p 
LEFT JOIN products_locations pl 
  ON pl.product_id = p.id
  AND pl.height<= 4552
  AND pl.height>= 4543
  AND pl.width<= 7351
  AND pl.width>= 7344
WHERE 1 = 1 
  AND p.id = ANY(
             SELECT product_id 
               FROM search 
             WHERE text &@* 'salt');

关键执行计划（EXPLAIN ANALYZE, BUFFERS） #

Aggregate  (cost=108362.57..108362.58 rows=1 width=8) (actual time=9030.004..9030.007 rows=1 loops=1)
  Buffers: shared hit=2513363 read=62175
  I/O Timings: shared/local read=4645.775
  ->  Nested Loop Left Join  (cost=104118.94..108361.37 rows=480 width=0) (actual time=773.642..8981.304 rows=363244 loops=1)
        Buffers: shared hit=2513363 read=62175
        I/O Timings: shared/local read=4645.775
        ->  Nested Loop  (cost=104118.51..107926.10 rows=480 width=16) (actual time=773.600..2851.648 rows=362887 loops=1)
              Buffers: shared hit=1438456 read=38071
              I/O Timings: shared/local read=326.060
              ->  HashAggregate  (cost=104118.09..104122.89 rows=480 width=16) (actual time=773.542..944.752 rows=362887 loops=1)
                    Group Key: search.product_id
                    Batches: 1  Memory Usage: 36881kB
                    Buffers: shared hit=2 read=24977
                    I/O Timings: shared/local read=206.245
                    ->  Index Scan using search_product_id_lang_text_idx on search  (cost=0.00..104116.89 rows=480 width=16) (actual time=56.756..537.866 rows=362887 loops=1)
                          Index Cond: (text &@* 'salt'::text)
                          Buffers: shared hit=2 read=24977
                          I/O Timings: shared/local read=206.245
              ->  Index Only Scan using products_id_idx on products p  (cost=0.42..7.92 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=362887)
                    Index Cond: (id = search.product_id)
                    Heap Fetches: 362887
                    Buffers: shared hit=1438454 read=13094
                    I/O Timings: shared/local read=119.815
        ->  Index Only Scan using products_locations_project_id_h_w_idx on products_locations pl  (cost=0.43..0.90 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=362887)
              Index Cond: ((product_id = p.id) AND (height <= 4552) AND (height >= 4543) AND (width <= 7351::numeric) AND (width >= 7344::numeric))
              Heap Fetches: 9254
              Buffers: shared hit=1074907 read=24104
              I/O Timings: shared/local read=4319.715
Planning:
  Buffers: shared hit=508 read=46
  I/O Timings: shared/local read=7.460
Planning Time: 66.997 ms
JIT:
  Functions: 16
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 3.342 ms, Inlining 0.000 ms, Optimization 0.640 ms, Emission 13.157 ms, Total 17.139 ms
Execution Time: 9124.662 ms

现有索引 #

Products表 #

CREATE INDEX products_id_idx ON public.products USING btree (id);

Products_locations表 #

CREATE INDEX products_locations_id_idx ON public.products_locations USING btree (id);
CREATE INDEX products_locations_height_idx ON public.products_locations USING btree (height);
CREATE INDEX products_locations_width_idx ON public.products_locations USING btree (width);
CREATE INDEX products_locations_product_id_idx ON public.products_locations USING btree (product_id);
CREATE INDEX products_locations_project_id_h_w_idx ON public.products_locations USING btree (product_id, height, width);

Search表 #

CREATE INDEX search_product_id_lang_text_idx ON public.search USING pgroonga (((product_id)::character varying), locale, text);

优化方案 #

1. 消除Heap Fetches，提升Index Only Scan效率 #

执行计划中products表的Index Only Scan出现362887次Heap Fetches，说明索引无法满足"仅索引扫描"的条件（数据页可见性未更新）。执行以下命令更新统计信息与可见性映射：

VACUUM ANALYZE products;

这会让PostgreSQL跳过堆表读取，直接从索引获取数据，减少IO耗时。

2. 查询改写：减少嵌套循环次数 #

原查询通过LEFT JOIN关联products_locations，但COUNT(*)实际统计的是所有匹配search条件的products记录（包括无对应locations的），等价于COUNT(p.id)。可以去掉LEFT JOIN，直接统计products数量，大幅减少IO操作：

SELECT COUNT(*)
FROM products p
WHERE p.id = ANY(
    SELECT product_id 
    FROM search 
    WHERE text &@* 'salt'
);

如果需求是统计存在符合条件locations的products，则改用EXISTS子查询避免循环扫描：

SELECT COUNT(*)
FROM products p
WHERE p.id = ANY(
    SELECT product_id 
    FROM search 
    WHERE text &@* 'salt'
)
AND EXISTS(
    SELECT 1 
    FROM products_locations pl
    WHERE pl.product_id = p.id
      AND pl.height BETWEEN 4543 AND 4552
      AND pl.width BETWEEN 7344 AND 7351
);

3. 预聚合符合条件的locations记录 #

先筛选出products_locations中符合height/width条件的product_id集合，再与search结果求交集，避免逐个产品扫描：

SELECT COUNT(*)
FROM (
    SELECT product_id 
    FROM search 
    WHERE text &@* 'salt'
    INTERSECT
    SELECT product_id 
    FROM products_locations
    WHERE height BETWEEN 4543 AND 4552
      AND width BETWEEN 7344 AND 7351
) AS combined;

4. 调整pgroonga索引结构 #

当前search表的索引包含冗余字段，创建仅包含查询所需字段的pgroonga索引，提升扫描效率：

CREATE INDEX search_text_product_id_idx ON public.search USING pgroonga (text, product_id);

5. 禁用JIT编译 #

执行计划中JIT存在额外开销，临时禁用可减少耗时：

SET jit = off;

估算方案（精度±10%） #

如果无法快速优化查询，可通过以下方式快速估算结果：

1. 基于统计信息估算 #

利用现有表统计数据计算比例，无需全表扫描：

-- 计算search中匹配'salt'的product_id占比
WITH search_ratio AS (
    SELECT (SELECT count(*) FROM search WHERE text &@* 'salt')::numeric / (SELECT count(*) FROM search) AS ratio
),
-- 计算products_locations中符合条件的product_id占比
location_ratio AS (
    SELECT (SELECT count(DISTINCT product_id) FROM products_locations WHERE height BETWEEN 4543 AND 4552 AND width BETWEEN 7344 AND 7351)::numeric / (SELECT count(DISTINCT product_id) FROM products_locations) AS ratio
)
-- 估算结果（根据需求选择：统计所有匹配search的products则去掉* location_ratio）
SELECT (SELECT count(*) FROM products) * (SELECT ratio FROM search_ratio) * (SELECT ratio FROM location_ratio) AS estimated_count;

2. 采样估算 #

用TABLESAMPLE快速采样获取比例，速度极快且精度可控：

-- 采样估算search中匹配'salt'的product_id数量
SELECT (count(*) * 10)::int AS estimated_search_count
FROM search TABLESAMPLE SYSTEM(10)
WHERE text &@* 'salt';

-- 采样估算products_locations中符合条件的product_id数量
SELECT (count(DISTINCT product_id) * 10)::int AS estimated_location_product_count
FROM products_locations TABLESAMPLE SYSTEM(10)
WHERE height BETWEEN 4543 AND 4552 AND width BETWEEN 7344 AND 7351;

-- 最终估算（统计交集取最小值，统计所有匹配search的取estimated_search_count）
SELECT least(estimated_search_count, estimated_location_product_count) AS estimated_total;

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