pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Incomprehensible dogged sort in Merge Join
Date: Fri, 31 Aug 2007 18:18:00 +0400
Message-ID: <46D82318.3070700@gmail.com> (raw)



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
ÿþ 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)

view thread (9+ messages)  latest in thread

Message-ID: <46D82318.3070700@gmail.com>
Permalink:  ../46D82318.3070700@gmail.com/
Also on:    postgresql.org/message-id/46D82318.3070700@gmail.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: aleksandr.vin@gmail.com
  Subject: Re: Incomprehensible dogged sort in Merge Join
  In-Reply-To: <46D82318.3070700@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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