From: Mark Bronnimann <meb@speakeasy.net>
To: Rod Taylor <rbt@rbt.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: function returning setof performance question
Date: Tue, 29 Jul 2003 22:57:27 -0400
Message-ID: <20030730025727.GA2094@spiff.astoria.bogus> (raw)
In-Reply-To: <1059532098.54325.23.camel@jester>
References: <20030730020859.GA1795@spiff.astoria.bogus>
<1059532098.54325.23.camel@jester>
Thanks for the reply.
I was hoping to eliminate the parse call on the view because I was doing
the where clause on the view instead of putting the where in the view.
In all, I was hoping to keep a single view called from multiple functions
with different where clauses. Yep... I shoulda known better...
Thanks again!
And Rod Taylor (rbt@rbt.ca) said...:
> > The performance hit is tiny, we're talking less than 1/2 a second,
> > but when I've done this sort of thing in Oracle I've seen a performance
> > increase, not a decrease.
>
> Thats just plain strange (never tried on Oracle). Why in the world
> would adding the overhead of a function call (with no other changes)
> increase performance?
>
> The function has additional overhead in the form of the plpgsql
> interpreter. You may find a c function will give close to identical
> performance as with the standard view so long as the query is the same.
>
>
> One thing to keep in mind is that the view can be rearranged to give a
> better query overall. The exact work completed for the view may be
> different when called from within a different SQL statement. Most
> functions -- some SQL language based functions are strange this way --
> cannot do this
>
--
Mark Bronnimann
meb@speakeasy.net
-- Let's organize this thing and take all the fun out of it. --
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-sql@postgresql.org
Cc: meb@speakeasy.net, rbt@rbt.ca
Subject: Re: function returning setof performance question
In-Reply-To: <20030730025727.GA2094@spiff.astoria.bogus>
* 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