Re: Index on immutable function call

pgsql-general-owner@postgresql.org wrote:
> In response to Philippe Lang :
>>
>> 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".
>>
>
> Around 50% of your data comply with your where-condition, i think,
> this is the reason why the planner decides for a seq-scan.

I tried to insert data differently, with a strong dissymetry:

-- insert data into table
insert into indexed_table
  select
    i,
    cast((select random() * 10 * i) as integer),
    cast((select random() * 2000 * i) as integer)
  from generate_series(1, 100000) as i;

OR

-- insert data into table
insert into indexed_table
  select
    i,
    cast((select random() * 2000 * i) as integer),
    cast((select random() * 10 * i) as integer)
  from generate_series(1, 100000) as i;


I use 3 indexes, just in case:



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