Thread: Incomprehensible dogged sort in Merge Join
Hello all,
Trying to get an extra time savings in my query, I stopped at an unusual
doggedness of the planner.
Here is the query:
>---------------------------------<cut>--------------------------------<
select *
from (
select *
from "user_history"
order by name
) as uh
right join log_example_3 as log
on log.name = uh.name
>---------------------------------<cut>--------------------------------<
And that is its plan (attached one is the same, but with costs):
>---------------------------------<cut>--------------------------------<
Merge Left Join
Merge Cond: ("outer".name = "inner".name)
-> Sort
Sort Key: log.name
-> Seq Scan on log_example_3 log
-> Sort
Sort Key: uh.name
-> Subquery Scan uh
-> Sort
Sort Key: name
-> Seq Scan on user_history
>---------------------------------<cut>--------------------------------<
The strange thing is that planner can combine two sorts by uh.name key
in one, but it seems it can't see this.
May be this can be recorded as a needed feature for future releases?
Here is a code for two tables that I have in the query:
>---------------------------------<cut>--------------------------------<
create table user_history (
rec_id SERIAL not null,
date TIMESTAMP not null,
action INT2 not null,
uid INT4 not null,
name CHAR(10) null default NULL,
constraint PK_USER_HISTORY primary key (rec_id),
constraint AK_DATE_USER_HIS unique (date)
);
create table log_example_3 (
rec_id integer not null,
date timestamp not null,
uid integer not null,
name char(10) not null,
constraint PK_log_example_3 primary key (rec_id)
);
>---------------------------------<cut>--------------------------------<
With best regards to all of you,
Aleksandr.
��
Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes:
> Trying to get an extra time savings in my query, I stopped at an unusual
> doggedness of the planner.
> Merge Left Join
> Merge Cond: ("outer".name = "inner".name)
> -> Sort
> Sort Key: log.name
> -> Seq Scan on log_example_3 log
> -> Sort
> Sort Key: uh.name
> -> Subquery Scan uh
> -> Sort
> Sort Key: name
> -> Seq Scan on user_history
> The strange thing is that planner can combine two sorts by uh.name key
> in one, but it seems it can't see this.
> May be this can be recorded as a needed feature for future releases?
When criticizing planner deficiencies, it's considered polite to use
something that's less than two major releases back ;-)
CVS HEAD gets this right, although I need to go look at why it's
sticking a Materialize in there:
regression=# explain select * from (select * from tenk1 order by twothousand) uh right join tenk1 log on log.thousand =
uh.twothousand; QUERY PLAN
-----------------------------------------------------------------------------------Merge Right Join
(cost=4575.77..6225.77rows=100000 width=488) Merge Cond: (tenk1.twothousand = log.thousand) -> Sort
(cost=2287.89..2312.89rows=10000 width=244) Sort Key: tenk1.twothousand -> Seq Scan on tenk1
(cost=0.00..458.00rows=10000 width=244) -> Materialize (cost=2287.89..2412.89 rows=10000 width=244) -> Sort
(cost=2287.89..2312.89rows=10000 width=244) Sort Key: log.thousand -> Seq Scan on tenk1 log
(cost=0.00..458.00rows=10000 width=244)
(9 rows)
[ pokes at it a bit more... ] 8.1 and 8.2 get it right for a plain
join, but not for an outer join. Strange, I need to check that too.
regards, tom lane
"Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes:
> And that is its plan (attached one is the same, but with costs):
>
>>---------------------------------<cut>--------------------------------<
> Merge Left Join
> Merge Cond: ("outer".name = "inner".name)
> -> Sort
> Sort Key: log.name
> -> Seq Scan on log_example_3 log
> -> Sort
> Sort Key: uh.name
> -> Subquery Scan uh
> -> Sort
> Sort Key: name
> -> Seq Scan on user_history
>>---------------------------------<cut>--------------------------------<
What version are you running with?
Incidentally with CVS HEAD I see it working:
postgres=# explain select * from (select * from a order by i) as a right join b on a.i = b.i ;
QUERYPLAN
-----------------------------------------------------------------Merge Right Join (cost=299.56..675.13 rows=22898
width=16) Merge Cond: (public.a.i = b.i) -> Sort (cost=149.78..155.13 rows=2140 width=8) Sort Key: public.a.i
-> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8) -> Sort (cost=149.78..155.13 rows=2140 width=8)
SortKey: b.i -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
(8 rows)
> With best regards to all of you,
> Aleksandr.
> ÿþ
Tom Lane wrote:
> Aleksandr Vinokurov <aleksandr.vin@gmail.com> writes:
>> Trying to get an extra time savings in my query, I stopped at an unusual
>> doggedness of the planner.
>
>> Merge Left Join
>> Merge Cond: ("outer".name = "inner".name)
>> -> Sort
>> Sort Key: log.name
>> -> Seq Scan on log_example_3 log
>> -> Sort
>> Sort Key: uh.name
>> -> Subquery Scan uh
>> -> Sort
>> Sort Key: name
>> -> Seq Scan on user_history
>
>> The strange thing is that planner can combine two sorts by uh.name key
>> in one, but it seems it can't see this.
>
>> May be this can be recorded as a needed feature for future releases?
>
> When criticizing planner deficiencies, it's considered polite to use
> something that's less than two major releases back ;-)
Sorry, it was blown out from my head at the end of composing: my version
is 8.0.1, not so old, IMHO.
>
> CVS HEAD gets this right, although I need to go look at why it's
> sticking a Materialize in there:
>
> regression=# explain select * from (select * from tenk1 order by twothousand) uh right join tenk1 log on log.thousand
=uh.twothousand;
> QUERY PLAN
> -----------------------------------------------------------------------------------
> Merge Right Join (cost=4575.77..6225.77 rows=100000 width=488)
> Merge Cond: (tenk1.twothousand = log.thousand)
> -> Sort (cost=2287.89..2312.89 rows=10000 width=244)
> Sort Key: tenk1.twothousand
> -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
> -> Materialize (cost=2287.89..2412.89 rows=10000 width=244)
> -> Sort (cost=2287.89..2312.89 rows=10000 width=244)
> Sort Key: log.thousand
> -> Seq Scan on tenk1 log (cost=0.00..458.00 rows=10000 width=244)
> (9 rows)
>
> [ pokes at it a bit more... ] 8.1 and 8.2 get it right for a plain
> join, but not for an outer join. Strange, I need to check that too.
>
> regards, tom lane
>
Best wishes, Aleksandr.
Gregory Stark wrote:
> "Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes:
>
>> And that is its plan (attached one is the same, but with costs):
>>
>>> ---------------------------------<cut>--------------------------------<
>> Merge Left Join
>> Merge Cond: ("outer".name = "inner".name)
>> -> Sort
>> Sort Key: log.name
>> -> Seq Scan on log_example_3 log
>> -> Sort
>> Sort Key: uh.name
>> -> Subquery Scan uh
>> -> Sort
>> Sort Key: name
>> -> Seq Scan on user_history
>>> ---------------------------------<cut>--------------------------------<
>
> What version are you running with?
8.0.1, sorry for missing.
>
> Incidentally with CVS HEAD I see it working:
>
> postgres=# explain select * from (select * from a order by i) as a right join b on a.i = b.i ;
> QUERY PLAN
> -----------------------------------------------------------------
> Merge Right Join (cost=299.56..675.13 rows=22898 width=16)
> Merge Cond: (public.a.i = b.i)
> -> Sort (cost=149.78..155.13 rows=2140 width=8)
> Sort Key: public.a.i
> -> Seq Scan on a (cost=0.00..31.40 rows=2140 width=8)
> -> Sort (cost=149.78..155.13 rows=2140 width=8)
> Sort Key: b.i
> -> Seq Scan on b (cost=0.00..31.40 rows=2140 width=8)
> (8 rows)
>
>
>
>> With best regards to all of you,
>> Aleksandr.
>> ÿþ
>
> gesundheit.
>
>
Best wishes,
Aleksandr.
Aleksandr Vinokurov escribió: > Tom Lane wrote: >> When criticizing planner deficiencies, it's considered polite to use >> something that's less than two major releases back ;-) > > Sorry, it was blown out from my head at the end of composing: my version is > 8.0.1, not so old, IMHO. It _is_ quite old, yes. Try 8.2 at the very least, but in some cases you're going to get "current CVS HEAD does it better", so if you want to suggest improvements to the planner you should be really looking into that. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "No reniegues de lo que alguna vez creíste"
Alvaro Herrera wrote: > Aleksandr Vinokurov escribió: >> Tom Lane wrote: > >>> When criticizing planner deficiencies, it's considered polite to use >>> something that's less than two major releases back ;-) >> Sorry, it was blown out from my head at the end of composing: my version is >> 8.0.1, not so old, IMHO. > > It _is_ quite old, yes. Try 8.2 at the very least, but in some cases > you're going to get "current CVS HEAD does it better", so if you want to > suggest improvements to the planner you should be really looking into > that. > I understood my fault :) Guys, please forgive my dodge. Aleksandr.
"Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes: > Gregory Stark wrote: >> >> What version are you running with? > > 8.0.1, sorry for missing. There have been 12 bug-fix releases since then on the 8.0 branch including updating timezones to reflect the new daylight saving time rules for North America, various crashes, data corruption, and security bugs. There is no reason not to upgrade to the current latest 8.0 branch release which is 8.0.13. Separately, you may not want to upgrade to 8.2 now for operational reasons but reporting "wishlist" bugs against a version that's two major releases old is almost certainly going to be pointless. Postgres is moving ahead so fast that the chances that your wish is granted already in more recent releases are very high. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
stark@enterprisedb.com (Gregory Stark) writes: > "Aleksandr Vinokurov" <aleksandr.vin@gmail.com> writes: > >> Gregory Stark wrote: >>> >>> What version are you running with? >> >> 8.0.1, sorry for missing. > > There have been 12 bug-fix releases since then on the 8.0 branch including > updating timezones to reflect the new daylight saving time rules for North > America, various crashes, data corruption, and security bugs. There is no > reason not to upgrade to the current latest 8.0 branch release which is > 8.0.13. > > Separately, you may not want to upgrade to 8.2 now for operational > reasons but reporting "wishlist" bugs against a version that's two > major releases old is almost certainly going to be > pointless. Postgres is moving ahead so fast that the chances that > your wish is granted already in more recent releases are very high. Further, the chances of your wishes being granted on the 8.0 branch are somewhere between zero and none, and the changes since 8.0 are major enough that comparing against 8.0 for the purposes of discussion just generally isn't useful. -- (reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc")) http://linuxfinances.info/info/oses.html Rule of Scarcity "If it's not allocated by a market, then it's more expensive than money." -- Mark Miller