0

I state that my answer to the object question is Yes in my case is convinient but I ask here to the expert.

I developed a lot of plpgsql functions and just one in C but I already understood that the learning curve is definitely more sloped.

In may case I need a real developing language that plpgsql sometimes is not, but also I need performance otherwise I'd looked at python.

But here the question. Mainly I need to retrieve data with some select and join, make elaboration on them, sametimes complex and return a table of data.

From the time of execution point of view is quicker a c function for this kind of use?

I apreciate any comment

luca

Improve this question
2
  • You should stop thinking in terms of CPU. Database queries (and performance) are totally dominated by the cost of (disk) I/O and the need for memory to buffer it. CPU (and languages wasting CPU) are relatively unimportant. Commented Nov 20, 2013 at 19:12
  • @wildplasser I/O isn't a bottleneck if the hot data is in the cache. And complex data manipulation can easily take more time than I/O, depending on how complex it is. Commented Dec 13, 2013 at 7:24

2 Answers 2

Reset to default
2

But here the question. Mainly I need to retrieve data with some select and join, make elaboration on them, sametimes complex and return a table of data.

I would go with pl/pgsql for this, as that's what it is designed for. In general, pl/pgsql performs very well within its problem domain, and I doubt you are likely to get significantly better performance by going with C. To the extent you can push your elaborations into the main query, all the better performance-wise.

This is assuming that your elaborations can be done with existing functions and not a huge amount of complex data manipulation (in particular, say, converting between datatypes, like arrays and sets). If that is the case, I would still put the main query and light manipulation in the pl/pgsql, and put the specific operations that need to be tuned in C. There are two reasons for doing this:

  1. It means less C code, which means the C code is easier to read, follow, and prove correct.
  2. It separates concerns so that you can use similar manipulations elsewhere.

There's a lot of performance tuning that has gone into pl/pgsql for its problem domain and reinventing all of that would be a lot of work both in development and testing. To the extent you can leverage tools that are already there you can get the performance you need with a lot less effort and a lot more in the way of guarantees.

EDIT

If you want to write PL/PGSQL code that performs well, you want to have it be a large main query with modest support logic. The more you can push into your query the better, and the more of your elaborations you can do in SQL (with possible C functions as mentioned above), the better. Not only does this mean better performance but it means better maintainability. As ArtemGr mentioned, certain operations are very expensive in PL/PGSQL. and in these cases you want to supplement with C code in order to get the performance you need.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

I know C/C++ well and for me it's easier to write a PostgreSQL function in C++ than to learn the intricacies of pgSQL syntax and workaround its limitations. I'd say go with the language you (and the rest of your team) are more familiar with. C should be faster than pgSQL (and Tcl, Perl, Python) for complex data manipulation. Usually 5-10 times faster. Javascript (http://code.google.com/p/plv8js/) might be nearly as fast as C if it has a chance to spin it's JIT. Python code can actually use a Cython extension under the hood which might be nearly as fast as C.

You should probably measure how much time is spent in the data manipulation in question and relative to the time spent in the I/O before making a decision. In some domains C isn't faster, for example Tcl and Javascript has very good regular expression engines.

Improve this answer

1 Comment

Just as a note, writing maintainable, well-performing PL/PGSQL is very different from writing maintainable, well-performing C code. Also there are often places where pushing certain sorts of logic into C can be helpful (for example, say, slicing arrays). So you have an important point about some of the elaborations being better done in C if you can't do them in SQL in a maintainable and performant way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.