|
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. |
