Re: Index on immutable function call

pgsql-general-owner(at)postgresql(dot)org wrote:
> On Tue, Jan 19, 2010 at 07:40:00AM +0100, Philippe Lang wrote:
>> I'm trying to figure out how to use an index on an immutable function
>> call in order to speed up queries.
> [..]
>> Unfortunately, Postgreql does not use the index at all.
>
> Yup, an index isn't going to be very useful in what you're doing.
> It's arranged the "wrong" way around---you want something in the
> table itself to compare against. I'd just have another column in the
> table that stores the result of the function call (use a trigger to
> keep it up to date) and then create an index on this column if you
> want/need.

Hi,

I'm already doing "caching with triggers", it works great. The only
problem is that it costs an extra "machinery": a column and triggers.

I was wondering this morning if a proper index could replace this. It
looks like the answer is "yes", but I'm worried about stability: you are
never sure in advance how the planner will work exactly...

Best regards,

Philippe

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