pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIncomprehensible dogged sort in Merge Join
9+ messages / 5 participants
[nested] [flat]
* Incomprehensible dogged sort in Merge Join
@ 2007-08-31 14:18 Aleksandr Vinokurov <aleksandr.vin@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Aleksandr Vinokurov @ 2007-08-31 14:18 UTC (permalink / raw)
To: pgsql-sql
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.
Attachments:
plan.txt (text/plain, 2.0K) download
ÿþ M e r g e L e f t J o i n ( c o s t = 2 0 7 6 4 6 . 0 0 . . 2 1 3 8 6 4 . 1 2 r o w s = 3 4 7 8 5 1 w i d t h = 6 2 ) ( a c t u a l t i m e = 3 0 9 2 2 . 3 6 6 . . 3 2 1 6 6 . 5 1 8 r o w s = 1 3 9 0 8 l o o p s = 1 )
M e r g e C o n d : ( " o u t e r " . n a m e = " i n n e r " . n a m e )
- > S o r t ( c o s t = 6 . 7 1 . . 7 . 0 3 r o w s = 1 2 7 w i d t h = 3 0 ) ( a c t u a l t i m e = 1 . 1 1 7 . . 1 . 2 0 7 r o w s = 1 0 0 l o o p s = 1 )
S o r t K e y : l o g . n a m e
- > S e q S c a n o n l o g _ e x a m p l e _ 3 l o g ( c o s t = 0 . 0 0 . . 2 . 2 7 r o w s = 1 2 7 w i d t h = 3 0 ) ( a c t u a l t i m e = 0 . 0 5 3 . . 0 . 1 7 7 r o w s = 1 0 0 l o o p s = 1 )
- > S o r t ( c o s t = 2 0 7 6 3 9 . 2 9 . . 2 0 9 0 0 8 . 7 8 r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l t i m e = 3 0 9 2 1 . 1 7 1 . . 3 1 4 6 7 . 1 1 7 r o w s = 4 4 2 8 4 8 l o o p s = 1 )
S o r t K e y : u h . n a m e
- > S u b q u e r y S c a n u h ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 8 2 9 5 . 3 5 r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l t i m e = 1 9 2 7 7 . 9 6 3 . . 2 1 5 9 5 . 8 7 4 r o w s = 5 4 7 7 9 6 l o o p s = 1 )
- > S o r t ( c o s t = 1 1 1 4 4 7 . 9 0 . . 1 1 2 8 1 7 . 3 9 r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l t i m e = 1 9 2 7 7 . 9 0 8 . . 2 0 1 0 4 . 5 6 8 r o w s = 5 4 7 7 9 6 l o o p s = 1 )
S o r t K e y : n a m e
- > S e q S c a n o n u s e r _ h i s t o r y ( c o s t = 0 . 0 0 . . 2 2 1 0 3 . 9 6 r o w s = 5 4 7 7 9 6 w i d t h = 3 2 ) ( a c t u a l t i m e = 0 . 0 5 1 . . 1 4 7 4 . 1 4 3 r o w s = 5 4 7 7 9 6 l o o p s = 1 )
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 14:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2007-08-31 14:43 UTC (permalink / raw)
To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>; +Cc: pgsql-sql
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.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 14:45 Gregory Stark <stark@enterprisedb.com>
parent: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Gregory Stark @ 2007-08-31 14:45 UTC (permalink / raw)
To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>; +Cc: pgsql-sql
"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 ;
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 14:53 Aleksandr Vinokurov <aleksandr.vin@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Aleksandr Vinokurov @ 2007-08-31 14:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 14:56 Aleksandr Vinokurov <aleksandr.vin@gmail.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 9+ messages in thread
From: Aleksandr Vinokurov @ 2007-08-31 14:56 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 15:03 Alvaro Herrera <alvherre@commandprompt.com>
parent: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Alvaro Herrera @ 2007-08-31 15:03 UTC (permalink / raw)
To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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"
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 15:08 Aleksandr Vinokurov <aleksandr.vin@gmail.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 9+ messages in thread
From: Aleksandr Vinokurov @ 2007-08-31 15:08 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 15:24 Gregory Stark <stark@enterprisedb.com>
parent: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Gregory Stark @ 2007-08-31 15:24 UTC (permalink / raw)
To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Incomprehensible dogged sort in Merge Join
@ 2007-08-31 17:49 Chris Browne <cbbrowne@acm.org>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 0 replies; 9+ messages in thread
From: Chris Browne @ 2007-08-31 17:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-08-31 17:49 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-31 14:18 Incomprehensible dogged sort in Merge Join Aleksandr Vinokurov <aleksandr.vin@gmail.com>
2007-08-31 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-31 14:53 ` Aleksandr Vinokurov <aleksandr.vin@gmail.com>
2007-08-31 15:03 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-31 15:08 ` Aleksandr Vinokurov <aleksandr.vin@gmail.com>
2007-08-31 14:45 ` Gregory Stark <stark@enterprisedb.com>
2007-08-31 14:56 ` Aleksandr Vinokurov <aleksandr.vin@gmail.com>
2007-08-31 15:24 ` Gregory Stark <stark@enterprisedb.com>
2007-08-31 17:49 ` Chris Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox