I am using postgres 9.5 on linux7. Here is the environment:
create table t1(c1 int primary key, c2 varchar(100));
insert some rows in just created table
do $$
begin
for i in 1..12000000 loop
insert into t1 values(i,to_char(i,'9999999'));
end loop;
end $$;
Now I want to update c2 column where c1=random value (EXPLAIN show that index is not used).
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)
Now, if I replace "cast(floor(random()*100000) as int)" with a number (any number) index is used:
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)
Questions are:
- Why in first case (when random() is used) postgres doesn't use index?
- How can I force Postgres to use index?
Aucun commentaire:
Enregistrer un commentaire