Hi hackers!
I would like to revive this thread. At ServiceNow we recurringly encounter queries that are much slower than they would have to be, because of frequent calls to uncached stable functions with constant arguments (mostly to_date()). We've seen e.g. queries that get more than 8x faster by temporarily changing to_date() from stable to immutable.
I would be glad to help bringing this effort forward. Was there more work on the patch left than rebasing on latest master?
@Marina: do you have any plans to continue with this?
For reference here are all existing mailing list discussions I could find on this topic:
- [WIP] Caching constant stable expressions per execution (Marti, 2011),
https://www.postgresql.org/message-id/flat/CABRT9RC-1wGxZC_Z5mwkdk70fgY2DRX3sLXzdP4voBKuKPZDow%40mail.gmail.com- Caching for stable expressions with constant arguments v6 (Marti, 2012),
https://www.postgresql.org/message-id/flat/CABRT9RA-RomVS-yzQ2wUtZ=m-eV61LcbrL1P1J3jydPStTfc6Q@mail.gmail.com- WIP Patch: Precalculate stable functions (Marina, 2017),
https://www.postgresql.org/message-id/flat/ba261b9fc25dea4069d8ba9a8fcadf35@postgrespro.ru- WIP Patch: Precalculate stable functions, infrastructure v1 (Marina, 2017),
https://www.postgresql.org/message-id/flat/da87bb6a014e029176a04f6e50033cfb%40postgrespro.ru--
David Geier
(ServiceNow)
On 2018-11-29 18:00:15 +0100, Dmitry Dolgov wrote:
> > On Tue, Oct 2, 2018 at 4:22 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > On Thu, May 24, 2018 at 04:00:33PM +0300, Marina Polyakova wrote:
> > > Here there's a 9-th version of the patches for the precalculation of stable
> > > or immutable functions, stable or immutable operators and other nonvolatile
> > > expressions. This is a try to execute cached expressions as PARAM_EXEC,
> > > thanks to the comments of Tom Lane and Andres Freund [1].
> >
> > Please note that v9-0004 fails to apply, so a rebase is needed. This
> > patch is moved to next CF, waiting on author.
>
> Unfortunately, patch still has some conflicts, could you please post an updated
> version?
As nothing has happened since, I'm marking this as returned with
feedback.
Greetings,
Andres Freund