* Eliot Gable (egable+pgsql-performance@xxxxxxxxx) wrote: > Would a query such as this obtain any performance improvement by being > re-written using C? I wouldn't expect the queries called by the pl/pgsql function to be much faster if called through SPI from C instead. I think the question you need to answer is- how long does the pl/pgsql code take vs. the overall time the function takes as a whole? You could then consider that your 'max benefit' (or pretty close to it) which could be gained by rewriting it in C. > Are there specific cases where writing a function in C would be highly > desirable verses using PL/PGSQL (aside from simply gaining access to > functionality not present in PL/PGSQL)? Cases where a function is called over and over again, or there are loops which go through tons of data, or there's alot of data processing to be done. > Are there specific cases where writing a function in C would be slower than > writing the equivalent in PL/PGSQL? Probably not- provided the C code is written correctly. You can certainly screw that up (eg: not preparing a query in C and having PG replan it every time would probably chew up any advantage C has over pl/pgsql, in a simple function). > Basically, I am looking for some guidelines based primarily on performance > of when I should use C to write a function verses using PL/PGSQL. Realize that C functions have alot of other issues associated with them- typically they're much larger foot-guns, for one, for another, C is an untrusted language because it can do all kinds of bad things. So you have to be a superuser to create them. > Can anybody quantify any of the performance differences between doing a > particular task in C verses doing the same thing in PL/PGSQL? For example, > performing a SELECT query or executing a certain number of lines of control > logic (primarily IF/THEN, but an occasional loop included)? How about > assignments or basic math like > addition/subtraction/multiplication/division? Actually performing a SELECT through SPI vs. calling it from pl/pgsql probably won't result in that much difference, presuming most of the time there is in the actual query itself. Assignments, basic math, control logic, etc, will all be faster in C. You need to figure out if that work is taking enough time to justify the switch though. > When executing SQL queries inside a C-based function, is there any way to > have all of the SQL queries pre-planned through the compilation process, > definition of the function, and loading of the .so file similar to PL/PGSQL? You might be able to do that when the module is loaded, but I'm not 100% sure.. Depends on if you can start using SPI in _PG_init.. I think there was some discussion about that recently but I'm not sure what the answer was. > Would I get better performance writing each SQL query as a stored procedure > and then call these stored procedures from within a C-based function which > does the logging, math, control logic, and builds the result sets and > cursors? Uhh, I'd guess 'no' to that one. Thanks, Stephen
Attachment:
signature.asc
Description: Digital signature
