Thread: Index not used without explicit typecast

Hi,

When I have a large table, the index is not used if the type I use in the comparison does not match exactly. The result is the same, but it takes longer.

Below is an example that illustrates this.

Is it possible to fix this?

Regards,

Jan

-- Create 4M records

drop table if exists table1;

create table table1 (

    id integer NOT NULL,

    CONSTRAINT table1p PRIMARY KEY (id),

    date1 timestamp without time zone);

insert into table1 (id) values (1);

insert into table1 (id) select id + 1 from table1;

insert into table1 (id) select id + 2 from table1;

insert into table1 (id) select id + 4 from table1;

insert into table1 (id) select id + 8 from table1;

insert into table1 (id) select id + 16 from table1;

insert into table1 (id) select id + 32 from table1;

insert into table1 (id) select id + 64 from table1;

insert into table1 (id) select id + 128 from table1;

insert into table1 (id) select id + 256 from table1;

insert into table1 (id) select id + 512 from table1;

insert into table1 (id) select id + 1024 from table1;

insert into table1 (id) select id + 2048 from table1;

insert into table1 (id) select id + 4096 from table1;

insert into table1 (id) select id + 8192 from table1;

insert into table1 (id) select id + 16384 from table1;

insert into table1 (id) select id + 32768 from table1;

insert into table1 (id) select id + 65536 from table1;

insert into table1 (id) select id + 65536 * 2 from table1;

insert into table1 (id) select id + 65536 * 4 from table1;

insert into table1 (id) select id + 65536 * 8 from table1;

insert into table1 (id) select id + 65536 * 16 from table1;

insert into table1 (id) select id + 65536 * 32 from table1;

vacuum analyze table1;

select count(*) from table1

UPDATE TABLE1 SET date1 = current_timestamp WHERE ID = 1000000;

-- 45ms

UPDATE TABLE1 SET date1 = current_timestamp WHERE ID = 1000000::numeric;

-- 490ms