From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Karl Czajkowski <karlcz@isi.edu>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: optimizing immutable vs. stable function calls?
Date: Wed, 18 Jan 2017 18:23:26 -0500
Message-ID: <16158.1484781806@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, karlcz@isi.edu
Subject: Re: optimizing immutable vs. stable function calls?
In-Reply-To: <16158.1484781806@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox