[SQL] Incomprehensible dogged sort in Merge Join

Fri, 31 Aug 2007 07:20:17 -0700 


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.

ÿþ Merge Left Join  
(cost=207646.00..213864.12 rows=347851 
width=62) (actual 
time=30922.366..32166.518 rows=13908 
loops=1)

   Merge Cond: ("outer".name = 
"inner".name)

   ->  Sort  (cost=6.71..7.03 rows=127 
width=30) (actual time=1.117..1.207 
rows=100 loops=1)

         Sort Key: log.name

         ->  Seq Scan on log_example_3 
log  (cost=0.00..2.27 rows=127 
width=30) (actual time=0.053..0.177 
rows=100 loops=1)

   ->  Sort  (cost=207639.29..209008.78 
rows=547796 width=32) (actual 
time=30921.171..31467.117 rows=442848 
loops=1)

         Sort Key: uh.name

         ->  Subquery Scan uh  
(cost=111447.90..118295.35 rows=547796 
width=32) (actual 
time=19277.963..21595.874 rows=547796 
loops=1)

               ->  Sort  
(cost=111447.90..112817.39 rows=547796 
width=32) (actual 
time=19277.908..20104.568 rows=547796 
loops=1)

                     Sort Key: name

                     ->  Seq Scan on 
user_history  (cost=0.00..22103.96 
rows=547796 width=32) (actual 
time=0.051..1474.143 rows=547796 
loops=1)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match






















					Reply via email to