Re: Speeding up query pulling comments from pg_catalog

On Sat, Jul 20, 2019 at 12:25 PM Tom Lane <[email protected]> wrote:

> Ken Tanzer <[email protected]> writes:
> > On Sat, Jul 20, 2019 at 7:46 AM Tom Lane <[email protected]> wrote:
> >> and then to add insult to injury, has to search pg_description a second
> >> time for each hit.
>
> > Not sure if I'm understanding this correctly, but are you saying that
> > because col_description() is specified in two places in the query, that
> it
> > actually will get called twice?
>
> Yes.
>
> > I was under the impression that a function
> > (at least a non-volatile one) specified multiple times, but with the same
> > arguments, would only get called once. Is that just wishful thinking?
>
> Afraid so.


That's good to know!  Just to help me understand:



> There's been assorted talk about various optimizations to
> avoid unnecessary duplicate function calls,


So I had read the sentence below to mean my functions would only get called
once.  But is that sentence only supposed to apply to index scans?  Or does
it mean the planner is allowed to optimize, but it just doesn't know how
yet?

A STABLE function cannot modify the database and is guaranteed to return
the same results given the same arguments for all rows within a single
statement. *This category allows the optimizer to optimize multiple calls
of the function to a single call.* In particular, it is safe to use an
expression containing such a function in an index scan condition. (Since an
index scan will evaluate the comparison value only once, not once at each
row, it is not valid to use a VOLATILE function in an index scan condition.)
(https://www.postgresql.org/docs/9.6/xfunc-volatility.html)

Cheers,
Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
[email protected]
(253) 245-3801

Subscribe to the mailing list
<[email protected]?body=subscribe> to
learn more about AGENCY or
follow the discussion.