Thanks for the quick follow-up. So, you are saying that if I can do SPI in _PG_init, then I could prepare all my queries there and they would be prepared once for the entire function when it is loaded? That would certainly achieve what I want. Does anybody know whether I can do SPI in _PG_init?
The function gets called a lot, but not in the same transaction. It is only called once per transaction. On Wed, May 26, 2010 at 12:18 PM, Stephen Frost <sfr...@snowman.net> wrote: > * Eliot Gable > (egable+pgsql-performa...@gmail.com<egable%2bpgsql-performa...@gmail.com>) > 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 > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkv9Sd8ACgkQrzgMPqB3kihj/gCdEIA8DhnvZX4Hz3tof6yzLscS > Lf8An2Xp8R/KXnkmp8uWg+84Cz7Pp7R3 > =AX4g > -----END PGP SIGNATURE----- > > -- Eliot Gable "We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower "I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower "Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero