title: postgres functions: when does IMMUTABLE hurt performance?
body: <p>The <a href="http://www.postgresql.org/docs/9.2/static/xfunc-volatility.html" rel="nofollow noreferrer">Postgres docs</a> say </p>
  
  <blockquote>
    <p>For best optimization results, you should label your functions with the strictest volatility category that is valid for them.</p>
  </blockquote>
  
  <p>However, I seem to have an example where this is not the case, and I'd like to understand what's going on.  (Background: I'm running postgres 9.2)</p>
  
  <p>I often need to convert times expressed as integer numbers of seconds to dates.  I've written a function to do this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION 
    to_datestamp(time_int double precision) RETURNS date AS $$
    SELECT date_trunc('day', to_timestamp($1))::date;
  $$ LANGUAGE SQL;
  </code></pre>
  
  <p>Let's compare performance to otherwise identical functions, with volatility set to IMMUTABLE and to STABLE:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION 
    to_datestamp_immutable(time_int double precision) RETURNS date AS $$
    SELECT date_trunc('day', to_timestamp($1))::date;
  $$ LANGUAGE SQL IMMUTABLE;
  </code></pre>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE OR REPLACE FUNCTION 
    to_datestamp_stable(time_int double precision) RETURNS date AS $$
    SELECT date_trunc('day', to_timestamp($1))::date;
  $$ LANGUAGE SQL STABLE;
  </code></pre>
  
  <p>To test this, I'll create a table of 10^6 random integers corresponding to times between 2010-01-01 and 2015-01-01</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TEMPORARY TABLE random_times AS
    SELECT 1262304000 + round(random() * 157766400) AS time_int 
    FROM generate_series(1, 1000000) x;
  </code></pre>
  
  <p>Finally, I'll time calling the two functions on this table; on my particular box, the original takes ~6 seconds, the immutable version takes ~33 seconds, and the stable version takes ~6 seconds.</p>
  
  <pre class="lang-sql prettyprint-override"><code>EXPLAIN ANALYZE SELECT to_datestamp(time_int) FROM random_times;
  
  Seq Scan on random_times  (cost=0.00..20996.62 rows=946950 width=8) 
    (actual time=0.150..5493.722 rows=1000000 loops=1)
  Total runtime: 6258.827 ms
  
  
  EXPLAIN ANALYZE SELECT to_datestamp_immutable(time_int) FROM random_times;
  
  Seq Scan on random_times  (cost=0.00..250632.00 rows=946950 width=8) 
    (actual time=0.211..32209.964 rows=1000000 loops=1)
  Total runtime: 33060.918 ms
  
  
  EXPLAIN ANALYZE SELECT to_datestamp_stable(time_int) FROM random_times;
  Seq Scan on random_times  (cost=0.00..20996.62 rows=946950 width=8)
    (actual time=0.086..5295.608 rows=1000000 loops=1)
  Total runtime: 6063.498 ms
  </code></pre>
  
  <p>What's going on here?  E.g., is postgres spending time caching results when that won't actually be helpful since the arguments passed to the function are unlikely to repeat?</p>
  
  <p>(I'm running postgres 9.2.)</p>
  
  <p>Thanks!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks to <a href="https://stackoverflow.com/users/398670/craig-ringer">Craig Ringer</a> this has been discussed on the <a href="http://www.postgresql.org/message-id/520AD240.9060508@2ndquadrant.com" rel="nofollow noreferrer">pgsql-performance mailing list</a>.  Highlights:</p>
  
  <p><a href="http://www.postgresql.org/message-id/22812.1376453860@sss.pgh.pa.us" rel="nofollow noreferrer">Tom Lane says</a> </p>
  
  <blockquote>
    <p>[ shrug... ]  Using IMMUTABLE to lie about the mutability of a function
    (in this case, date_trunc) is a bad idea.  It's likely to lead to wrong
    answers, never mind performance issues.  In this particular case, I
    imagine the performance problem comes from having suppressed the option
    to inline the function body ... but you should be more worried about
    whether you aren't getting flat-out bogus answers in other cases.</p>
  </blockquote>
  
  <p><a href="http://www.postgresql.org/message-id/CAFj8pRBF3Qr7WtQwO1H_WN=hhFGk0semwhdE+ODz3iyv-TroMQ@mail.gmail.com" rel="nofollow noreferrer">Pavel Stehule says</a></p>
  
  <blockquote>
    <p>If I understand, a used IMMUTABLE flag disables inlining. What you see, is
    SQL eval overflow.  My rule is - don't use flags in SQL functions, when it is possible.</p>
  </blockquote>