jeudi 1 septembre 2016

Postgres doesn't use index

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:

  1. Why in first case (when random() is used) postgres doesn't use index?
  2. How can I force Postgres to use index?



Aucun commentaire:

Enregistrer un commentaire