cursor_tuple_fraction

• 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)

可以使用实际需要的时间来演示差异，分别使用cursor_tuple_fraction设置为1和0.1来提取10行

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中声明游标或如何减少内存消耗 - 2019年4月Hans-Jürgen Schönig / CyberTec的博客文章