From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: pl/pgsql functions outperforming sql ones?
Date: Thu, 26 Jan 2012 19:09:13 -0500
Message-ID: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA> (raw)
Assuming there was some sort of cost to pl/pgsql, I rewrote a bunch of
stored functions s in straight SQL. Each stored proc was calling the next,
so to get the full effect I had to track down all the pl/pgsql stored
functions and convert them to sql. However, I was surprised to find after
all of the rewrites, the LANGUAGE sql procs caused the queries to run slower
than the LANGUAGE plpgsql.
None of the stored functions selected from tables, the operated on and
returned scalar values - it was all assign variables, if/then/else - not
even any looping.
For those who need the dirty details, here they are. If you happen to think
this behavior is expected, I needn't bore you - just let me know!
Thanks,
Carlo
This was all triggered during the optimization of a query like this:
SELECT myVar
FROM myTable
WHERE myFunc(myVar);
Looking at EXPLAIN ANALYSE I saw something like this:
Filter: myFunc(myVar)
I rewrote the body of myFunc(myVar) something like this:
SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar) END
When I reran EXPLAIN ANALYZE I got this:
Filter: SELECT CASE WHEN myVar IS NULL THEN false ELSE myOtherFunc(myVar)
END
Nice. So, I did the same treatment to myOtherFunc() (converted to straight
sql) but the EXPLAIN ANALYZE didn't change (reasonable, I guess - how deep
would I expect it to go?)
All of the procs were IMMUTABLE.
I was very surprised to find that the query now ran much slower by a factor
of 4.
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: stonec.register@sympatico.ca
Subject: Re: pl/pgsql functions outperforming sql ones?
In-Reply-To: <CE4615C16F2F41CAB0E67D4099BF20D2@CAPRICA>
* 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