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 |
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 |