PostgreSQL聚合查询为何比Fetch后Python端聚合慢？

我有两条针对reviews表的查询：
第一条查询（耗时约7ms）：

explain analyze
select * from reviews
    where
    product_id = 4922
    and time > now() - interval '2 month'

第二条聚合查询（耗时约3.2秒）：

explain analyze
SELECT
    max(reviews)
FROM
    reviews
WHERE
    product_id = 4922
    AND time > now() - interval '2 month';

reviews表有约870万行数据，已创建(product_id,time)复合索引，第一条查询正常使用该索引，但第二条聚合查询却选择了reviews单列索引，需要过滤数百万行数据。reviews列非空，我不理解为何PostgreSQL会做出这样的索引选择。

两条查询的explain analyze结果 #

聚合查询（慢查询）的执行计划： #

Result  (cost=911.01..911.02 rows=1 width=8) (actual time=3483.019..3483.020 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.43..911.01 rows=1 width=8) (actual time=3483.013..3483.014 rows=1 loops=1)
          ->  Index Scan Backward using reviews_reviews_idx on reviews  (cost=0.43..1341280.17 rows=1473 width=8) (actual time=3483.011..3483.012 rows=1 loops=1)
                Index Cond: (reviews IS NOT NULL)
"                Filter: ((product_id = 4922) AND (""time"" > (now() - '2 mons'::interval)))"
                Rows Removed by Filter: 3277989
Planning Time: 0.316 ms
Execution Time: 3483.043 ms

普通查询（快查询）的执行计划： #

Bitmap Heap Scan on reviews  (cost=39.54..5619.14 rows=1473 width=210) (actual time=1.571..6.689 rows=3610 loops=1)
"  Recheck Cond: ((product_id = 4922) AND (""time"" > (now() - '2 mons'::interval)))"
  Heap Blocks: exact=3582
  ->  Bitmap Index Scan on reviews_product_id_time_idx  (cost=0.00..39.17 rows=1473 width=0) (actual time=0.705..0.706 rows=3610 loops=1)
"        Index Cond: ((product_id = 4922) AND (""time"" > (now() - '2 mons'::interval)))"
Planning Time: 0.239 ms
Execution Time: 7.036 ms

我知道可以创建(product_id,time,reviews)复合索引来优化，但觉得现有索引应该足够支撑查询。

补充：reviews表DDL #

postgres@ubuntu-8gb-sin-1:~$ pg_dump -t reviews --schema-only postgres
--
-- PostgreSQL database dump
--

-- Dumped from database version 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: reviews; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.reviews (
    id bigint NOT NULL,
    daraz_id text NOT NULL,
    rating_score double precision NOT NULL,
    reviews bigint NOT NULL,
    client text NOT NULL,
    href text NOT NULL,
    "time" timestamp with time zone NOT NULL,
    hostcountry text,
    product_id integer
);


ALTER TABLE public.reviews OWNER TO postgres;

--
-- Name: reviews_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.reviews_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.reviews_id_seq OWNER TO postgres;

--
-- Name: reviews_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.reviews_id_seq OWNED BY public.reviews.id;


--
-- Name: reviews id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.reviews ALTER COLUMN id SET DEFAULT nextval('public.reviews_id_seq'::regclass);


--
-- Name: reviews reviews_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_pkey PRIMARY KEY (id);


--
-- Name: reviews_client_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_client_idx ON public.reviews USING btree (client);


--
-- Name: reviews_daraz_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_daraz_id_idx ON public.reviews USING btree (daraz_id);


--
-- Name: reviews_daraz_id_reviews_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_daraz_id_reviews_idx ON public.reviews USING btree (daraz_id, reviews);


--
-- Name: reviews_hostcountry_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_hostcountry_idx ON public.reviews USING btree (hostcountry);


--
-- Name: reviews_product_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_product_id_idx ON public.reviews USING btree (product_id);


--
-- Name: reviews_product_id_time_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_product_id_time_idx ON public.reviews USING btree (product_id, "time");


--
-- Name: reviews_rating_score_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_rating_score_idx ON public.reviews USING btree (rating_score);


--
-- Name: reviews_time_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_time_idx ON public.reviews USING btree ("time");


--
-- Name: reviews_reviews_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX reviews_reviews_idx ON public.reviews USING btree (reviews);


--
-- Name: reviews reviews_products_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_products_fk FOREIGN KEY (product_id) REFERENCES public.products(id);


--
-- PostgreSQL database dump complete
--

问题分析与解决方案 #

问题根源 #

PostgreSQL选择reviews单列索引，是因为优化器认为倒序扫描该索引找到第一条满足过滤条件的记录就能得到max值，只需返回1行数据，成本估算更低。但实际情况是，满足reviews IS NOT NULL的记录里，绝大多数不匹配product_id=4922和时间条件，导致扫描数百万行才找到目标，完全偏离预期。

核心原因：

1. 优化器对符合条件的记录数估算严重不准（执行计划估1473行，实际3610行），误判扫描reviews索引成本更低。
2. 优化器未利用reviews列非空的约束信息，仍添加reviews IS NOT NULL的索引条件，进一步干扰了计划选择。

解决方案 #

1. 更新统计信息

过时的统计信息是优化器误判的常见原因，执行以下命令更新表统计：

ANALYZE reviews;

更新后优化器能更精准估算记录数，大概率会自动选择正确的索引。

2. 创建覆盖索引（最优解）

(product_id,time)索引无法直接返回reviews列，若选择它需要回表查询。创建覆盖索引(product_id, time, reviews)后，优化器可直接从索引获取所有所需数据，无需回表，同时精准过滤条件：

CREATE INDEX reviews_product_id_time_reviews_idx ON public.reviews USING btree (product_id, "time", reviews);

这是针对此类聚合查询的最优索引设计。

3. 强制指定索引（临时方案）

若需快速生效，可使用索引提示强制优化器选择正确索引：

SELECT max(reviews)
FROM reviews
WHERE product_id = 4922
  AND time > now() - interval '2 month'
-- 强制使用(product_id,time)复合索引
INDEX reviews_product_id_time_idx;

但此方法属于硬编码，长期维护性差。

4. 删除无用索引（可选）

如果reviews单列索引对其他业务查询无价值，可直接删除避免优化器误选：

DROP INDEX reviews_reviews_idx;

注意：若有其他查询依赖该索引，请勿执行此操作。

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