On Wed, Jul 16, 2014 at 9:11 AM, Tom Lane wrote:
> Simon Riggs writes:
> > On 15 July 2014 12:58, David Rowley 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 th
Simon Riggs writes:
> On 15 July 2014 12:58, David Rowley 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.
> Is there a w
On 15 July 2014 12:58, David Rowley 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 important that we don
David Rowley 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 feelings.
query_outputs
David Rowley writes:
> On Mon, Jul 14, 2014 at 3:00 AM, Tom Lane 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 a much smaller chance of a real
On Mon, Jul 14, 2014 at 3:00 AM, Tom Lane wrote:
> David Rowley 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
>
David Rowley 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 imagine fixing this
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 dis
On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane 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
> > this is wort
David Rowley writes:
> On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane 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 AND x IS NOT NULL AND y IS NOT NULL
> I think this is the way to go
On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane 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
> > this is wort
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 bett
David Rowley 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 NOT IN with an anti
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
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
> 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
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
On Sun, Jun 29, 2014 at 4:18 PM, David Rowley 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 now.
However some c
On Fri, Jun 27, 2014 at 6:14 AM, Tom Lane wrote:
> David Rowley 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->rta
David Rowley 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 type? it seems a b
On 26 June 2014 10:31, David Rowley 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 find an outer
On Thu, Jun 26, 2014 at 3:52 AM, Tom Lane wrote:
> Simon Riggs 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 t
Simon Riggs 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 a WHERE id NOT I
On 24 June 2014 23:22, Simon Riggs 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 mean that the
On 24 June 2014 23:52, Tom Lane wrote:
> Simon Riggs writes:
>> On 24 June 2014 23:44, Tom Lane wrote:
>>> Simon Riggs 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 wr
Simon Riggs writes:
> On 24 June 2014 23:44, Tom Lane wrote:
>> Simon Riggs 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
On 24 June 2014 23:44, Tom Lane wrote:
> Simon Riggs 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 ... EvalPl
Simon Riggs 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 ?
regards, tom l
On 11 June 2014 17:52, Greg Stark wrote:
> On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane 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.
>
> Obvi
On 24 June 2014 11:32, David Rowley 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.
> On a more positi
On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp wrote:
> On Sun, Jun 8, 2014 at 3:36 PM, 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 subqu
On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane 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 COLUMN TYPE but d
Marti Raudsepp writes:
> On Wed, Jun 11, 2014 at 11:53 AM, David Rowley 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
>> see that NULL value in our
On Wed, Jun 11, 2014 at 9:32 PM, Marti Raudsepp wrote:
> On Sun, Jun 8, 2014 at 3:36 PM, 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 subqu
On Sun, Jun 8, 2014 at 3:36 PM, 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.
There's a bug in targetListIs
On Wed, Jun 11, 2014 at 11:53 AM, David Rowley 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 saw that, but
On Tue, Jun 10, 2014 at 2:19 PM, Tom Lane wrote:
> Jeff Janes 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 to finish reading the outer in
On Mon, Jun 9, 2014 at 11:20 PM, Marti Raudsepp wrote:
> On Sun, Jun 8, 2014 at 3:36 PM, 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 subqu
Jeff Janes writes:
> On Monday, June 9, 2014, Tom Lane 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 *outer* input, no?
> Have I screw
On Monday, June 9, 2014, Tom Lane wrote:
> Jeff Janes > writes:
> > On Sun, Jun 8, 2014 at 5:36 AM, David Rowley > 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
Jeff Janes writes:
> On Sun, Jun 8, 2014 at 5:36 AM, David Rowley 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 WHERE b_id NOT IN(SELECT id FRO
On Sun, Jun 8, 2014 at 5:36 AM, 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.
>
> A simple example of this
Marti Raudsepp writes:
> On Sun, Jun 8, 2014 at 3:36 PM, 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.
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
ke
On Sun, Jun 8, 2014 at 3:36 PM, 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.
I believe the reason why this
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
45 matches
Mail list logo
