Subject: Re: function returning setof performance question
From: Rod Taylor <rbt@rbt.ca>
To: Mark Bronnimann <meb@speakeasy.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20030730020859.GA1795@spiff.astoria.bogus>
References: <20030730020859.GA1795@spiff.astoria.bogus>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-wXIudNigNvDGi5HPPEaG"
Message-Id: <1059532098.54325.23.camel@jester>
Mime-Version: 1.0
Date: 29 Jul 2003 22:28:19 -0400

--=-wXIudNigNvDGi5HPPEaG
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> The performance hit is tiny, we're talking less than 1/2 a second,=20
> but when I've done this sort of thing in Oracle I've seen a performance=
=20
> increase, not a decrease.=20

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


--=-wXIudNigNvDGi5HPPEaG
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Jy1C6DETLow6vwwRAjEJAJ446f+fjBCyFkVZtobtqqMx3Dai+ACfVjbo
9tdR9XTAfLtilquPPr9tok0=
=nfdL
-----END PGP SIGNATURE-----

--=-wXIudNigNvDGi5HPPEaG--