Re: Speeding up query pulling comments from pg_catalog - Mailing list pgsql-performance
On Sat, Jul 20, 2019 at 12:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> On Sat, Jul 20, 2019 at 7:46 AM Tom Lane <tgl@sss.pgh.pa.us> 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.)
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-performance by date: