Re: Index on immutable function call

Hello Philippe,

if you always select data1 > this_is_a_long_transformation(data2) you could
use the following index:

create index long_transformation_index_2 on indexed_table ( ( data1 >
this_is_a_long_transformation(data2) ) );

Index Scan using long_transformation_index_2 on indexed_table
(cost=0.25..2450.96 rows=33333 width=12)
Index Cond: ((data1 > this_is_a_long_transformation(data2)) = true)
Filter: (data1 > this_is_a_long_transformation(data2))

Greetings
Timo Klecker

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Im Auftrag von Philippe Lang
Gesendet: Dienstag, 19. Januar 2010 11:19
An: A. Kretschmer; pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] Index on immutable function call

pgsql-general-owner(at)postgresql(dot)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(dot)lang(at)attiksystem(dot)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