postgresql - Postgres 不使用索引

Question

我在 linux7 上使用 postgres 9.5。这是环境:

create table t1(c1 int primary key, c2 varchar(100));

在刚刚创建的表中插入一些行

do $$
begin
for i in 1..12000000 loop
insert into t1 values(i,to_char(i,'9999999'));
end loop;
end $$;

现在我想更新 c2 列，其中 c1=random 值(EXPLAIN 表明未使用索引)。

explain update t1 set c2=to_char(4,'9999999') where c1=cast(floor(random()*100000) as int);
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Update on t1  (cost=10000000000.00..10000000017.20 rows=1 width=10)
   ->  Seq Scan on t1  (cost=10000000000.00..10000000017.20 rows=1 width=10)
         Filter: (c1 = (floor((random() * '100000'::double precision)))::integer)
(3 rows)

现在，如果我用数字(任意数字)替换“cast(floor(random()*100000) as int)”，则使用索引:

explain update t1 set c2=to_char(4,'9999999') where c1=12345;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Update on t1  (cost=0.15..8.17 rows=1 width=10)
   ->  Index Scan using t1_pkey on t1  (cost=0.15..8.17 rows=1 width=10)
         Index Cond: (c1 = 12345)
(3 rows)

问题是:

1. 为什么在第一种情况下(使用 random() 时)postgres 不使用索引？
2. 如何强制 Postgres 使用索引？

Answer 1

这是因为 random() 是一个volatile 函数(参见 PostgreSQL CREATE FUNCTION )，这意味着它应该对每一行进行(重新)评估。

所以你实际上并不是每次都更新一个随机行(据我所知你想要的)而是随机行数(它自己的行数随机生成的数字恰好匹配它的 id)，它的参与概率将趋于 0。

使用随机生成数字的较低范围查看它:

test=# select * from t1 where c1=cast(floor(random()*10) as int);
 c1 | c2
----+----
(0 rows)

test=# select * from t1 where c1=cast(floor(random()*10) as int);
 c1 |    c2
----+----------
  3 |        3
(1 row)

test=# select * from t1 where c1=cast(floor(random()*10) as int);
 c1 |    c2
----+----------
  4 |        4
  9 |        9
(2 rows)

test=# select * from t1 where c1=cast(floor(random()*10) as int);
 c1 |    c2
----+----------
  5 |        5
  8 |        8
(2 rows)

如果您只想检索一个随机行，您首先需要生成一个单个随机 ID 以与行 ID 进行比较。

HINT: You can think that database planner is dumb and always perform sequential scan over all rows and calculates condition expressions one time per each row. Then, under the hood, database planner is much more smart and, if he know that every time he calculate it (in the same transaction) the result will be the same, then he calculate it once and perform an index scan.

一个棘手(但肮脏)的解决方案可能是创建您自己的 random_stable() 函数，声明它是稳定的，即使它返回一个随机生成的数字也是如此。

...这将使您的查询像现在一样简单。但我认为这是一个肮脏的解决方案，因为它伪装函数实际上是 volatile 的事实。

然后，一个更好的解决方案(对我来说是正确的)是以一种真正一次性生成数字的形式编写查询。

例如:

test=# with foo as (select floor(random()*1000000)::int as bar) select * from t1 join foo on (t1.c1 = foo.bar);
 c1  |    c2    | bar
-----+----------+-----
 929 |      929 | 929
(1 row)

...或类似提供@a_horse_with_no_name 的子查询解决方案

注意:为了简单性和可读性，我使用了select查询而不是update查询，但情况是一样的:只需使用相同的where 子句(使用子查询方法:当然，使用 which 会有点棘手......)。然后，要检查是否使用了索引，您只需要在前面加上您知道的“explain”即可。