Re: Index on immutable function call

On 19 Jan 2010, at 7:40, Philippe Lang wrote:

> 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.

Did you analyse the table?
Can you show us an explain analyse?

What I notice off-hand is that you don't appear to have an index on data1, so Postgres doesn't know for which rows that is > some_immutable_function(data2).

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b5579a310607798915529!