所以我有一个包含 1000 行的表,我只是说select * from hugedata limit 1
查询计划只是说table scan
然后limit operator
附加的是查询计划
[
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.00,
"Total Cost": 0.02,
"Plan Rows": 1,
"Plan Width": 41,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["pk", "description", "flags"],
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "hugedata",
"Schema": "public",
"Alias": "hugedata",
"Startup Cost": 0.00,
"Total Cost": 15406.01,
"Plan Rows": 1000001,
"Plan Width": 41,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.006,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["pk", "description", "flags"],
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
"Settings": {
},
"Planning": {
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 0.035,
"Triggers": [
],
"Execution Time": 0.069
}
]
问题是 postgres 中如何应用 limit 子句?
- DB Engine 选择 1000(所有)记录,然后将这些记录传递给 limit 运算符,它接受第一个记录并丢弃 999 条记录。
或者
- 数据库引擎使用接受它的限制运算符检索第一个记录检查,然后数据库引擎尝试使用拒绝它的限制运算符搜索第二个记录检查,并且数据库引擎停止进一步搜索...与第一个记录的区别是数据库引擎始终适用于(搜索/检索)N+1(限制数量)行,其中在第一个行中它总是获取表中的所有行。
从我的搜索中,它告诉我限制运算符按照第一点建议的方式工作,如果这是真的,为什么实现效率如此低下,或者我错过了什么?
限制文档说它在生成查询计划时考虑了限制,但是 N+1 行的产量或“考虑到”意味着未指定其他内容... https://www.postgresql.org/docs/current/查询限制.html