Re: Index on immutable function call - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Index on immutable function call
Date
Msg-id FE3E684C-40B5-4DD0-B0C4-4C8E17AACF43@solfertje.student.utwente.nl
Whole thread Raw
In response to Index on immutable function call  ("Philippe Lang" <philippe.lang@attiksystem.ch>)
List pgsql-general
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!



pgsql-general by date:

Previous
From: Dimitri Fontaine
Date:
Subject: Re: C: extending text search: from where to start
Next
From: Alban Hertroys
Date:
Subject: Re: Index on immutable function call