PostgreSQL关联排序查询性能异常问题优化咨询

表结构与数据概况 #

• companies表约700行，users表3-4万行，stuff表数百万行
• stuff表每日新增2-3万行，用户插入量差异显著：部分用户单日插入数百行后月余无新增，部分用户每日插入数千行
• 表关联关系：stuff通过user_id外键关联users，users通过company_id外键关联companies，表结构SQL如下：

CREATE TABLE public.companies
(
    id uuid primary key not null default uuid_generate_v4()
);

CREATE TABLE public.users
(
    id         uuid primary key not null default uuid_generate_v4(),
    company_id uuid             not null,

    foreign key (company_id) references public.companies (id)
);

CREATE INDEX "USER__COMPANY_ID_IDX" on users (company_id);

CREATE TABLE public.stuff
(
    id         uuid primary key         not null default uuid_generate_v4(),
    created_at timestamp with time zone not null default now(),
    user_id    uuid                     not null,

    -- other ~20 fields that are not used in the query

    foreign key (user_id) references public.users (id)
);

CREATE INDEX "STUFF__USER_ID_IDX" on stuff (user_id);
CREATE INDEX "STUFF__CREATED_AT_IDX" on stuff (created_at DESC);

业务查询与性能问题 #

业务需要展示指定公司的stuff历史数据，要求按created_at倒序排列，支持offset，基础查询语句：

select s.id
from stuff s
         inner join users u on s.user_id = u.id
where u.company_id = 'SOME UUID HERE'
order by s.created_at desc
limit 10;

该查询在高活跃用户所属公司场景下瞬间完成，但在低活跃用户所属公司场景下耗时可达2分钟。

执行计划分析 #

带analyze的执行计划如下：

QUERY PLAN
Limit  (cost=1000.88..4303.03 rows=10 width=24) (actual time=6206.540..9353.007 rows=10 loops=1)
  ->  Gather Merge  (cost=1000.88..5902270.64 rows=17871 width=24) (actual time=6206.539..9353.000 rows=10 loops=1)
        Workers Planned: 2
        Workers Launched: 0
        ->  Nested Loop  (cost=0.86..5899207.86 rows=7446 width=24) (actual time=6205.840..9352.223 rows=10 loops=1)
              ->  Parallel Index Scan using "STUFF__CREATED_AT_IDX" on stuff s  (cost=0.43..795599.34 rows=4568817 width=40) (actual time=0.026..2942.201 rows=3708817 loops=1)
              ->  Index Scan using "PK_users" on users u  (cost=0.42..1.12 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3708817)
                    Index Cond: (id = s.user_id)
                    Filter: (company_id = 'SOME UUID HERE'::uuid)
                    Rows Removed by Filter: 1
Planning Time: 0.336 ms
Execution Time: 9353.056 ms

我对执行计划的理解是：PostgreSQL遍历stuff表的每一行，关联users表检查company_id是否匹配，不符合则跳过，直到找到limit指定的10条数据。若理解有误，请指正。

测试观察结果 #

• 移除limit后，低活跃公司查询极快，但高活跃公司查询极慢
• 移除order by排序后查询速度极快，但不符合业务需求
• 直接按user_id过滤stuff表的查询性能极佳，执行计划如下：

QUERY PLAN
Limit  (cost=97.22..97.24 rows=10 width=24) (actual time=0.019..0.020 rows=0 loops=1)
  ->  Sort  (cost=97.22..97.43 rows=84 width=24) (actual time=0.019..0.019 rows=0 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using "STUFF__USER_ID_IDX" on stuff s  (cost=0.43..95.40 rows=84 width=24) (actual time=0.015..0.015 rows=0 loops=1)
              Index Cond: (user_id = 'SOME USER UUID'::uuid)
Planning Time: 0.087 ms
Execution Time: 0.040 ms

优化疑问 #

我猜测在stuff表中新增company_id列可改善性能，但不确定。希望得到查询优化方向或表结构调整的具体建议。

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