Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Mark Bronnimann <meb@speakeasy.net>, pgsql-sql@postgresql.org
Subject: Re: [SQL] function returning setof performance question
Date: Wed, 30 Jul 2003 10:28:50 -0700
User-Agent: KMail/1.4.3
References: <20030730020859.GA1795@spiff.astoria.bogus>
In-Reply-To: <20030730020859.GA1795@spiff.astoria.bogus>
Cc: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307301028.50759.josh@agliodbs.com>

Mark,

I'm crossing this over to the performance list; it's really appropriate on=
=20
both lists.  So I'm quoting you in full as well.

>   I have a question regarding the performance of a function returning a=
=20
> set of a view as opposed to just selecting the view with the same=20
> where clause. Please, if this should go to the performance list instead,=
=20
> let me know. I'm just wondering about this from the sql end of things.=20
>=20
>   Here's the environment:
>=20
>   I'm working from PHP, calling on the query.=20
>=20
>   I have a view that joins 12 tables and orders the results.=20
>=20
>   From PHP, I do a select on that view with a where clause.=20
>=20
>   I created a function that queries the view with the where clause=20
> included in the function. The function is returning a setof that=20
> view taking one variable for the where clause (there are several=20
> other static wheres in there).
>=20
>   I have found that querying the view with the where clause is=20
> giving me quicker results than if I call the function.=20
>=20
>   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
>=20
>   Any ideas?=20

Actually, this is exactly what I'd expect in your situation.   The SRF retu=
rns=20
the records in a very inefficient fashion: by materializing the result set=
=20
and looping through it to return it to the calling cursor, whereas the View=
=20
does set-based operations to grab blocks of data.  Also PL/pgSQL as a=20
language is not nearly as optimized as Oracle's PL/SQL.

It's also possible that PostgreSQL handles criteria-filtered views better t=
han=20
Oracle does.   I wouldn't be surprised.

The only times I can imagine an SRF being faster than a view with a where=
=20
clause are:

1) When you're only returning a small part of a complex result set, e.g. 10=
=20
rows out of 32,718.
2) When the view is too complex (e.g. UNION with subselects) for the Postgr=
es=20
planner to "push down" the WHERE criteria into the view execution.

I've been planning on testing the performance of SRFs vs. views myself for=
=20
paginated result sets in a web application, but haven't gotten around to it=
=20
since I can't get my www clients to upgrade to 7.3 ...

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco