Re: Efficiency of stored procedure vs large join

On Fri, Nov 08, 2002 at 17:56:33 +0000,
Malcolm Hutty <msah-postgres(at)hutty(dot)com> wrote:
>
> On the one hand (with pure SQL), I'm sending a much larger length of
> query text across the network from PHP to the database, and I might be
> constructing a very large joined resultset before it gets trimmed down
> (I don't know how the optimisation works). On the other hand (with a
> stored procedure) I'm performing several queries, taking the
> resultsets out of the Postgres optimiser and iteritively querying each
> result from PL/pgSQL. Is that a bad thing? Or is it no worse than the
> joined subselects in the big query? Does it make a performance
> difference at all?

What about using views? There you could use the sql method, but not
have to pass the extra sql around. This also gives you another option.
If the application authenticates to the database as the end user,
you can enforce the security in the database instead of in the
application (by giving normal users only access to the views).