On Jan 21, 2008 2:47 AM, Alex Turner <armtuk@gmail.com> wrote:
> I re-implemented in C and it was 8-12 times faster, and didn't error out on
> acos for the same values. Expecting DBAs to be able to write functions in C
acos error on distance function is rounding issue, easy fix if you had
to do it this way (although this code really belongs in C).
> IMHO is a bit unrealistic. I am far from a typical DBA, I've met precious
> few Oracle DBAs who could write functions in C. Trying to implement good
> database code that is atomic and makes good use of functions in Postgresql
> is an uphill battle because they slow the database down so much.
While doing things like calculating distance are better handled in
languages like C, it is relatively trivial to wrap C libraries with
SQL and call them from in side sql or pl/pgsql procedures. Most of
the database functions I write are simply chaining SQL statements
together for presentation to the application or more complex queries.
pl/pgsql is very good at this...unless you know _exactly_ what you
are doing, writing these kinds of functions in C, which requires use
of SPI, will not get you anything on the performance side (and could
actually hurt, if you don't go through all the effort of plan
management).
In other words, pl/pgsql's speed disadvantages only matter if a
significant portion of the work is in doing things other than
executing queries and handling the results. Certainly, these types of
problems come up, but such cases are simply out of the scope of the
intended purpose of the language and there are many techniques dealing
with these problems. On the other hand, pl/pgsql is extremely easy to
write and debug, especially when running static sql. IMO, it is an
absolute must for any PostgreSQL DBA to master the language as well as
be aware of its limitations.
merlin
