2016-12-29 10:04 GMT+01:00 Tim Uckun <timuc...@gmail.com>: > Mostly generating SQL statements to execute. Like for example deciding > which partition to insert into. >
Then you don't find any possible performance difference - the query is about 10-100x slower than expression - so the plpgsql should be good. More you can use a "format" function - implemented in C. Regards Pavel > > On Thu, Dec 29, 2016 at 10:00 PM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> >> >> 2016-12-29 9:23 GMT+01:00 Tim Uckun <timuc...@gmail.com>: >> >>> I am not doubting the efficacy of stored procs, just wondering which >>> language is better. From the sound of it string manupilation is slow in >>> PL-PGSQL but looking at my procs there does seem to be a lot of string >>> manipulation going on so maybe I better do some tests. >>> >> >> It is interesting, what string operations you are doing in stored >> procedures? >> >> Regards >> >> Pavel >> >> >>> >>> >>> On Thu, Dec 29, 2016 at 3:02 AM, Mike Sofen <mso...@runbox.com> wrote: >>> >>>> *From:* Tim Uckun >>>> I have seen various links on the internet which indicate that PLV8 is >>>> significantly faster than PL-PGSQL sometimes an order of magnitude faster. >>>> >>>> >>>> >>>> Is there any benefit to choosing PL-PGSQL? >>>> >>>> ------------------------ >>>> >>>> I can’t speak to PLV8. However, I can speak to plpgsql, and >>>> specifically stored functions (procs). I use it exclusively to create a >>>> database API for real-time web applications to hit. My API calls (procs) >>>> are hitting large tables, sometimes doing complex logic within the sproc. >>>> It allows me to provide a simple, standardized interface to the web devs, >>>> allowing them to focus on the app code work. >>>> >>>> >>>> >>>> Performance is superb and continues to surprise me (I came from the SQL >>>> Server world). As others have mentioned, the natural lashup of plpgsql to >>>> postgres (I liked Alban’s term, “impedance”), is a key aspect. Also: >>>> >>>> >>>> >>>> - stored procs provide another security layer against sql >>>> injection attacks. >>>> >>>> - Caching SEEMS to be more efficient/effective with stored >>>> procs (that could be wishful thinking too). >>>> >>>> - Stored procs allow skilled sql practitioners to provide far >>>> more sophisticated sql solutions than the typical python developer is >>>> capable of…my experience is that most web devs don’t really understand >>>> databases (or even care about them – they are a necessary evil), so >>>> providing a pure encapsulated sql solution (via stored procs) removes that >>>> mental impedance mismatch. >>>> >>>> - Performance? Simple “get” procs that return data for a >>>> specific indexed query against larger tables (50m+ rows) in a few >>>> milliseconds…I can live with that kind of performance. >>>> >>>> - I’m also doing some heavy lifting in the sql, calculating >>>> histograms and boxplots for data visualizations. This is an unusual >>>> scenario, but the other option is sending a massive chunk of data to >>>> another server for processing – just the transit time would kill the deal. >>>> I am mindful that at a certain point, there won’t be enough memory and i/o >>>> to go around, but the web app is a low user count/high user task complexity >>>> app, so I’ve tailored the model to match. >>>> >>>> >>>> >>>> Mike Sofen (Synthetic Genomics) >>>> >>> >>> >> >
