Re: Optimizing nested loops in the query plan

On Mon, 11 Feb 2002 12:25:25 -0500
Fran Fabrizio <ffabrizio(at)mmrd(dot)com> wrote:

> Thank you, it was this step that really made the difference! It runs in
> 0.64 seconds now.
>
> Interestingly, adding with (iscachable) to the findregion() function
> made the query run twice as slow (even after first execution). Strange.

Twice -- I don't know why that case slows down so much.
But index functions seem to need iscachable explicitly and strictly,
which is commented in the ../src/backend/commands/indexcmds.c after 7.2.

/*
* Require that the function be marked cachable. Using a noncachable
* function for a functional index is highly questionable, since if
* you aren't going to get the same result for the same data every
* time, it's not clear what the index entries mean at all.
*/
if (!func_iscachable(funcid))
elog(ERROR, "DefineIndex: index function must be marked iscachable");

Regards,
Masaru Sugawara