Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id A85981F07123 for ; Thu, 26 Jan 2012 20:09:31 -0400 (AST) Received: from toroondcbmts08-srv.bellnexxia.net ([207.236.237.42]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RqZNg-0004Qi-4W for pgsql-performance@postgresql.org; Fri, 27 Jan 2012 00:09:30 +0000 Received: from toip59-bus.srvr.bell.ca ([67.69.240.186]) by toroondcbmts08-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20120127000914.DTTA10851.toroondcbmts08-srv.bellnexxia.net@toip59-bus.srvr.bell.ca> for ; Thu, 26 Jan 2012 19:09:14 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Av4EAELjIU9GMVPj/2dsb2JhbABCgk2sA4EFgXkIfgUGEBQ+GiUBBBsDoAKfaok+MhECDoJnSQNRBWGCOWMEiD+FBwGNUYx2 Received: from unknown (HELO CAPRICA) ([70.49.83.227]) by toip59-bus.srvr.bell.ca with ESMTP; 26 Jan 2012 19:09:14 -0500 From: "Carlo Stonebanks" To: Subject: pl/pgsql functions outperforming sql ones? Date: Thu, 26 Jan 2012 19:09:13 -0500 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0AEB_01CCDC5D.FE101D80" X-Mailer: Microsoft Office Outlook 11 Thread-Index: Aczch7aKwtMGJ1uqQTmh/tUUxVbzuw== X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609 X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/78 X-Sequence-Number: 46090 This is a multi-part message in MIME format. ------=_NextPart_000_0AEB_01CCDC5D.FE101D80 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. ------=_NextPart_000_0AEB_01CCDC5D.FE101D80 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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.

 

 

------=_NextPart_000_0AEB_01CCDC5D.FE101D80--