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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Philippe Lang
Gesendet: Dienstag, 19. Januar 2010 11:19
An: A. Kretschmer; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] 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






--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general