Re: [HACKERS] Discussion on missing optimizations

2017-10-22 Thread Andreas Seltenreich
Alvaro Herrera writes: > Andres Freund wrote: >> Unfortunately it won't help in this specific case (no support for UNION, >> just UNION ALL), but I thought it might be interesting to reference >> https://medium.com/@uwdb/introducing-cosette-527898504bd6 >> here. > > Interesting. I thought about

Re: [HACKERS] Discussion on missing optimizations

2017-10-13 Thread Andres Freund
Hi, On 2017-10-14 10:38:13 +1300, David Rowley wrote: > On 12 October 2017 at 04:50, Robert Haas wrote: > > We haven't really done a very good job figuring out what to do about > > optimizations that some people (mostly you) think are marginal. It's > > obviously true

Re: [HACKERS] Discussion on missing optimizations

2017-10-13 Thread David Rowley
On 12 October 2017 at 04:50, Robert Haas wrote: > We haven't really done a very good job figuring out what to do about > optimizations that some people (mostly you) think are marginal. It's > obviously true that we can't just burn infinite planner cycles on > things that

Re: [HACKERS] Discussion on missing optimizations

2017-10-13 Thread Adam Brusselback
So from following this discussion and others focused on making the planner "smarter", there is always an argument to be had over wasting planner cycles, and it's always a hard fought battle to get any changes made. Now, i'm speaking without any knowledge of the Postgres internals, so please bear

Re: [HACKERS] Discussion on missing optimizations

2017-10-13 Thread Laurenz Albe
Stephen Frost wrote: > * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > > Robert Haas wrote: > > > One trick that some system use is avoid replanning as much as we do > > > by, for example, saving plans in a shared cache and reusing them even > > > in other sessions. That's hard to do in our

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread Stephen Frost
Laurenz, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > Robert Haas wrote: > > One trick that some system use is avoid replanning as much as we do > > by, for example, saving plans in a shared cache and reusing them even > > in other sessions. That's hard to do in our architecture because

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread David Rowley
On 13 October 2017 at 03:00, Tom Lane wrote: > Laurenz Albe writes: >> Robert Haas wrote: >>> One trick that some system use is avoid replanning as much as we do >>> by, for example, saving plans in a shared cache and reusing them even >>> in other

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread Robert Haas
On Thu, Oct 12, 2017 at 10:00 AM, Tom Lane wrote: >> From my experience with Oracle I would say that that is a can of worms. > > Yeah, I'm pretty suspicious of the idea too. We've had an awful lot of > bad experience with local plan caching, to the point where people wonder >

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread Tom Lane
Laurenz Albe writes: > Robert Haas wrote: >> One trick that some system use is avoid replanning as much as we do >> by, for example, saving plans in a shared cache and reusing them even >> in other sessions. That's hard to do in our architecture because the >>

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread Alvaro Herrera
Andres Freund wrote: > On 2017-10-08 11:28:09 -0400, Tom Lane wrote: > > Adam Brusselback writes: > > > On another note: > > >> turning ORs into UNIONs > > > > > This is another one which would be incredibly useful for me. I've had > > > to do this manually for

Re: [HACKERS] Discussion on missing optimizations

2017-10-12 Thread Laurenz Albe
Robert Haas wrote: > One trick that some system use is avoid replanning as much as we do > by, for example, saving plans in a shared cache and reusing them even > in other sessions. That's hard to do in our architecture because the > controlling GUCs can be different in every session and there's

Re: [HACKERS] Discussion on missing optimizations

2017-10-11 Thread Robert Haas
On Fri, Oct 6, 2017 at 10:19 PM, Tom Lane wrote: >> 9. Unneeded Self JOIN > >> Can't remember discussions of this. > > I can't get very excited about that one either. My memories of being a PostgreSQL user rather than a developer are getting a bit distant by now, but I

Re: [HACKERS] Discussion on missing optimizations

2017-10-09 Thread Ants Aasma
On Sun, Oct 8, 2017 at 7:24 PM, Tom Lane wrote: > Petr Jelinek writes: >> Okay, that makes sense, thanks for explanation. Your patch is the way to >> go then. > > Hearing no further comment, pushed. Thanks for reviewing it. The tautological col

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread David Rowley
On 9 October 2017 at 17:41, David Rowley wrote: > Thoughts? Actually, I was a little inconsistent with my List NULL/NIL checks in that last one. I've attached an updated patch. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread David Rowley
On 7 October 2017 at 14:48, Andres Freund wrote: > 3. JOIN Elimination > > There's been a lot of discussion and several patches. There's a bunch of > problems here, one being that there's cases (during trigger firing, > before the constraint checks) where foreign keys don't

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Andres Freund
On 2017-10-08 17:11:44 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-10-08 11:28:09 -0400, Tom Lane wrote: > >> https://commitfest.postgresql.org/15/1001/ > >> The reason that's not in v10 is we haven't been able to convince > >> ourselves whether it's 100%

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Andres Freund writes: > On 2017-10-08 11:28:09 -0400, Tom Lane wrote: >> https://commitfest.postgresql.org/15/1001/ >> The reason that's not in v10 is we haven't been able to convince >> ourselves whether it's 100% correct. > Unfortunately it won't help in this specific case

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Petr Jelinek writes: > Okay, that makes sense, thanks for explanation. Your patch is the way to > go then. Hearing no further comment, pushed. Thanks for reviewing it. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Andres Freund
On 2017-10-08 11:28:09 -0400, Tom Lane wrote: > Adam Brusselback writes: > > On another note: > >> turning ORs into UNIONs > > > This is another one which would be incredibly useful for me. I've had > > to do this manually for performance reasons far too often. > >

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Tom Lane
Adam Brusselback writes: > On another note: >> turning ORs into UNIONs > This is another one which would be incredibly useful for me. I've had > to do this manually for performance reasons far too often. Well, maybe you could sign up to help review the open patch for

Re: [HACKERS] Discussion on missing optimizations

2017-10-08 Thread Adam Brusselback
> I can't get very excited about this one either, though I do believe it > can arise as the author says, "when you build complex views and JOIN > them to each other". Maybe I'm not excited about it because I've not > needed it :) This is one that I know would help with my database. There is a

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 19:59, Tom Lane wrote: > Petr Jelinek writes: >> On 07/10/17 18:15, Tom Lane wrote: >>> No, I'm afraid you didn't read that comment closely enough. This will >>> flat out fail for cases like "select ... where x=x order by x", because >>> there will

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Nico Williams
On Fri, Oct 06, 2017 at 10:19:54PM -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: > >> The article in question is here: > >>

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
Petr Jelinek writes: > On 07/10/17 18:15, Tom Lane wrote: >> No, I'm afraid you didn't read that comment closely enough. This will >> flat out fail for cases like "select ... where x=x order by x", because >> there will already be a single-element EC for x and so

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 18:15, Tom Lane wrote: > Petr Jelinek writes: >> On 07/10/17 04:19, Tom Lane wrote: >>> (edit: a few minutes later, I seem to remember that equivclass.c has >>> to do something special with the X=X case, so maybe it could do >>> something else special

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
David Rowley writes: > It would be much nicer if you'd at least wait for benchmarks before > shooting. Benchmarks of what? We'd have to expend quite a bit of effort just to get to a place where we'd have something to benchmark. I do not think it's unreasonable of

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Tom Lane
Petr Jelinek writes: > On 07/10/17 04:19, Tom Lane wrote: >> (edit: a few minutes later, I seem to remember that equivclass.c has >> to do something special with the X=X case, so maybe it could do >> something else special instead, with little new overhead.) > So I

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread David Rowley
On 7 October 2017 at 15:19, Tom Lane wrote: >> 9. Unneeded Self JOIN > >> Can't remember discussions of this. > > I can't get very excited about that one either. > > In the end, what the article fails to consider is that all of these are > tradeoffs, not unalloyed goods. If

Re: [HACKERS] Discussion on missing optimizations

2017-10-07 Thread Petr Jelinek
On 07/10/17 04:19, Tom Lane wrote: > Andres Freund writes: >> On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: >>> The article in question is here: >>> https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/ > >> That's

Re: [HACKERS] Discussion on missing optimizations

2017-10-06 Thread Andres Freund
On 2017-10-06 22:19:54 -0400, Tom Lane wrote: > The impression I have in a quick scan is that probably hardly any of these > are cases that any of the DB designers think are important in > themselves. > In the end, what the article fails to consider is that all of these are > tradeoffs, not

Re: [HACKERS] Discussion on missing optimizations

2017-10-06 Thread Tom Lane
Andres Freund writes: > On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: >> The article in question is here: >> https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/ > That's interesting. The impression I have in a quick scan

Re: [HACKERS] Discussion on missing optimizations

2017-10-06 Thread Andres Freund
Hi, On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: > Hopefully it's alright for me to post this here, please let me know if not. > I ran across an article on blog.jooq.org comparing all the major RDBMS' > with their ability to optimize away unnecessary work with queries which are > less

[HACKERS] Discussion on missing optimizations

2017-10-06 Thread Adam Brusselback
Hopefully it's alright for me to post this here, please let me know if not. I ran across an article on blog.jooq.org comparing all the major RDBMS' with their ability to optimize away unnecessary work with queries which are less than optimal, and saw some discussion on hackernews and reddit, but