From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <craig@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
Date: Wed, 14 Aug 2013 15:05:19 -0400
Message-ID: <12240.1376507119@sss.pgh.pa.us> (raw)
In-Reply-To: <520B0B26.70204@2ndquadrant.com>
References: <520AD240.9060508@2ndquadrant.com>
<22812.1376453860@sss.pgh.pa.us>
<520B0B26.70204@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Craig Ringer <craig@2ndquadrant.com> writes:
> It's interesting that this variant doesn't seem to be slow:
> create or replace function to_datestamp_immutable(
> time_int double precision
> ) returns date as $$
> select date_trunc('day', timestamp 'epoch' + $1 * interval '1
> second')::date;
> $$ language sql immutable;
> and there's no sign it's parsed each time. So it's not just the
> IMMUTABLE flag.
If you're working with timestamp not timestamptz, I think the functions
being called here actually are immutable (they don't have any dependency
on the timezone parameter). So this function is safely inline-able
and there's no performance hit from multiple executions.
As Pavel mentioned upthread, the safest rule of thumb for SQL functions
that you want to get inlined is to not mark them as to either mutability
or strictness. That lets the planner inline them without any possible
change of semantics. (The basic point here is that a function marked
volatile can be expanded to its contained functions even if they're
immutable; but the other way around represents a potential semantic
change, so the planner won't do it.)
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, craig@2ndquadrant.com
Subject: Re: Interesting case of IMMUTABLE significantly hurting performance
In-Reply-To: <12240.1376507119@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