On Jan 21, 2008 2:47 AM, Alex Turner <armtuk@xxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
