大型数据库中排序已经排序的数据

Question

我正在使用一个postgres10数据库，其中包含数十亿行的物联网数据（我们称之为primary）。数据存储在一个结构中：SENSOR\u ID、TIMESTAMP、VALUE，在这个结构中我使用一个辅助表来获取关于传感器的元数据（让我们称这个表为secondary）

主表已按时间顺序（主键）存储。我访问python代码中的数据来模拟物联网环境（使用这些信息检查流、模型和ext）。数据按时间顺序从数据库中出来是很重要的，因此为了保证这一点，我们的查询使用orderbytime子句

因为我们的电脑无法获得数十亿的回报，所以我们正试图使用游标来获取小批量的结果

我的问题是，对于“小型”查询（比如100万），Postgres会识别出数据已经按排序顺序排列，只需快速扫描主索引（然后与元数据辅助表进行连接），而不会出现问题。如果我给它一个更大的查询，那么QEP包含一个非常昂贵的排序操作，尽管数据不需要排序

有没有一种方法可以让优化器识别出数据已经处于有序状态，并在使用游标时放弃排序

对于没有游标的较小查询，我们可以获得所需的性能和QEP，但对于较大的数据集来说，这很困难

QEP的两个例子和优化器的一些奇怪问题。一个使用没有限制的查询，另一个使用比行数更高的限制（或者足够简单）

无限制条款：

EXPLAIN SELECT timestampmilli, value, id, globalid FROM data11 TB1,
sensor_enrichment_11 TS WHERE TB1.id = TS.globalid AND
timestampmilli BETWEEN '2018-01-01'::timestamp AND '2018-06-06'::timestamp
ORDER BY timestampmilli;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=99448621.97..100079863.62 rows=252496661 width=28)
   Sort Key: tb1.timestampmilli
   ->  Hash Join  (cost=1014.59..46087034.51 rows=252496661 width=28)
         Hash Cond: (tb1.id = ts.globalid)
         ->  Seq Scan on data11 tb1  (cost=0.00..42298597.84 rows=252491094 width=24)
               Filter: ((timestampmilli >= '2018-01-01 00:00:00'::timestamp without time zone) AND (timestampmilli <= '2018-06-06 00:00:00'::timestamp without time zone))
         ->  Hash  (cost=699.26..699.26 rows=25226 width=4)
               ->  Seq Scan on sensor_enrichment_11 ts  (cost=0.00..699.26 rows=25226 width=4)

大限额条款：

EXPLAIN SELECT timestampmilli, value, id, globalid FROM data11 TB1,
sensor_enrichment_11 TS WHERE TB1.id = TS.globalid AND
timestampmilli BETWEEN '2018-01-01'::timestamp AND '2018-06-06'::timestamp 
ORDER BY timestampmilli LIMIT 10000000;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.99..82250110.69 rows=10000000 width=28)
   ->  Nested Loop  (cost=0.99..2076787807.52 rows=252496661 width=28)
         ->  Index Scan using data11_pkey on data11 tb1  (cost=0.58..994786742.16 rows=252491094 width=24)
               Index Cond: ((timestampmilli >= '2018-01-01 00:00:00'::timestamp without time zone) AND (timestampmilli <= '2018-06-06 00:00:00'::timestamp without time zone))
         ->  Index Only Scan using sensor_enrichment_11_pkey on sensor_enrichment_11 ts  (cost=0.41..4.28 rows=1 width=4)
               Index Cond: (globalid = tb1.id)