In response to Philippe Lang : > Hi, > > I'm trying to figure out how to use an index on an immutable function > call in order to speed up queries. > 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". > > > Unfortunately, Postgreql does not use the index at all. > > What am I doing wrong? I use the default query tuning options of > Postgresql 8.3.7.
The planner doesn't know the actual parameter for the function, so he picked out the wrong plan. You can force the planner to re-planning with a dynamical statement within the function and EXECUTE that. http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN In short: build a string that contains your query and EXECUTE that string, within your function. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
