PostgreSQL小LIMIT值查询性能异常问题排查与解决

在一个相对简单的查询中使用小LIMIT子句值时，查询速度异常缓慢。

我遇到了这类典型问题：当LIMIT值较小时，查询耗时约7409626纳秒，而LIMIT值较大时仅需53纳秒。只要把LIMIT从1改为1000，查询速度就变得极快；改成10或1时，速度又会异常缓慢。

我试过一些常见建议，比如添加额外的无用ORDER BY列来引导查询优化器，甚至把不带LIMIT的主查询放进WITH子句（CTE）里，但优化器生成的执行计划依然很慢。

查询语句 #

select id 
from rounds 
where userid = (
  select id
  from users
  where integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2') 
order by created desc 
limit 1;

执行计划分析结果 #

LIMIT = 1时的原生查询 #

explain analyze select id from rounds where userid = (select id from users where integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2') order by created desc, userid limit 1;
                 QUERY PLAN
------------------------------------------------
 Limit  (cost=3.07..47.03 rows=1 width=40) (actual time=7408.097..7408.099 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_integrationuserid_idx on users  (cost=0.41..2.63 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=1)
           Index Cond: (integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2'::text)
   ->  Index Scan using recent_rounds_idx on rounds  (cost=0.44..938182.73 rows=21339 width=40) (actual time=7408.096..7408.096 rows=1 loops=1)
         Filter: (userid = $0)
         Rows Removed by Filter: 23123821
 Planning Time: 0.133 ms
 Execution Time: 7408.114 ms
(9 rows)

对比LIMIT = 1000时的情况（取值随机，仅用于测试） #

explain analyze select id from rounds where userid = (select id from users where integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2') order by created desc, userid limit 1000;
                    QUERY PLAN
------------------------------------------------
 Limit  (cost=24163.47..24165.97 rows=1000 width=40) (actual time=0.048..0.049 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using users_integrationuserid_idx on users  (cost=0.41..2.63 rows=1 width=16) (actual time=0.018..0.019 rows=1 loops=1)
           Index Cond: (integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2'::text)
   ->  Sort  (cost=24160.84..24214.18 rows=21339 width=40) (actual time=0.047..0.048 rows=1 loops=1)
         Sort Key: rounds.created DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on rounds  (cost=226.44..22990.84 rows=21339 width=40) (actual time=0.043..0.043 rows=1 loops=1)
               Recheck Cond: (userid = $0)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on rounds_userid_idx  (cost=0.00..221.10 rows=21339 width=0) (actual time=0.040..0.040 rows=1 loops=1)
                     Index Cond: (userid = $0)
 Planning Time: 0.108 ms
 Execution Time: 0.068 ms
(14 rows)

核心问题 #

• 为什么会出现这种极端情况？为什么扫描整个数据库的所有行比先过滤出子集再扫描更快？
• 如何解决这个问题？

我期望查询优化器先把原表过滤成符合WHERE条件的行，再对这些行排序和LIMIT，但实际它会先对全表（约2300万条数据）执行排序和LIMIT操作，导致速度异常缓慢。我试了几十种写法，想先通过子查询提取用户对应的rounds数据再应用LIMIT，但优化器总能识别这种写法，还是会对全表执行LIMIT。

额外尝试/信息 #

有资料提到，针对这类问题的旧解决方案在PostgreSQL 13及以上版本不再生效，建议用CTE，但我尝试后都失败了：

CTE尝试1（速度极慢） #

with r as (
  select id, created 
  from rounds 
  where userid = (
    select id
    from users 
    where integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2')
) 
select r.id from r order by r.created desc limit 1;

CTE尝试2（调整ORDER BY和LIMIT位置，依然没用） #

with r as (
   select id, created from rounds 
   where userid = (select id from users where integrationuserid = 'sample:64ce5bad-8c48-44a4-b473-5a7451980bb2') 
   order by created desc
)
select r.id from r limit 1;

解决方案 #

create index recent_rounds_idx on rounds (userid, created desc);

内容的提问来源于Stack Exchange，提问作者Mordachai