2010/3/29 Marko Tiikkaja :
> On 2010-03-29 11:19 +0200, Pavel Stehule wrote:
>> postgres=# explain select a from a left join b on true;
you have a true.
I forgot SELECT DISTINCT
regards
Pavel
>
> This is effectively a cross join and it would give wrong answers. Try
> SELECT a FROM a LEFT JOIN
On 2010-03-29 11:19 +0200, Pavel Stehule wrote:
> postgres=# explain select a from a left join b on true;
This is effectively a cross join and it would give wrong answers. Try
SELECT a FROM a LEFT JOIN b ON a.a = b.b;
Regards,
Marko Tiikkaja
--
Sent via pgsql-hackers mailing list (pgsql-hack
Hello
is any reason why join removal doesn't remove useless relation b?
postgres=# \d a
Table "public.a"
Column | Type | Modifiers
+-+---
a | integer |
Indexes:
"a_a_idx" UNIQUE, btree (a)
postgres=# \d b
Table "public.b"
Column | Type | Mo
reducejoins.c ?
flattenjoins.c ?
filterjoins.c ?
--
dim
Le 28 mars 2010 à 22:12, Tom Lane a écrit :
Robert Haas writes:
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane wrote:
joinremoval.c ?
Maybe, except as I mentioned in the email linked upthread, my plan
for
implementing inner join r
On Sun, Mar 28, 2010 at 4:12 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane wrote:
>>> joinremoval.c ?
>
>> Maybe, except as I mentioned in the email linked upthread, my plan for
>> implementing inner join removal would also include allowing join
>> reorde
Robert Haas writes:
> On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane wrote:
>> joinremoval.c ?
> Maybe, except as I mentioned in the email linked upthread, my plan for
> implementing inner join removal would also include allowing join
> reordering in cases where we currently don't. So I don't want t
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane wrote:
>>> * in a new file in plan/. Not sure if it's worth this, though your
>>> thought that we might add more logic later makes it more defensible.
>
>> I sort of like the last
Robert Haas writes:
> On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane wrote:
>> * in a new file in plan/. Not sure if it's worth this, though your
>> thought that we might add more logic later makes it more defensible.
> I sort of like the last of these ideas though I'm at a loss for what
> to call i
On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane wrote:
>>> * I left join_is_removable where it was, mainly so that it was easy to
>>> compare how much it changed for this usage (not a lot). I'm not sure
>>> that joinpath.c is
Robert Haas writes:
> On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane wrote:
>> * I left join_is_removable where it was, mainly so that it was easy to
>> compare how much it changed for this usage (not a lot). I'm not sure
>> that joinpath.c is an appropriate place for it anymore, though I can't
>> s
I wrote:
> Robert Haas writes:
>> I'm alarmed by your follow-on statement that the current code can't
>> handle the two-levels of removable join case. Seems like it ought to
>> form {B C} as a path over {B} and then {A B C} as a path over {A}.
> Actually I think it ought to form {A B} as a no-op
Simon Riggs writes:
> Does the new patch find more than two levels of join removal?
Well, I'd assume if it can do two nested levels then it should work for
any number, but I plead guilty to not having actually tested that.
regards, tom lane
--
Sent via pgsql-hackers mai
On Sun, 2010-03-28 at 02:15 -0400, Tom Lane wrote:
> I wrote:
> > [ crude patch ]
>
> Oh, btw, if you try to run the regression test additions in that patch
> against CVS HEAD, you'll find out that HEAD actually fails to optimize
> the two-levels-of-removable-joins case. Seems like another reason
Robert Haas writes:
> I'm alarmed by your follow-on statement that the current code can't
> handle the two-levels of removable join case. Seems like it ought to
> form {B C} as a path over {B} and then {A B C} as a path over {A}.
Actually I think it ought to form {A B} as a no-op join and then b
On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane wrote:
> Robert Haas writes:
>> On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane wrote:
>>> I'm not seeing how that would occur or would matter, but the worst case
>>> answer is to restart the scan of the SpecialJoinInfos from scratch any
>>> time you succeed
I wrote:
> [ crude patch ]
Oh, btw, if you try to run the regression test additions in that patch
against CVS HEAD, you'll find out that HEAD actually fails to optimize
the two-levels-of-removable-joins case. Seems like another reason to
press ahead with making the change.
Robert Haas writes:
> On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane wrote:
>> I'm not seeing how that would occur or would matter, but the worst case
>> answer is to restart the scan of the SpecialJoinInfos from scratch any
>> time you succeed in doing a join removal.
> Well, say you have something
On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane wrote:
>> In particular, if
>> you remove one join, it might make some other join that wasn't
>> previously removable now able to be removed, and it's not exactly
>> clear to me how to make this method cope with that.
>
> I'm not seeing how that would occu
Robert Haas writes:
> On Sat, Mar 27, 2010 at 10:50 AM, Tom Lane wrote:
>> It might be that in
>> practice it has to succeed with the min LHS if it's going to succeed
>> anywhere, but I'm not convinced.
> It's a bit difficult to wrap one's brain around all the cases, but I
> think that the state
On Sat, Mar 27, 2010 at 10:50 AM, Tom Lane wrote:
> Robert Haas writes:
>> I'm not totally sure about this but I think it's possible to do this
>> without a combinatorial search. Suppose we just iterate over the list
>> of
>> SpecialJoinInfo structures and look for those where jointype is LEFT,
Robert Haas writes:
> I'm not totally sure about this but I think it's possible to do this
> without a combinatorial search. Suppose we just iterate over the list
> of
> SpecialJoinInfo structures and look for those where jointype is LEFT,
> delay_upper_joins is false, and min_righthand is a sing
On Fri, Mar 26, 2010 at 6:10 PM, Tom Lane wrote:
>> Reading through the "optimizer functions" section of
>> src/backend/optimizer/README, it seems like the earliest point at
>> which we could do this would be just before the call to
>> make_one_rel(). I think that would eliminate some redundant
>
Robert Haas writes:
> On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote:
>> Yeah. Ideally this sort of thing would happen in prepjointree.c, but
>> we don't have nearly enough information at that stage.
> You've mentioned this point a couple of times - what is ideal about
> prepjointree?
Well,
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote:
> Yeah. Ideally this sort of thing would happen in prepjointree.c, but
> we don't have nearly enough information at that stage.
Tom,
You've mentioned this point a couple of times - what is ideal about
prepjointree? Reading through the "optimize
On Sun, Aug 16, 2009 at 5:31 PM, Robert Haas wrote:
> It seems that the needed checks are very similar to the ones that we
> already implement when setting restrictinfo->mergeopfamilies. That is
> filled in by get_mergejoin_opfamilies(), which checks for btree
> opfamilies where the strategy numbe
On Sun, Aug 9, 2009 at 12:19 PM, Tom Lane wrote:
> Robert Haas writes:
>> distinct_col_search() is going to return the relevant equality
>> operator from the argument list, which is ultimately going to come
>> from the RestrictInfo for the join clause. So I need to see whether
>> that's compatibl
> I took at a first crack at coding up an implementation of
> relation_is_distinct_for() tonight.
I am not sure if this will help or not, but on the 8.4 code base we
implemented two functions:
- getCandidateKeys() - would recursively traverse a tree from a given
node to the leaf nodes and determi
On Sun, Aug 9, 2009 at 5:19 PM, Tom Lane wrote:
>
>> I am having a hard time wrapping my brain around what it means to have
>> multiple, incompatible notions of equality... any help appreciated!
>
> Well, for instance a complex-number datatype could have one btree
> opclass that sorts on absolute v
Robert Haas writes:
> distinct_col_search() is going to return the relevant equality
> operator from the argument list, which is ultimately going to come
> from the RestrictInfo for the join clause. So I need to see whether
> that's compatible with the index, but equality_ops_are_compatible()
> w
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote:
> I think we want something along the lines of relation_is_distinct_for
> with a list of columns and a list of comparison operators, where the
> first-cut implementation will be to look for matching indexes.
> This will be different from query_is_di
On Fri, Jul 24, 2009 at 7:53 AM, Alex Brasetvik wrote:
>
> On Jul 17, 2009, at 04:27 , Robert Haas wrote:
>
>> - INNER joins are more complex because what happens on the inner side
>> of the join can potentially wipe out rows from the result. With a
>> LEFT join, it's sufficient to prove that the
On Jul 17, 2009, at 04:27 , Robert Haas wrote:
- INNER joins are more complex because what happens on the inner side
of the join can potentially wipe out rows from the result. With a
LEFT join, it's sufficient to prove that the inner rel is at least
unique enough, but for an INNER join, we hav
Robert Haas writes:
> On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote:
>> I think we want something along the lines of relation_is_distinct_for
>> with a list of columns and a list of comparison operators, where the
>> first-cut implementation will be to look for matching indexes.
> That seems r
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote:
> Robert Haas writes:
>> On Thu, Jul 16, 2009 at 9:02 PM, Greg Stark wrote:
>>> I have one big worry though. Currently you're detecting the unique
>>> property using the planner's path mechanism. I suppose that works, but
>>> it's only an accident
Robert Haas writes:
> On Thu, Jul 16, 2009 at 9:02 PM, Greg Stark wrote:
>> I have one big worry though. Currently you're detecting the unique
>> property using the planner's path mechanism. I suppose that works, but
>> it's only an accident of the planner design that the path for the
>> unique in
On Thu, Jul 16, 2009 at 9:02 PM, Greg Stark wrote:
> I started looking at this patch and it looks pretty good as far as it
> goes. But I think we can do a lot more. It seems to me the cases where
> foreign key relationships exist are likely to be really big use cases.
I agree. But that seems a lo
I started looking at this patch and it looks pretty good as far as it
goes. But I think we can do a lot more. It seems to me the cases where
foreign key relationships exist are likely to be really big use cases.
I have one big worry though. Currently you're detecting the unique
property using the
Apologies for submitting new patches while we're still in the midst of
CommitFest:November, but I think I've more or less come to the end of
the reviewing that I can usefully do for 8.4 (or at least, I haven't
had any requests lately, feel free...) and I don't want to wait
forever to start thinking
Simon Riggs wrote:
>
> On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > We can check for removal of a rel by...
>
> OT comment: I just found a blog about Oracle's optimizermagic, which is
> quite interesting. I notice there is a blog there about
On Thu, 2008-08-14 at 09:27 -0400, Robert Haas wrote:
> I'm guessing it's this... looks pretty interesting even if not.
>
> http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.html
Yes, thanks for copying it in.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQ
I'm guessing it's this... looks pretty interesting even if not.
http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.html
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mail
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
>> Simon Riggs <[EMAIL PROTECTED]> writes:
>> > We can check for removal of a rel by...
>
> OT comment: I just found a blog about Oracle's optimizermagic, which is
> quite interesting. I notice there is
On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > We can check for removal of a rel by...
OT comment: I just found a blog about Oracle's optimizermagic, which is
quite interesting. I notice there is a blog there about join removal,
posted about 12 ho
On Fri, 2008-06-27 at 17:50 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
> >> It might be possible to treat "ignore the RHS" as a join strategy and
> >> try to apply it while forming join relations, which would be late enou
Simon Riggs <[EMAIL PROTECTED]> writes:
> On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
>> It might be possible to treat "ignore the RHS" as a join strategy and
>> try to apply it while forming join relations, which would be late enough
>> to have all the needed info available.
> Oh, actually
On Thu, 2008-06-26 at 13:42 -0400, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > We can check for removal of a rel by
>
> > 1. inspecting the target list for the query to see if there are rels
> > that do not provide any attributes. (We might also use equivalence
> > classes to re
Simon Riggs <[EMAIL PROTECTED]> writes:
> We can check for removal of a rel by
> 1. inspecting the target list for the query to see if there are rels
> that do not provide any attributes. (We might also use equivalence
> classes to recode the targetlist to minimise the numbers of tables
> touched,
There are common cases where we want to remove unwanted joins from
queries, especially with view and Object Relational Mapping systems such
as Hibernate etc.. (I've mentioned this before on -hackers or -perform,
but I can't find the links)
Typical case is where we have a class that has a subclass
48 matches
Mail list logo
