Re: pl/php for windows - Mailing list pgsql-php

On Sat, 2009-03-07 at 18:51 +0100, Andreas Wenk wrote:
>
> Hi Andrew,
>
> woooh .... sounds like giving the advice to reduce the usage of user
> defined functions to a minimum. But in my opinion it should be a good
> idea to move functionality to the database in case you work with a
> Language like PHP. As I recognized it is a good idea to do so 'cause the
> database is often faster than PHP.
>
> I am speaking of PL/pgsql and SQL udf's.
>
> Did I misunterstand you? Are there other experiences? In which cases is
> the planner forced to have problems with statements containing udf's?

From what I have seen, sometimes the planner can have no idea about the
costs of a function.  This is essentially an unsolvable problem -
consider the case where for certain values of input a complex select
may/may not be performed.  Consequently the planner doesn't try very
hard, and assigns a default performance cost for the function call which
may be wildly wrong.

But there can definitely be performance improvements from functions in
the database, where communication may be faster.  I'm not blindly in
favour of either approach, though I have had horrible experiences having
to maintain an Oracle application where *everything* was in database
functions, so serving most pages came down to something like:

 SELECT some_function(input1,input2) FROM dual;

There was a lot of developer overhead in writing in that way, because
Oracle developers are (very) expensive, development is generally slower
than in PHP or Perl or modern scripting languages.  Also, in that case,
there was little value in the meagre performance increases.

My experience strongly suggests that PHP programmers are many orders of
magnitude easier to find than PL/PgSQL programmers, so I try not to
write much more in PL/PgSQL than is necessary for performance (or
simplicity) reasons, and I try and keep my functions small (not always
successfully :-).

That said, I think more people *should* understand PL/PgSQL, and I have
a very successful 1-2 hour talk I can give at the drop of a hat which
introduces people to many interesting features of PL/PgSQL by going over
the solution to a particular problem which only an idiot would want to
solve in the language.  And it turns out that my code to do that in
PL/PgSQL is around 400 lines, compared with around 700 lines for some
equivalent code I have written in PHP.

So by no means am I saying "don't use in-database functions".  I'm
saying think carefully about it, considering:

 * development costs
 * maintenance costs
 * performance gains / losses
 * additional per connection memory for the interpreter

That last one doesn't apply so much for PL/SQL and probably not
particularly for PL/PgSQL which has a low memory footprint, as far as I
can see.

But yeah, I guess I am advocating that development and maintenance costs
are significantly greater for in-database functions, so don't use them
everywhere. They can also have unanticipated effects on performance, so
even when you do decide to use them make sure you check your assumptions
about performance improvement before spending too much time and effort
on it.

Cheers,
                    Andrew McMillan.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
            Executive ability is prominent in your make-up.
------------------------------------------------------------------------