Re: omitting redundant join predicate
От | Ehab Galal |
---|---|
Тема | Re: omitting redundant join predicate |
Дата | |
Msg-id | BAY138-W453548E0FFC1D11A00D2AD96880@phx.gbl обсуждение исходный текст |
Ответ на | Re: omitting redundant join predicate (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: omitting redundant join predicate
|
Список | pgsql-sql |
<div style="text-align: left;"><div style="text-align: left;">Sorry for not being clear enough. What i meant is how awarethe optimizer is about the transitivity of operators. <br /><br /> I agree that the more join clauses a query gets,the more flexibility the optimizer gets to pick an optimal plan.<br /><br /> what i expected is that the optimizer willuse the redundant predicates to create the plan, but the execution plan itself will not execute a redundant predicate.<br /><br /> O! I see, it's my mistake. The example i mentioned was not a good example. I tried the equality andit is working well :)<br /><br /></div> Nested Loop (cost=0.00..3.14 rows=1 width=368)<br /> Join Filter: ("outer".username= "inner".username)<br /> -> Nested Loop (cost=0.00..2.10 rows=1 width=218)<br /> JoinFilter: ("outer".username = "inner".username)<br /> -> Seq Scan on t1 (cost=0.00..1.01 rows=1 width=146)<br/> -> Seq Scan on t3 (cost=0.00..1.04 rows=4 width=72)<br /> -> Seq Scan on t2 (cost=0.00..1.02rows=2 width=150)<br /><br /> I am using postgresql 8.5.1, I am wondering is there is any patch that i canrun to enable it to put the actual table names instead of inner/outer. Should i post this to the hackers mailing list?<br/><br /> Thanks a lot.<br /></div><br /><br /><br /><hr id="stopSpelling" />> To: ehabgalal123@hotmail.com<br/>> CC: pgsql-sql@postgresql.org<br />> Subject: Re: [SQL] omitting redundant join predicate<br />> Date: Sun, 4 Nov 2007 11:35:36 -0500<br />> From: tgl@sss.pgh.pa.us<br />> <br />> Ehab Galal<ehabgalal123@hotmail.com> writes:<br />> > explain select * <br />> > from t1, t2, t3 <br />>> where t1.f <= t2.f<br />> > and t2.f <= t3.f<br />> > and t1.f <= t3.f;<br />> <br />>> I was wondering if there is a<br />> > way to omit the redundant join predicate.<br />> <br />> You'renot being very clear here. Do you mean will you get the same<br />> answer if you omit "t1.f <= t3.f"? Yes, ofcourse (ignoring possibly<br />> different output ordering). Do you mean you think the system should<br />> discardit as redundant? I disagree --- the more join clauses the<br />> better, as a rule. Do you mean that the EXPLAINoutput looks like<br />> the same comparison is being applied twice? It isn't --- in a more<br />> modern PGrelease the output looks like this:<br />> <br />> QUERY PLAN <br />> ------------------------------------------------------------------<br/>> Nested Loop (cost=33.54..81794021.44 rows=362975624width=12)<br />> Join Filter: ((t1.f <= t2.f) AND (t2.f <= t3.f))<br />> -> Nested Loop (cost=0.00..124472.40rows=1526533 width=8)<br />> Join Filter: (t1.f <= t3.f)<br />> -> Seq Scan on t1 (cost=0.00..31.40rows=2140 width=4)<br />> -> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=4)<br />> -> Materialize(cost=33.54..54.94 rows=2140 width=4)<br />> -> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)<br />>(8 rows)<br />> <br />> This is of course the stupidest possible join plan, but it's hard to do<br />> muchbetter --- both hash and merge joins work only on equality<br />> conditions. You can do a bit better with an indexon t2.f:<br />> <br />> QUERY PLAN <br />> ----------------------------------------------------------------------<br/>> Nested Loop (cost=0.00..13222230.60 rows=362975624width=12)<br />> -> Nested Loop (cost=0.00..124472.40 rows=1526533 width=8)<br />> Join Filter: (t1.f<= t3.f)<br />> -> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)<br />> -> Seq Scan on t3 (cost=0.00..31.40rows=2140 width=4)<br />> -> Index Scan using t2i on t2 (cost=0.00..5.01 rows=238 width=4)<br />>Index Cond: ((t1.f <= t2.f) AND (t2.f <= t3.f))<br />> (7 rows)<br />> <br />> regards, tom lane<br/><br /><hr />Help yourself to FREE treats served up daily at the Messenger Café. <a href="http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline"target="_new">Stop by today!</a>
В списке pgsql-sql по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: Returning the total number of rows as a separate column when using limit
Следующее
От: Gregory StarkДата:
Сообщение: Re: Returning the total number of rows as a separate column when using limit