happy_marmoset's questions

我正在尝试计算旧记录。在这种情况下，即使我设置了一些限制，为什么 Postgres 会给出 1160 的结果LIMIT 1？

SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1;

 count
--------
1160
(1 row)

我期望结果为 1 或 0，但结果为 1160。为什么？

给定表格：

和索引：

• "items_pkey" 主键，btree (id)
• “items_object_id_idx” btree (object_id)

当我执行时：

SELECT *
FROM items
WHERE object_id = 123
LIMIT 1;

它很快返回 0 行。但是，当我使用 执行此查询时ORDER BY，它会挂起很长时间：

SELECT *
FROM items
WHERE object_id = 123
ORDER BY id DESC  -- I added the ORDER BY
LIMIT 1;

是什么解释了这种差异？

查询计划

快速查询（无ORDER BY）

                                                                          QUERY PLAN                                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..3.34 rows=1 width=63) (actual time=0.014..0.014 rows=0 loops=1)
   ->  Index Scan using items_object_id_operation_idx on items  (cost=0.56..2579.16 rows=929 width=63) (actual time=0.013..0.013 rows=0 loops=1)
         Index Cond: (object_id = 123::bigint)
 Total runtime: 0.029 ms

慢查询（带ORDER BY）

                                                                            QUERY PLAN                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.44..1269.14 rows=1 width=63) (actual time=873796.061..873796.061 rows=0 loops=1)
   ->  Index Scan Backward using items_pkey on items  (cost=0.44..1164670.11 rows=918 width=63) (actual time=873796.059..873796.059 rows=0 loops=1)
         Filter: (object_id = 123::bigint)
         Rows Removed by Filter: 27942522
 Total runtime: 873796.113 ms