Re: Optimizing nested loops in the query plan

On Mon, 11 Feb 2002 12:25:25 -0500
Fran Fabrizio <ffabrizio@mmrd.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