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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] Im Auftrag von Philippe Lang Gesendet: Dienstag, 19. Januar 2010 11:19 An: A. Kretschmer; pgsql-general@xxxxxxxxxxxxxx Betreff: Re: Index on immutable function call pgsql-general-owner@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
