Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql functions outperforming sql ones?

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: pl/pgsql functions outperforming sql ones?
Date: 2012-02-08 20:33:08
Message-ID: CA+TgmobFfyivRvFDrRFR7dfK=8hwgic_+BWrv4idiMBGDQpyXA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Jan 28, 2012 at 11:20 PM, Carlo Stonebanks
<stonec(dot)register(at)sympatico(dot)ca> wrote:
> Update: The main stored function in question and all of its sub
> sub-functions were recoded to new pure sql functions.
>
> I then stub tested the sub functions sql vs. plpgsql.
>
> Here were the results for new sql vs old plpgsql:
>
> Individual sub functions tested 20-30% faster
>
> But the main function calling new sql sub functions ran 100% slower
>
> So I tried this:
>
> I modified the old plpgsql function to call the new sql sub functions.
>
> THAT ran 20-30% faster then the unmodified version.
>
> That modified function is listed below. All the functions ending in 2 are
> the new SQL versions.

One advantage of PL/pgsql for code like this is that you can compute
values once and save them in variables.  SQL doesn't have variables,
so you can end up repeating the same SQL in multiple places (causing
multiple evaluation), or even if you manage to avoid that, the system
can inline things in multiple places and produce the same effect.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2012-02-08 23:27:00
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
Previous:From: Ofer IsraeliDate: 2012-02-08 19:59:52
Subject: Re: Vacuuming problems on TOAST table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group