Re: [HACKERS] Faster methods for getting SPI results (460%improvement)

On 1/5/17 9:50 PM, Jim Nasby wrote:
> The * on that is there's something odd going on where plpython starts
> out really fast at this, then gets 100% slower. I've reached out to some
> python folks about that. Even so, the overall results from a quick test
> on my laptop are (IMHO) impressive:
>
>             Old Code        New Code    Improvement
> Pure SQL    2 sec          2 sec
> plpython    12.7-14 sec    4-10 sec     ~1.3-3x
> plpython - SQL 10.7-12 sec 2-8 sec      ~1.3-6x
>
> Pure SQL is how long an equivalent query takes to run with just SQL.
> plpython - SQL is simply the raw python times minus the pure SQL time.

I finally got all the kinks worked out and did some testing with python 
3. Performance for my test [1] improved ~460% when returning a dict of 
lists (as opposed to the current list of dicts). Based on previous 
testing, I expect that using this method to return a list of dicts will 
be about 8% slower. The inconsistency in results on 2.7 has to do with 
how python 2 handles ints.

Someone who's familiar with pl/perl should take a look at this and see 
if it would apply there. I've attached the SPI portion of this patch.

I think the last step here is to figure out how to support switching 
between the current behavior and the "columnar" behavior of a dict of 
lists. I believe the best way to do that is to add two optional 
arguments to the execution functions: container=[] and members={}, and 
then copy those to produce the output objects. That means you can get 
the new behavior by doing something like:

plpy.execute('...', container={}, members=[])

Or, more interesting, you could do:

plpy.execute('...', container=Pandas.DataFrame, members=Pandas.Series)

since that's what a lot of people are going to want anyway.

In the future we could also add a GUC to change the default behavior.

Any concerns with that approach?

1:
> d = plpy.execute('SELECT s AS some_table_id, s AS some_field_name, s AS some_other_field_name FROM
generate_series(1,{})s'.format(iter) )
 
> return len(d['some_table_id'])
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers