PostgreSQL分组获取订阅用户最新50条帖子的查询优化

现有用户、帖子（blog_posts）、订阅（subscriptions）三张表，用户可订阅其他用户并查看其最新帖子。原查询语句如下：

SELECT "blog_posts".* 
FROM "blog_posts" 
WHERE (user_id IN (SELECT subscribed_to_id FROM subscriptions WHERE subscriber_id = ?)) 
ORDER BY "blog_posts"."created_at" LIMIT 50

该查询用于获取当前用户订阅对象的所有帖子，再按created_at排序取前50条。但随着帖子数量增长（测试库含10000名用户，每人4000条帖子），当用户订阅多名其他用户时，查询计划会加载所有订阅用户的帖子后进行内存排序，性能随数据量增大持续下降。

尝试添加包含created_at的索引后未被PostgreSQL使用，改用CROSS JOIN LATERAL查询：

SELECT b.*
FROM   subscriptions s
CROSS JOIN LATERAL (
   SELECT *
   FROM blog_posts b
   WHERE  s.subscriber_id = 100 
   AND b.user_id = s.subscribed_to_id
   ORDER  BY created_at DESC LIMIT 50
   ) b
ORDER BY created_at DESC LIMIT 50

虽减少了帖子表的读取行数，速度有所提升，但会全表扫描subscriptions表，查询成本极高。

现寻求纯SQL优化方案，在保证获取最新50条帖子的同时，控制读取行数。

Posts表 #

CREATE TABLE blog_posts (
    id bigint DEFAULT nextval('blog_posts_id_seq'::regclass) PRIMARY KEY,
    message character varying(280) NOT NULL,
    user_id bigint NOT NULL REFERENCES users(id),
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);

-- 索引 -------------------------------------------------------

CREATE UNIQUE INDEX blog_posts_pkey ON blog_posts(id int8_ops);
CREATE INDEX index_posts_on_user_id ON blog_posts(user_id int8_ops);
CREATE INDEX idx_posts_user_created ON blog_posts(user_id int8_ops,created_at timestamp_ops DESC);

（注：原代码中重复了created_at timestamp_ops DESC);，已修正）

Users表 #

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email text NOT NULL,
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);

-- 索引 -------------------------------------------------------

CREATE UNIQUE INDEX users_pkey ON users(id int8_ops);

Subscriptions表 #

CREATE TABLE subscriptions (
    id BIGSERIAL PRIMARY KEY,
    subscriber_id bigint REFERENCES users(id),
    subscribed_to_id bigint REFERENCES users(id),
    created_at timestamp(6) without time zone NOT NULL,
    updated_at timestamp(6) without time zone NOT NULL
);

-- 索引 -------------------------------------------------------

CREATE UNIQUE INDEX subscriptions_pkey ON subscriptions(id int8_ops);
CREATE INDEX index_subscriptions_on_subscribed_to_id ON subscriptions(subscribed_to_id int8_ops);
CREATE UNIQUE INDEX index_subscriptions_on_subscriber_id_and_subscribed_to_id ON subscriptions(subscriber_id int8_ops,subscribed_to_id int8_ops);
CREATE INDEX index_subscriptions_on_subscriber_id ON subscriptions(subscriber_id int8_ops);

Limit  (cost=74084.78..74084.90 rows=50 width=57) (actual time=119.681..119.696 rows=50 loops=1)
  Buffers: shared hit=16 read=28029 written=3
  ->  Sort  (cost=74084.78..74134.70 rows=19971 width=57) (actual time=119.679..119.687 rows=50 loops=1)
          Sort Key: blog_posts.created_at
          Sort Method: top-N heapsort Memory: 37kB
        Buffers: shared hit=16 read=28029 written=3
        ->  Nested Loop  (cost=51.68..73421.35 rows=19971 width=57) (actual time=1.389..112.820 rows=28000 loops=1)
              Buffers: shared hit=16 read=28029 written=3
              ->  Index Only Scan using index_subscriptions_on_subscriber_id_and_subscribed_to_id on subscriptions subscriptions  (cost=0.29..4.38 rows=5 width=8) (actual time=0.535..0.549 rows=7 loops=1)
                      Index Cond: (subscriptions.subscriber_id = 9999)
                    Buffers: shared hit=1 read=2

              ->  Bitmap Heap Scan on blog_posts blog_posts  (cost=51.39..14643.46 rows=3994 width=57) (actual time=1.295..15.097 rows=4000 loops=7)
                      Recheck Cond: (blog_posts.user_id = subscriptions.subscribed_to_id)
                      Heap Blocks: exact=28000
                    Buffers: shared hit=15 read=28027 written=3
                    ->  Bitmap Index Scan on index_posts_on_user_id  (cost=0.00..50.39 rows=3994 width=0) (actual time=0.693..0.693 rows=4000 loops=7)
                            Index Cond: (blog_posts.user_id = subscriptions.subscribed_to_id)
                          Buffers: shared hit=7 read=35

Planning:
  Buffers: shared hit=24 read=14
Execution time: 119.728 ms

Limit  (cost=10163257.73..10163257.85 rows=50 width=57) (actual time=31.224..31.238 rows=50 loops=1)
  Buffers: shared hit=741
  ->  Sort  (cost=10163257.73..10169483.10 rows=2490150 width=57) (actual time=31.222..31.230 rows=50 loops=1)
          Sort Key: b.created_at DESC
          Sort Method: top-N heapsort Memory: 35kB
        Buffers: shared hit=741
        ->  Nested Loop  (cost=0.56..10080536.74 rows=2490150 width=57) (actual time=0.296..31.139 rows=300 loops=1)
              Buffers: shared hit=741
              ->  Seq Scan on subscriptions s  (cost=0.00..914.03 rows=49803 width=16) (actual time=0.007..4.748 rows=49803 loops=1)
                    Buffers: shared hit=416
              ->  Limit  (cost=0.56..201.39 rows=50 width=57) (actual time=0.000..0.000 rows=0 loops=49803)
                    Buffers: shared hit=325
                    ->  Result  (cost=0.56..16042.46 rows=3994 width=57) (actual time=0.000..0.000 rows=0 loops=49803)
                          Buffers: shared hit=325
                          ->  Index Scan using idx_posts_user_created on blog_posts b  (cost=0.56..16042.46 rows=3994 width=57) (actual time=0.007..0.041 rows=50 loops=6)
                                  Index Cond: (b.user_id = s.subscribed_to_id)
                                Buffers: shared hit=325
Execution time: 31.267 ms

方案1：修正LATERAL查询，避免全表扫描订阅表 #

原LATERAL查询未过滤当前订阅者，导致全表扫描subscriptions。调整后先通过索引快速获取当前用户的订阅列表，再关联子查询：

SELECT b.*
FROM   (SELECT subscribed_to_id FROM subscriptions WHERE subscriber_id = ?) s
CROSS JOIN LATERAL (
   SELECT *
   FROM blog_posts b
   WHERE  b.user_id = s.subscribed_to_id
   ORDER  BY created_at DESC LIMIT 50
) b
ORDER BY b.created_at DESC LIMIT 50;

该版本利用订阅表索引快速定位目标订阅对象，针对每个对象取最新50条帖子，最后全局排序取前50，兼顾性能与准确性。

方案2：窗口函数+复合索引优化（订阅用户较多场景） #

若订阅用户数量大，LATERAL查询开销较高，可结合窗口函数与现有复合索引：

WITH ranked_posts AS (
    SELECT 
        bp.*,
        ROW_NUMBER() OVER (PARTITION BY bp.user_id ORDER BY bp.created_at DESC) AS rn
    FROM blog_posts bp
    JOIN subscriptions s ON bp.user_id = s.subscribed_to_id
    WHERE s.subscriber_id = ?
)
SELECT * FROM ranked_posts 
WHERE rn <= 50
ORDER BY created_at DESC LIMIT 50;

利用idx_posts_user_created索引快速按用户分组排序，窗口函数限制每个用户仅取最新50条，最后全局排序取结果，减少无效数据读取。

方案3：强制索引引导查询计划 #

若PostgreSQL未自动选择最优索引，可尝试强制使用复合索引或改写JOIN语句：

-- 强制使用复合索引
SELECT "blog_posts".* 
FROM "blog_posts" 
WHERE user_id IN (SELECT subscribed_to_id FROM subscriptions WHERE subscriber_id = ?) 
ORDER BY "blog_posts"."created_at" DESC LIMIT 50
INDEX idx_posts_user_created;

或改写为JOIN形式引导优化器：

SELECT bp.*
FROM subscriptions s
JOIN blog_posts bp ON s.subscribed_to_id = bp.user_id
WHERE s.subscriber_id = ?
ORDER BY bp.created_at DESC LIMIT 50;

这种写法更易让优化器选择按created_at排序的索引，降低排序开销。

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