Incomprehensible dogged sort in Merge Join

From: Aleksandr Vinokurov <aleksandr(dot)vin(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Incomprehensible dogged sort in Merge Join
Date: 2007-08-31 14:18:00
Message-ID: 46D82318.3070700@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Attachment Content-Type Size
plan.txt text/plain 2.0 KB

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2007-08-31 14:43:39 Re: Incomprehensible dogged sort in Merge Join
Previous Message AlphaÔmega 2007-08-31 02:06:02 Software CASE - PostgreSQL