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 (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 (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: