Re: [HACKERS] Faster methods for getting SPI results

> On 12 Sep 2017, at 23:00, Tom Lane wrote: > > Chapman Flack writes: >> On 09/12/2017 03:41 PM, Tom Lane wrote: >>> So the conclusion at the end of the last commitfest was that this patch >>> should be marked Returned With Feedback, and no new work

Re: [HACKERS] Faster methods for getting SPI results

On 09/12/17 17:00, Tom Lane wrote: > I did not see any reason given in the thread why we should need that. > If you want to accumulate tuples ten at a time before you do something > with them, you can do that now, by calling ExecutorRun with count=10. Ah, that sounds easy enough. I'll withdraw

Re: [HACKERS] Faster methods for getting SPI results

Chapman Flack writes: > On 09/12/2017 03:41 PM, Tom Lane wrote: >> So the conclusion at the end of the last commitfest was that this patch >> should be marked Returned With Feedback, and no new work appears to have >> been done on it since then. Why is it in this fest at

Re: [HACKERS] Faster methods for getting SPI results

On 09/12/2017 03:41 PM, Tom Lane wrote: > So the conclusion at the end of the last commitfest was that this patch > should be marked Returned With Feedback, and no new work appears to have > been done on it since then. Why is it in this fest at all? There > certainly doesn't seem to be any

Re: [HACKERS] Faster methods for getting SPI results

So the conclusion at the end of the last commitfest was that this patch should be marked Returned With Feedback, and no new work appears to have been done on it since then. Why is it in this fest at all? There certainly doesn't seem to be any reason to review it again.

Re: [HACKERS] Faster methods for getting SPI results

On 12/20/16 23:14, Jim Nasby wrote: > I'm guessing one issue might be that > we don't want to call an external interpreter while potentially holding page > pins, but even then couldn't we just copy a single tuple at a time and save > a huge amount of palloc overhead? On 04/06/17 03:38, Craig

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

On 4/6/17 9:21 PM, Andres Freund wrote: Personally I'm way more excited about what a SPI feature like this could do for plpgsql than about what it can do for plpython. If the latter is what floats your boat, that's fine; but I want a feature that we can build on for other uses, not a hack that

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

On 2017-04-07 00:11:59 -0400, Tom Lane wrote: > Jim Nasby writes: > > On 4/6/17 8:13 PM, Tom Lane wrote: > >> Given Peter's objections, I don't think this is getting into v10 anyway, > >> so we might as well take a bit more time and do it right. > > > Well, Peter's

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

On Apr 6, 2017, at 9:10 PM, Andres Freund wrote: > >>> Why? We could very well return a somewhat "smarter" object. Returning >>> rows row-by-row if accessed via iterator, materializes when accessed via >>> row offset. >> >> I completely agree with that. What I don't

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

Jim Nasby writes: > On 4/6/17 8:13 PM, Tom Lane wrote: >> Given Peter's objections, I don't think this is getting into v10 anyway, >> so we might as well take a bit more time and do it right. > Well, Peter's objection is that we're not going far enough in plpython, > but

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

On 2017-04-06 21:06:59 -0700, Jim Nasby wrote: > On 4/6/17 9:04 PM, Andres Freund wrote: > > On 2017-04-06 09:14:43 -0700, Jim Nasby wrote: > > > On 4/6/17 9:04 AM, Peter Eisentraut wrote: > > > > On 4/6/17 03:50, Craig Ringer wrote: > > > > > But otherwise, pending docs changes, I think it's

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

On 4/6/17 9:04 PM, Andres Freund wrote: On 2017-04-06 09:14:43 -0700, Jim Nasby wrote: On 4/6/17 9:04 AM, Peter Eisentraut wrote: On 4/6/17 03:50, Craig Ringer wrote: But otherwise, pending docs changes, I think it's ready for committer. My opinion is still that this is ultimately the wrong

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

On 2017-04-06 09:14:43 -0700, Jim Nasby wrote: > On 4/6/17 9:04 AM, Peter Eisentraut wrote: > > On 4/6/17 03:50, Craig Ringer wrote: > > > But otherwise, pending docs changes, I think it's ready for committer. > > > > My opinion is still that this is ultimately the wrong approach. The > > right

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

On 4/6/17 8:13 PM, Tom Lane wrote: It's on the pointy end for Pg10, and I thought we'd be fine to include this in pg10 then aim to clean up DestReceiver in early pg11, or even as a post-feature-freeze refactoring fixup in pg10. Should the callback approach be blocked because the API it has to

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

Craig Ringer writes: > On 7 April 2017 at 00:54, Tom Lane wrote: >> ... External callers will only be >> interested in the result of the canSetTag subquery. > I wasn't aware that such queries could ever return a result set, though. Possibly not, but

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

On 7 April 2017 at 00:54, Tom Lane wrote: > I can certainly get on board with the idea of letting a SPI caller provide > a DestReceiver instead of accumulating the query results into a > SPITupleTable, but the way it was done here seems quite bizarre. I think > for instance

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

Peter Eisentraut writes: > On 4/6/17 03:50, Craig Ringer wrote: >> But otherwise, pending docs changes, I think it's ready for committer. > My opinion is still that this is ultimately the wrong approach. The > right fix for performance issues in PL/Python is to

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

On 4/6/17 9:04 AM, Peter Eisentraut wrote: On 4/6/17 03:50, Craig Ringer wrote: But otherwise, pending docs changes, I think it's ready for committer. My opinion is still that this is ultimately the wrong approach. The right fix for performance issues in PL/Python is to change PL/Python not

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

On 4/6/17 03:50, Craig Ringer wrote: > But otherwise, pending docs changes, I think it's ready for committer. My opinion is still that this is ultimately the wrong approach. The right fix for performance issues in PL/Python is to change PL/Python not to materialize the list of tuples. Now with

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

On 6 April 2017 at 15:38, Craig Ringer wrote: > Notes on the docs aside, I am pretty happy with this and think it's > reasonable to proceed with it for Pg 10. Actually, I'm a bit hesitant about returning a static struct that you expect callers to copy and modify. But it

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

On 6 April 2017 at 11:50, Jim Nasby wrote: > Attached is a complete series of patches that includes the docs patch. + SPI_execute_callback is the same as + SPI_execute, except that instead of returning results + via SPITupleTable, the user-supplied callback + is

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

On 4/5/17 9:08 PM, Craig Ringer wrote: ... which I can't reproduce now. Even though I cleared ccache and "git reset -fdx" before I ran the above and got the crash. Glad to hear that, since I can't repro at all. :) Assume it's a local system peculiarity. If I can reproduce again I'll dig into

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

On 6 April 2017 at 11:46, Craig Ringer wrote: > On 6 April 2017 at 09:40, Jim Nasby wrote: >> On 4/4/17 7:44 PM, Craig Ringer wrote: >>> >>> The patch crashes in initdb with --enable-cassert builds: >> >> >> Thanks for the review! I'll get to the

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

On 4/5/17 7:44 PM, Jim Nasby wrote: Updated patches attached, but I still need to update the docs. Attached is a complete series of patches that includes the docs patch. Right now, the docs don't include a concrete example, because adding one would be a pretty large if it demonstrated real

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

On 6 April 2017 at 09:40, Jim Nasby wrote: > On 4/4/17 7:44 PM, Craig Ringer wrote: >> >> The patch crashes in initdb with --enable-cassert builds: > > > Thanks for the review! I'll get to the rest of it in a bit, but I'm unable > to reproduce the initdb failure. I looked

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

On 4/4/17 7:44 PM, Craig Ringer wrote: On 5 April 2017 at 08:23, Craig Ringer wrote: On 5 April 2017 at 08:00, Craig Ringer wrote: This patch fails to update the documentation at all. https://www.postgresql.org/docs/devel/static/spi.html I'll

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

On 4/4/17 7:44 PM, Craig Ringer wrote: The patch crashes in initdb with --enable-cassert builds: Thanks for the review! I'll get to the rest of it in a bit, but I'm unable to reproduce the initdb failure. I looked at the assert line and I don't see anything obvious either. :/ Can you send

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

On 5 April 2017 at 08:23, Craig Ringer wrote: > On 5 April 2017 at 08:00, Craig Ringer wrote: > >> Taking a look at this now. > > Rebased to current master with conflicts and whitespace errors fixed. > Review pending. This patch fails to update the

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

On 5 April 2017 at 08:00, Craig Ringer wrote: > Taking a look at this now. Rebased to current master with conflicts and whitespace errors fixed. Review pending. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support,

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

On 6 March 2017 at 05:09, Jim Nasby wrote: > On 2/28/17 9:42 PM, Jim Nasby wrote: >>> >>> >>> I'll post a plpython patch that doesn't add the output format control. >> >> >> I've attached the results of that. Unfortunately the speed improvement >> is only 27% at this point

Re: [HACKERS] Faster methods for getting SPI results

On 3/5/17 16:07, Jim Nasby wrote: >> There is nothing that requires us to materialize the results into an >> actual list of actual rows. We could wrap the SPI_tuptable into a >> Python object and implement __getitem__ or __iter__ to emulate sequence >> or mapping access. > Would it be possible to

Re: [HACKERS] Faster methods for getting SPI results

On 3/2/17 8:03 AM, Peter Eisentraut wrote: On 12/20/16 23:14, Jim Nasby wrote: I've been looking at the performance of SPI calls within plpython. There's a roughly 1.5x difference from equivalent python code just in pulling data out of the SPI tuplestore. Some of that is due to an inefficiency

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

On 2/28/17 9:42 PM, Jim Nasby wrote: I'll post a plpython patch that doesn't add the output format control. I've attached the results of that. Unfortunately the speed improvement is only 27% at this point (with 999 tuples). Presumably that's because it's constructing a brand new

Re: [HACKERS] Faster methods for getting SPI results

On Thu, Mar 2, 2017 at 10:03 AM, Peter Eisentraut < peter.eisentr...@2ndquadrant.com> wrote: > On 12/20/16 23:14, Jim Nasby wrote: > > I've been looking at the performance of SPI calls within plpython. > > There's a roughly 1.5x difference from equivalent python code just in > > pulling data out

Re: [HACKERS] Faster methods for getting SPI results

On 12/20/16 23:14, Jim Nasby wrote: > I've been looking at the performance of SPI calls within plpython. > There's a roughly 1.5x difference from equivalent python code just in > pulling data out of the SPI tuplestore. Some of that is due to an > inefficiency in how plpython is creating result

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

On 1/24/17 10:43 PM, Jim Nasby wrote: I strongly suggest making this design effort a separate thread, and focusing on the SPI improvements that give "free" no-user-action performance boosts here. Fair enough. I posted the SPI portion of that yesterday. That should be useful for pl/R and

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

On 1/23/17 9:23 PM, Jim Nasby wrote: 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've thought more about this... instead of trying to switch from the current situation of 1 choice of how

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

On 1/23/17 10:36 PM, Craig Ringer wrote: which is currently returned as [ {"a":1, "b":10}, {"a":2, "b":20} ] instead as { "a": [1, 2], "b": [10, 20] } Correct. If so I see that as a lot more of a niche thing. I can see why it'd be useful and would help performance, but it seems much more

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

On 24 January 2017 at 11:23, Jim Nasby wrote: > 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,

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:

Re: [HACKERS] Faster methods for getting SPI results

On 12/28/16 3:14 AM, Craig Ringer wrote: On 28 December 2016 at 12:32, Jim Nasby wrote: On 12/27/16 9:10 PM, Craig Ringer wrote: On 28 December 2016 at 09:58, Jim Nasby wrote: I've looked at this some more, and ITSM that the only way to

Re: [HACKERS] Faster methods for getting SPI results

On 12/27/16 9:10 PM, Craig Ringer wrote: On 28 December 2016 at 09:58, Jim Nasby wrote: I've looked at this some more, and ITSM that the only way to do this without some major surgery is to create a new type of Destination specifically for SPI that allows for the

Re: [HACKERS] Faster methods for getting SPI results

On 28 December 2016 at 12:32, Jim Nasby wrote: > On 12/27/16 9:10 PM, Craig Ringer wrote: >> >> On 28 December 2016 at 09:58, Jim Nasby wrote: >> >>> I've looked at this some more, and ITSM that the only way to do this >>> without >>> some

Re: [HACKERS] Faster methods for getting SPI results

On 12/27/16 9:10 PM, Craig Ringer wrote: On 28 December 2016 at 09:58, Jim Nasby wrote: I've looked at this some more, and ITSM that the only way to do this without some major surgery is to create a new type of Destination specifically for SPI that allows for the

Re: [HACKERS] Faster methods for getting SPI results

On 28 December 2016 at 09:58, Jim Nasby wrote: > I've looked at this some more, and ITSM that the only way to do this without > some major surgery is to create a new type of Destination specifically for > SPI that allows for the execution of an arbitrary C function for

Re: [HACKERS] Faster methods for getting SPI results

On 12/21/16 8:21 AM, Jim Nasby wrote: On 12/20/16 10:14 PM, Jim Nasby wrote: It would be a lot more efficient if we could just grab datums from the executor and make a single copy into plpython (or R), letting the PL deal with all the memory management overhead. I briefly looked at using SPI

Re: [HACKERS] Faster methods for getting SPI results

On 12/20/16 10:14 PM, Jim Nasby wrote: It would be a lot more efficient if we could just grab datums from the executor and make a single copy into plpython (or R), letting the PL deal with all the memory management overhead. I briefly looked at using SPI cursors to do just that, but that looks

[HACKERS] Faster methods for getting SPI results

I've been looking at the performance of SPI calls within plpython. There's a roughly 1.5x difference from equivalent python code just in pulling data out of the SPI tuplestore. Some of that is due to an inefficiency in how plpython is creating result dictionaries, but fixing that is ultimately