Re: Performance PLV8 vs PLPGSQL

2016-12-29 10:04 GMT+01:00 Tim Uckun <timuckun(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 2016-12-29 9:23 GMT+01:00 Tim Uckun <timuckun(at)gmail(dot)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 <msofen(at)runbox(dot)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)
>>>>
>>>
>>>
>>
>