From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Karl Czajkowski <karlcz@isi.edu>,
        "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: optimizing immutable vs. stable function calls?
In-reply-to: 
 <CAKFQuwY-tztAzdaBNZR_uwiQnj1Ovbtd6-6uHQ57k8rXPrR+Nw@mail.gmail.com>
References: <20170118223633.GC23081@moraine.isi.edu>
 <14964.1484780049@sss.pgh.pa.us>
 <CAKFQuwY-tztAzdaBNZR_uwiQnj1Ovbtd6-6uHQ57k8rXPrR+Nw@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Wed, 18 Jan 2017 16:06:54 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <16157.1484781806.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 18 Jan 2017 18:23:26 -0500
Message-ID: <16158.1484781806@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ​I'm feeling a bit dense here but even after having read a number of these
> kinds of interchanges I still can't get it to stick.  I think part of the
> problem is this sentence from the docs:
> https://www.postgresql.org/docs/current/static/xfunc-volatility.html

> (Stable): "​This category allows the optimizer to optimize multiple calls
> of the function to a single call"

> I read that sentence (and the surrounding paragraph) and wonder why then
> doesn't it do so in this case.

It says "allows", it doesn't say "requires".

The reason we have this category is that without it, it would be formally
invalid to optimize an expression involving a non-immutable function into
an index comparison value, because in that context the function is indeed
only evaluated once (before the comparison value is fed into the index
machinery).  But there isn't a mechanism for that behavior outside of
index scans.

> If PostgreSQL cannot execute it only once at query start then all this talk
> about optimization seems misleading.  At worse there should be an sentence
> explaining when the optimizations noted in that paragraph cannot occur -
> and probably examples of both as well since its not clear when it can occur.

If you want an exact definition of when things will happen or not happen,
start reading the source code.  I'm loath to document small optimizer
details since they change all the time.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance