Re: Function index qeustion

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jonathan Bartlett <johnnyb(at)eskimo(dot)com>
Cc: Elielson Fontanezi <ElielsonF(at)prodam(dot)sp(dot)gov(dot)br>, "'Stephan Szabo'" <sszabo(at)megazone(dot)bigpanda(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org>, pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Function index qeustion
Date: 2003-07-25 23:41:53
Message-ID: 9914.1059176513@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

Jonathan Bartlett <johnnyb(at)eskimo(dot)com> writes:
> 1) If you have an index on a cacheable function, does PostgreSQL use the
> index instead of calculating the results?

Not in general --- only for an indexscan lookup.

> 2) How does PostgreSQL know when to recompute the function?

Never. That's what the iscachable flag means: you are promising that
the function's output for given input never changes. If you aren't
prepared to make that promise, you cannot index the function.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2003-07-25 23:55:06 Re: Using YY-MM-DD date input
Previous Message Maksim Likharev 2003-07-25 23:26:20 Re: Wacky query plan, why?

Browse pgsql-sql by date

  From Date Subject
Next Message Denis Zaitsev 2003-07-26 13:39:47 Very strange 'now' behaviour in nested triggers.
Previous Message Jamie Lawrence 2003-07-25 22:08:10 Re: Odd problems with create rule