reducejoins.c ?
flattenjoins.c ?
filterjoins.c ?
--
dim
Le 28 mars 2010 à 22:12, Tom Lane t...@sss.pgh.pa.us a écrit :
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
joinremoval.c ?
Maybe, except as I mentioned in the email
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 |
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
2010/3/29 Marko Tiikkaja marko.tiikk...@cs.helsinki.fi:
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
On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I'm not seeing how that would occur or would matter, but the worst case
answer is to restart the scan of the
Robert Haas robertmh...@gmail.com 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
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 to
Simon Riggs si...@2ndquadrant.com 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
I wrote:
Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane t...@sss.pgh.pa.us 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
On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 12:19 AM, Tom Lane t...@sss.pgh.pa.us wrote:
* I left join_is_removable where it was, mainly so that it was easy to
compare how much it changed for this usage
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sun, Mar 28, 2010 at 4:12 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Sun, Mar 28, 2010 at 2:10 PM, Tom Lane t...@sss.pgh.pa.us wrote:
joinremoval.c ?
Maybe, except as I mentioned in the email linked upthread, my plan for
implementing inner join
On Fri, Mar 26, 2010 at 6:10 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com 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
On Sat, Mar 27, 2010 at 10:50 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
Robert Haas robertmh...@gmail.com writes:
On Sat, Mar 27, 2010 at 10:50 AM, Tom Lane t...@sss.pgh.pa.us 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
On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes:
On Sat, Mar 27, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us 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.
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.
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes:
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sun, Aug 16, 2009 at 5:31 PM, Robert Haasrobertmh...@gmail.com 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
On Sun, Aug 9, 2009 at 12:19 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com 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
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
Robert Haas robertmh...@gmail.com 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
On Sun, Aug 9, 2009 at 5:19 PM, Tom Lanet...@sss.pgh.pa.us 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
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lanet...@sss.pgh.pa.us 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
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
On Fri, Jul 24, 2009 at 7:53 AM, Alex Brasetvika...@brasetvik.com 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
Robert Haas robertmh...@gmail.com writes:
On Thu, Jul 16, 2009 at 9:02 PM, Greg Starkst...@mit.edu 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
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Thu, Jul 16, 2009 at 9:02 PM, Greg Starkst...@mit.edu wrote:
I have one big worry though. Currently you're detecting the unique
property using the planner's path mechanism. I
Robert Haas robertmh...@gmail.com writes:
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lanet...@sss.pgh.pa.us 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
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
On Thu, Jul 16, 2009 at 9:02 PM, Greg Starkst...@mit.edu 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
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 join
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 hours
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 a blog there
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:
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
PostgreSQL
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 enough
to have
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 recode the
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 have a
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, but I
46 matches
Mail list logo
