cursor_tuple_fraction

• 19
• 18
• 17
• 16
• 15
• 14
• 13
• 12
• 11
• 10
• 9.6
• 9.5
• 9.4
• 9.3
• 9.2
• 9.1
• 9.0
• 8.4

cursor_tuple_fraction 的用法示例 - 首先，设置一个包含 10,000,000 行（已索引）随机数的简单表，并禁用自动清理

当 cursor_tuple_fraction 设置为 1（最大值）并且 random_page_cost 设置为一个非常高的值时，会选择顺序扫描

postgres=# BEGIN;
BEGIN

postgres=*# SET random_page_cost TO 1000;
SET

postgres=*# SET cursor_tuple_fraction TO 1;
SET

postgres=*# EXPLAIN
  DECLARE cur CURSOR FOR
  SELECT * FROM cursor_test ORDER BY val;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Sort  (cost=40459807.82..40484763.90 rows=9982431 width=8)
   Sort Key: val
   ->  Seq Scan on cursor_test  (cost=0.00..188320.31 rows=9982431 width=8)
(3 rows)

但将其降低到 0.1（默认值）会导致索引扫描

postgres=*# SET cursor_tuple_fraction TO 0.1;
SET

postgres=*# EXPLAIN
  DECLARE cur CURSOR FOR
  SELECT * FROM cursor_test ORDER BY val;
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Only Scan using val_ix on cursor_test  (cost=0.43..54989736.90 rows=9982431 width=8)
(1 row)

通过实际获取 10 行所需的时间，分别将 cursor_tuple_fraction 设置为 1 和 0.1，可以演示这种差异

postgres=# BEGIN ;
BEGIN
Time: 0.261 ms

postgres=*# SET cursor_tuple_fraction TO 1;
SET
Time: 0.305 ms

postgres=*# DECLARE cur CURSOR FOR
  SELECT * FROM cursor_test ORDER BY val;
DECLARE CURSOR
Time: 0.324 ms

postgres=*# FETCH 10 FROM cur;
          val           
------------------------
  4.086947802406371e-08
 2.2672169275317344e-07
  3.811600721714825e-07
  4.729380020940255e-07
  5.001395353421145e-07
  5.545030219433755e-07
  5.911655163171048e-07
  6.835541628280595e-07
  7.550144764945088e-07
  7.852209042980007e-07
(10 rows)

Time: 3699.970 ms (00:03.700)

postgres=*# SET cursor_tuple_fraction TO 0.1;
SET
Time: 0.295 ms

postgres=*# FETCH 10 FROM cur;
          val           
------------------------
 1.0436382211764794e-06
 1.0606182454520052e-06
 1.3870003436888823e-06
 1.4260912035712892e-06
 1.5682459066912458e-06
 1.7877217302064707e-06
 1.8104408385788417e-06
 1.9592999442874515e-06
  1.974146760508333e-06
  2.328882489743833e-06
(10 rows)

Time: 0.311 ms

• PostgreSQL 文档： cursor_tuple_fraction

• 在 PostgreSQL 中声明游标或如何减少内存消耗 - Hans-Jürgen Schönig / CyberTec 于 2019 年 4 月的文章