最近，我被要求研究一个案例，在同一个数据库上以两种不同的方式执行相同的 SQL 会导致两个完全不同的延迟：133 毫秒与 750 毫秒。
一种是客户可以接受的，另一种是不能接受的。
同一个 SQL 怎么会产生两个这样不同的延迟呢？首先让我解释一下设置：第一个延迟数字，即快速数字，是一个普通的 SQL。第二个是完全相同的 SQL，但是它在 PLpgSQL 过程中，并且使用了引用。两者都返回了大约 60.000 行。两种情况下的 SQL 语句都没有任何变量，它们完全相同。
对于普通的 SQL，很容易诊断执行计划和花费的时间：只需将解释分析放在 SQL 前面，执行它，然后查看统计信息。但是，对于在 PLpgSQL 过程中执行的 SQL，无法从 PLpgSQL 过程内部获取执行计划。处理这种情况的一般方法是使用普通 SQL 方法，即从过程中复制 SQL 并对其进行解释。这就是问题所在：一旦作为普通 SQL 执行，它就会暴露出可接受的延迟，只有当作为 PLpgSQL 执行时，它才会暴露出不同的、不可接受的延迟。
所以你能对它做点啥？在我发现问题之前，我花了一些时间来解决它。
我们为此创建了一个问题，一位同事发现，如果从语句中删除 order by，它会再次正确运行。
我采用了一种非常适合我的方法，并运行 linux perf 以使用调用图 (perf record -g -p
执行计划的许多主要部分都可以在 C 函数中看到。一种方法是查看函数 ExecScan 的性能输出（使用性能报告），并查看该函数调用了什么。这是执行计划：
Sort (cost=14603.24..14764.65 rows=64562 width=20)
Sort Key: tile_row, tile_col, block_row, block_col
-> Bitmap Heap Scan on block_tbl b (cost=2139.33..9445.26 rows=64562 width=20)
Recheck Cond: ((world_key = 19301) AND (perimeter_num = 8007) AND (updated_ts = '1655419238118'::bigint))
Filter: ((updated_user_key = 0) AND (frozen_flag = 0))
-> Bitmap Index Scan on block_perimeter (cost=0.00..2123.18 rows=65108 width=0)
Index Cond: ((world_key = 19301) AND (perimeter_num = 8007) AND (updated_ts = '1655419238118'::bigint))
如果我分配 ExecScan，并查看它周围的调用，像这样：
- 27.44% ExecSort
- 12.93% tuplesort_gettupleslot
- 9.15% tuplesort_gettuple_common
- 4.42% mergereadnext
- 2.84% readtup_heap
1.58% __mcount_internal
- 0.95% getlen
0.63% __mcount_internal
0.63% __mcount_internal
- 3.47% tuplesort_heap_replace_top
- 2.84% comparetup_heap
1.58% __mcount_internal
1.26% __mcount_internal
2.52% __mcount_internal
0.95% _mcount
- 6.31% ExecScan
- 5.36% BitmapHeapNext
- 3.15% MultiExecBitmapIndexScan
index_getbitmap
- btgetbitmap
- 2.21% _bt_next
_bt_steppage
_bt_readnextpage
- _bt_readpage
- _bt_checkkeys
0.63% FunctionCall2Coll
- 0.95% tbm_add_tuples
0.63% tbm_get_pageentry
1.26% tbm_iterate
0.63% heap_hot_search_buffer
- 0.63% ExecInterpExpr
slot_getsomeattrs
slot_deform_tuple
在那里我们得到了排序第一（ExecSort）和扫描第二（ExecScan），它执行一个称为MultiExecBitmapIndexScan的函数。 我可以看到功能和执行计划的相似之处。
好的，现在程序的性能报告怎么样？ 像这样：
- 13.55% ExecScan
- 10.57% IndexNext
- 9.76% index_getnext
- 9.49% index_fetch_heap
- 3.25% ReadBufferExtended
- ReadBuffer_common
- 1.08% BufTableLookup
0.54% __mcount_internal
0.54% hash_search_with_hash_value
0.81% PinBuffer
0.54% LWLockAcquire
1.90% heap_hot_search_buffer
1.63% heap_page_prune_opt
0.81% __mcount_internal
- 0.81% ReleaseAndReadBuffer
__mcount_internal
0.54% _mcount
0.54% LWLockAcquire
- 0.81% ExecStoreTuple
0.54% __mcount_internal
- 1.63% ExecInterpExpr
0.54% slot_getsomeattrs
这看起来不一样。它正在执行 IndexNext 而不是 BitmapHeapNext，因此它肯定使用不同的执行计划。另外，我找不到 ExecSort 函数。所以 SQL 执行没有执行排序。
在这一点上，我可以通过查看低级别执行来证明还有另一个执行计划。在这一点上，我的怀疑是计划器可能会针对 PLpgSQL 进行优化，并将一些工作（例如排序）留给 PLpgSQL，这可能就是它消失的原因，并且选择了另一个计划。但是 PLpgSQL 不是 postgres 代码的核心组件，它是一种可加载的过程语言，您可以通过查看扩展来验证它。
接下来我尝试将 SQL 创建为函数，然后将该函数用作过程中的表。函数以能够成为规划器优化 SQL 的“障碍”而闻名，并被视为“黑匣子”，这意味着它不会受到影响。这确实奏效了！通过创建一个返回表的函数，我接近了 SQL 性能。
但是：这意味着将过程中的所有 SQL 恢复为单个函数，并将 SQL 与该函数交换的大量工作。当然，它并没有解释为什么该过程确实会影响 SQL 优化。
然后客户回到我身边说如果他使用 refcursor 来获取一个或少量的行，它实际上非常快…但这对他没有用，因为它的使用方式，它需要所有 (60.000) 行。
这让我开始思考：等一下……如果不是程序影响这一点，而是创建的光标怎么办？并且可以解释一个游标！解释游标的方式如下：
postgres=# begin;
BEGIN
postgres=# explain declare tt cursor for select b.tile_row, b.tile_col, b.block_row, b.block_col, b.block_value
postgres-# from block_tbl b
postgres-# where b.world_key = 19301
postgres-# and b.updated_ts = 1655419238118
postgres-# and b.updated_user_key = 0
postgres-# and b.frozen_flag = 0
postgres-# and b.perimeter_num = 8007
postgres-# order by b.tile_row, b.tile_col, b.block_row, b.block_col;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Index Scan using block_tile on block_tbl b (cost=0.42..40080.63 rows=64562 width=20)
Index Cond: ((world_key = 19301) AND (frozen_flag = 0))
Filter: ((updated_ts = '1655419238118'::bigint) AND (updated_user_key = 0) AND (perimeter_num = 8007))
答对了！这显示了与之前使用排序和位图扫描不同的执行计划。
好的，这证明了另一个计划是基于用于游标的 SQL 捕获的。但是为什么会这样呢？
事实证明，基于游标的 SQL 根据参数 cursor_tuple_fraction 自动优化了第一行的百分比，默认为 0.1，别名 10%，而常规 SQL 执行针对获取 100% 进行了优化。
这在 parsenodes.h 和 planner.c 的源代码中可见。使用游标，或使用 SPI_prepare_cursor 函数。可以设置 CURSOR_OPT_FAST_PLAN 标志，然后执行第一行优化。可悲的是，不同的优化目标没有显示出来，所以从执行计划中看不到。
概括
postgres 计划程序优化以获得 100% 的行执行。这对于游标是不同的，游标针对获取前 10% 的行进行了优化，如果您不希望 10% 的行尽可能快，但 100% 的行尽可能快，这可能会导致不同的执行计划和不同的延迟尽可能快。
原文标题：Postgres and the curious case of the cursory cursors
原文作者：Frits Hoogland
原文地址：https://dev.to/yugabyte/postgres-and-the-curious-case-of-the-cursory-cursors-3l3h
