Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-16 Thread David Rowley
On Wed, Jul 16, 2014 at 9:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 15 July 2014 12:58, David Rowley dgrowle...@gmail.com wrote: I found that the call to is_NOTANY_compatible_with_antijoin adds about 0.2% and 2.3% to total planning time. Though

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-15 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: I've made some changes to the patch so that it only allows the conversion to ANTI JOIN to take place if both the outer query's expressions AND the subquery's target list can be proved not to have NULLs. This coding doesn't fill me with warm fuzzy

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-15 Thread Simon Riggs
On 15 July 2014 12:58, David Rowley dgrowle...@gmail.com wrote: I found that the call to is_NOTANY_compatible_with_antijoin adds about 0.2% and 2.3% to total planning time. Though the 2.3% was quite an extreme case, and the 0.2% was the most simple case I could think of. I think its quite

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-15 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 15 July 2014 12:58, David Rowley dgrowle...@gmail.com wrote: I found that the call to is_NOTANY_compatible_with_antijoin adds about 0.2% and 2.3% to total planning time. Though the 2.3% was quite an extreme case, and the 0.2% was the most simple

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-14 Thread David Rowley
On Mon, Jul 14, 2014 at 3:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: I had another look at this and it appears you were right the first time, we need to ensure there's no NULLs on both sides of the join condition. Ugh. I'm back to being

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-14 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: On Mon, Jul 14, 2014 at 3:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ugh. I'm back to being discouraged about the usefulness of the optimization. Are you worried about the planning overhead of the not null checks, or is it more that you think there's

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-13 Thread David Rowley
On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: We could no doubt fix this by also insisting that the left-side vars be provably not null, but that's going to make the patch even slower and even less often applicable. I'm feeling discouraged about whether

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-13 Thread Andres Freund
On 2014-07-13 23:06:15 +1200, David Rowley wrote: I had another look at this and it appears you were right the first time, we need to ensure there's no NULLs on both sides of the join condition. The patch is currently marked as 'ready for committer' - that doesn't seem to correspond to the

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-13 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: I had another look at this and it appears you were right the first time, we need to ensure there's no NULLs on both sides of the join condition. Ugh. I'm back to being discouraged about the usefulness of the optimization. The only other way I could

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-11 Thread David Rowley
On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: We could no doubt fix this by also insisting that the left-side vars be provably not null, but that's going to make the patch even slower and even less often applicable. I'm feeling discouraged about whether

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-11 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hm ... actually, there might be a better answer: what about transforming WHERE (x,y) NOT IN (SELECT provably-not-null-values FROM ...) to WHERE antijoin condition AND x IS NOT NULL

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-10 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: Attached is the updated version of the patch. I spent some time fooling with this patch, cleaning up the join-alias issue as well as more-cosmetic things. However, while testing it I realized that the whole thing is based on a false premise: to equate a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-10 Thread Tom Lane
I wrote: We could no doubt fix this by also insisting that the left-side vars be provably not null, but that's going to make the patch even slower and even less often applicable. I'm feeling discouraged about whether this is worth doing in this form. Hm ... actually, there might be a better

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-09 Thread Jeevan Chalke
Hi, With further testing I noticed that the patch was not allowing ANTI joins in cases like this: explain select * from a where id not in(select x from b natural join c); I too found this with natural joins and was about to report that. But its good that you found that and fixed it as

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-05 Thread David Rowley
On Wed, Jul 2, 2014 at 9:25 PM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: On Sun, Jun 29, 2014 at 4:18 PM, David Rowley dgrowle...@gmail.com wrote: I think I'm finally ready for a review again, so I'll update the commitfest app. I have reviewed this on code level. 1. Patch

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-02 Thread Jeevan Chalke
On Sun, Jun 29, 2014 at 4:18 PM, David Rowley dgrowle...@gmail.com wrote: I think I'm finally ready for a review again, so I'll update the commitfest app. I have reviewed this on code level. 1. Patch gets applied cleanly. 2. make/make install/make check all are fine No issues found till

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-07-02 Thread David Rowley
On Wed, Jul 2, 2014 at 9:25 PM, Jeevan Chalke jeevan.cha...@enterprisedb.com wrote: Testing more on SQL level. I'm just looking into an issue I've found in the find_inner_rels() function, where it does not properly find the rel in the from list in certain cases, for example: explain

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-29 Thread David Rowley
On Fri, Jun 27, 2014 at 6:14 AM, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: If there's no way to tell that the target entry comes from a left join, then would it be a bit too quirky to just do the NOT NULL checking when list_length(query-rtable) == 1 ? or

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-26 Thread David Rowley
On Thu, Jun 26, 2014 at 3:52 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: To be clearer, what I mean is we use only the direct proof approach, for queries like this SELECT * FROM a WHERE id NOT IN(SELECT unknown_col FROM b WHERE unknown_col IS NOT

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-26 Thread Simon Riggs
On 26 June 2014 10:31, David Rowley dgrowle...@gmail.com wrote: If there's no way to tell that the target entry comes from a left join, then would it be a bit too quirky to just do the NOT NULL checking when list_length(query-rtable) == 1 ? or maybe even loop over query-rtable and abort if we

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-26 Thread Tom Lane
David Rowley dgrowle...@gmail.com writes: If there's no way to tell that the target entry comes from a left join, then would it be a bit too quirky to just do the NOT NULL checking when list_length(query-rtable) == 1 ? or maybe even loop over query-rtable and abort if we find an outer join

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-25 Thread Simon Riggs
On 24 June 2014 23:22, Simon Riggs si...@2ndquadrant.com wrote: On a more positive or even slightly exciting note I think I've managed to devise a way that ANTI JOINS can be used for NOT IN much more often. It seems that find_nonnullable_vars will analyse a quals list to find expressions that

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-25 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: To be clearer, what I mean is we use only the direct proof approach, for queries like this SELECT * FROM a WHERE id NOT IN(SELECT unknown_col FROM b WHERE unknown_col IS NOT NULL); and we don't try to do it for queries like this SELECT * FROM

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread David Rowley
On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Simon Riggs
On 24 June 2014 11:32, David Rowley dgrowle...@gmail.com wrote: So if anyone can point me in the right direction then that would be really useful. Many things can be added simply, but most things can't. It seems we just don't have that information. If we did, Tom would have done this already.

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Simon Riggs
On 11 June 2014 17:52, Greg Stark st...@mit.edu wrote: On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: If we didn't have mechanisms like this, we'd have far worse hazards from ALTER TABLE than whether the planner made an incorrect join optimization. Consider ALTER COLUMN

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: Having said that, any join plan that relies upon a constraint will still be valid even if we drop a constraint while the plan executes because any new writes will not be visible to the executing join plan. mumble ... EvalPlanQual ?

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Simon Riggs
On 24 June 2014 23:44, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Having said that, any join plan that relies upon a constraint will still be valid even if we drop a constraint while the plan executes because any new writes will not be visible to the executing

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: On 24 June 2014 23:44, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Having said that, any join plan that relies upon a constraint will still be valid even if we drop a constraint while the plan executes because any new

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-24 Thread Simon Riggs
On 24 June 2014 23:52, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On 24 June 2014 23:44, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Having said that, any join plan that relies upon a constraint will still be valid even if we

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread David Rowley
On Mon, Jun 9, 2014 at 11:20 PM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread David Rowley
On Tue, Jun 10, 2014 at 2:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com writes: If you are using NOT IN, then once you find a NULL in the outer input (if the outer input is the in-list: clearly you can't reverse the two inputs in this case), you don't even need

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Marti Raudsepp
On Wed, Jun 11, 2014 at 11:53 AM, David Rowley dgrowle...@gmail.com wrote: The only way to consistently guarantee nullability is through primary key constraints. Fortunately that addresses most of the use cases of NOT IN(), in my experience. See the comment in check_functional_grouping: I

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Marti Raudsepp
On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. There's a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread David Rowley
On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp ma...@juffo.org wrote: On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Tom Lane
Marti Raudsepp ma...@juffo.org writes: On Wed, Jun 11, 2014 at 11:53 AM, David Rowley dgrowle...@gmail.com wrote: as long as the transaction id is taken before we start planning in session 1 then it should not matter if another session drops the constraint and inserts a NULL value as we won't

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-11 Thread Greg Stark
On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: If we didn't have mechanisms like this, we'd have far worse hazards from ALTER TABLE than whether the planner made an incorrect join optimization. Consider ALTER COLUMN TYPE for instance. Obviously not general cases of ALTER

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Martijn van Oosterhout
On Mon, Jun 09, 2014 at 12:36:30AM +1200, David Rowley wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. Awesome. I've had a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Marti Raudsepp
On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. I believe

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Vik Fearing
On 06/08/2014 02:36 PM, David Rowley wrote: + if (!get_attnotnull(tle-resorigtbl, tle-resorigcol)) + return false; As Marti says, you can't do this because NOT NULL doesn't have an oid to attach a dependency to. You'll have to restrict this test to primary keys

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Tom Lane
Marti Raudsepp ma...@juffo.org writes: On Sun, Jun 8, 2014 at 3:36 PM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Jeff Janes
On Sun, Jun 8, 2014 at 5:36 AM, David Rowley dgrowle...@gmail.com wrote: Currently pull_up_sublinks_qual_recurse only changes the plan for NOT EXISTS queries and leaves NOT IN alone. The reason for this is because the values returned by a subquery in the IN clause could have NULLs. A simple

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Sun, Jun 8, 2014 at 5:36 AM, David Rowley dgrowle...@gmail.com wrote: The attached patch allows an ANTI-join plan to be generated in cases like: CREATE TABLE a (id INT PRIMARY KEY, b_id INT NOT NULL); CREATE TABLE b (id INT NOT NULL); SELECT * FROM a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Jeff Janes
On Monday, June 9, 2014, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Janes jeff.ja...@gmail.com javascript:; writes: On Sun, Jun 8, 2014 at 5:36 AM, David Rowley dgrowle...@gmail.com javascript:; wrote: The attached patch allows an ANTI-join plan to be generated in cases like: CREATE TABLE a

Re: [HACKERS] Allowing NOT IN to use ANTI joins

2014-06-09 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: On Monday, June 9, 2014, Tom Lane t...@sss.pgh.pa.us wrote: Huh? The point of an antijoin (or indeed most join methods) is that we *don't* have to examine the whole inner input to make a decision. But all hash join methods needs to examine the entire