Incomprehensible dogged sort in Merge Join
От | Aleksandr Vinokurov |
---|---|
Тема | Incomprehensible dogged sort in Merge Join |
Дата | |
Msg-id | 46D82318.3070700@gmail.com обсуждение исходный текст |
Ответы |
Re: Incomprehensible dogged sort in Merge Join
Re: Incomprehensible dogged sort in Merge Join |
Список | 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. ��
В списке pgsql-sql по дате отправления: