Hi,
I'm trying to figure out how to use an index on an immutable function
call in order to speed up queries.
I came up with this small test:
----------------------------------------
--create database foo;
--drop table indexed_table;
create table indexed_table (
id serial primary key,
data1 integer,
data2 integer
);
create or replace function this_is_a_long_transformation(d integer)
returns integer as $$
declare
l integer;
begin
-- wait
l = 0;
while l < 100 loop
l = l + 1;
end loop;
-- return same value
return d;
end
$$
language plpgsql immutable;
-- insert data into table
insert into indexed_table
select
i,
cast((select random() * 1000 * i) as integer),
cast((select random() * 1000 * i) as integer)
from generate_series(1, 100000) as i;
-- create index
create index long_transformation_index on indexed_table
(this_is_a_long_transformation(data2));
--select * from indexed_table WHERE data1 > data2;
select * from indexed_table WHERE data1 >
this_is_a_long_transformation(data2);
----------------------------------------
My goal is to make query...
select * from indexed_table WHERE data1 >
this_is_a_long_transformation(data2);
... as fast as
select * from indexed_table WHERE data1 > data2;
... with the help of the index "long_transformation_index".
Unfortunately, Postgreql does not use the index at all.
What am I doing wrong? I use the default query tuning options of
Postgresql 8.3.7.
Best regards,
-----------------------------------------------------------------------
Philippe Lang Web : www.attiksystem.ch
Attik System Email : philippe.lang@attiksystem.ch
rte de la Fonderie 2 Phone : +41 26 422 13 75
1700 Fribourg Mobile : +41 79 351 49 94
Switzerland Fax : +41 26 422 13 76
