当我添加LIMIT子句时，简单查询变得非常缓慢

Question

我有一个表格（包含snmp陷阱，但这个并不重要）。

我有一个查询语句可以检索一些记录，大概是这样的：

SELECT * 
FROM traps_trap 
WHERE summary_id = 1600
ORDER BY traps_trap."trapTime";

这个操作会立即响应，返回6条记录。

当我添加LIMIT 50（因为不是所有的结果都只有6条记录），它非常、非常慢（甚至无法返回）。

在summary_id列上有一个索引，我只能假设它没有被用于第二个查询。

我知道解决这个问题的工具是explain，但我对它的结果不够熟悉，无法理解。

第一个（快速）查询的详细分析如下：

                                                                   QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=14491.51..14502.48 rows=4387 width=263) (actual time=0.128..0.130 rows=6 loops=1)
   Output: id, summary_id, "trapTime", packet
   Sort Key: traps_trap."trapTime"
   Sort Method: quicksort  Memory: 28kB
   ->  Index Scan using traps_trap_summary_id on public.traps_trap  (cost=0.00..13683.62 rows=4387 width=263) (actual time=0.060..0.108 rows=6 loops=1)
         Output: id, summary_id, "trapTime", packet
         Index Cond: (traps_trap.summary_id = 1600)
 Total runtime: 0.205 ms
(8 rows)

explain for the second is:

                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..2538.69 rows=10 width=263)
   ->  Index Scan using "traps_trap_trapTime" on traps_trap  (cost=0.00..1113975.68 rows=4388 width=263)
         Filter: (summary_id = 1600)
(3 rows)

我每天运行VACUUM和ANALYZE，我知道这应该会改善计划。还有其他的建议吗？

- AMADANON Inc.

---

尝试在列summary_id + trapTime上创建多列索引（summary_id必须是索引中的第一列）。 - krokodilko

Answer 1

使用trapTime进行索引扫描比使用summary_id慢得多。我会尝试嵌套查询（以使用计划#1）：

select * from (
    SELECT * 
    FROM traps_trap 
    WHERE summary_id = 1600
    ORDER BY traps_trap."trapTime"
) t
limit 50;

---

编辑：

经过一些测试，我发现简单的查询嵌套（如上所示）对计划程序没有影响。 为了强制计划程序使用traps_trap_summary_id索引，您可以使用CTE（我的测试证实了这种方法）：

with t as (
    SELECT * 
    FROM traps_trap 
    WHERE summary_id = 1600
    ORDER BY traps_trap."trapTime"
)
select * from t
limit 50;

- klin