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.
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99


Sorry...


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:

create index long_transformation1_index on indexed_table (data1);
create index long_transformation2_index on indexed_table
(this_is_a_long_transformation(data2));
create index long_transformation3_index on indexed_table (data1,
this_is_a_long_transformation(data2));

But in every case, I end up with a sequential scan. The partial index is
for the moment the only working solution...

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