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! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
