Received: from localhost (unknown [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 19C509F9C33
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 31 Aug 2007 11:18:17 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 96101-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 31 Aug 2007 11:18:00 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.243])
	by postgresql.org (Postfix) with ESMTP id 1DC6F9F9AEE
	for <pgsql-sql@postgresql.org>; Fri, 31 Aug 2007 11:18:11 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so162859ana
	for <pgsql-sql@postgresql.org>; Fri, 31 Aug 2007 07:18:10 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:reply-to:user-agent:mime-version:to:subject:content-type;
	b=LlvAW7bD0OIDZhwgYpaMZ1+JvtZz/NMXbiqcQL/f6tG7I3dErJ9Zsl8icjFvAnzSdIGFFFNe5WWVWnssnCJVpdM8s8OGGwgq96I4cQooC04A1luwHXlLdq5muQDTpl8pchAJrFdfI9ubd7W6SYHudCqAgj3ENfnCGEWA9l45780=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:reply-to:user-agent:mime-version:to:subject:content-type;
	b=POuZ2GYLGaQxkhATclvrwAuxAKbln/M7YcLRmdrm5+gAMgS8w1FeTFf0rOm3qXD3TcOSnsnae0oWHr/r0B4lWaZTU9JjK1civfLnDjT6vXEuNsbZfI7xMJgQjIurCPoOK72pIDmNvbq1TUOlcbwpn3m5ZpOm7WNuBNFTyVlzrP4=
Received: by 10.100.94.3 with SMTP id r3mr1564095anb.1188569889828;
	Fri, 31 Aug 2007 07:18:09 -0700 (PDT)
Received: from ?10.0.0.75? ( [213.170.101.138])
	by mx.google.com with ESMTPS id l22sm825984uga.2007.08.31.07.18.01
	(version=SSLv3 cipher=RC4-MD5); Fri, 31 Aug 2007 07:18:02 -0700 (PDT)
Message-ID: <46D82318.3070700@gmail.com>
Date: Fri, 31 Aug 2007 18:18:00 +0400
From: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
Reply-To: Aleksandr Vinokurov <aleksandr.vin@gmail.com>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Incomprehensible dogged sort in Merge Join
Content-Type: multipart/mixed; boundary="------------060705000309000802050406"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/259
X-Sequence-Number: 29197

This is a multi-part message in MIME format.
--------------060705000309000802050406
Content-Type: text/plain; charset=windows-1251; format=flowed
Content-Transfer-Encoding: 7bit



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.

--------------060705000309000802050406
Content-Type: text/plain;
 name="plan.txt"
Content-Transfer-Encoding: base64
Content-Disposition: inline;
 filename="plan.txt"

//4gAE0AZQByAGcAZQAgAEwAZQBmAHQAIABKAG8AaQBuACAAIAAoAGMAbwBzAHQAPQAyADAA
NwA2ADQANgAuADAAMAAuAC4AMgAxADMAOAA2ADQALgAxADIAIAByAG8AdwBzAD0AMwA0ADcA
OAA1ADEAIAB3AGkAZAB0AGgAPQA2ADIAKQAgACgAYQBjAHQAdQBhAGwAIAB0AGkAbQBlAD0A
MwAwADkAMgAyAC4AMwA2ADYALgAuADMAMgAxADYANgAuADUAMQA4ACAAcgBvAHcAcwA9ADEA
MwA5ADAAOAAgAGwAbwBvAHAAcwA9ADEAKQANAAoAIAAgACAATQBlAHIAZwBlACAAQwBvAG4A
ZAA6ACAAKAAiAG8AdQB0AGUAcgAiAC4AbgBhAG0AZQAgAD0AIAAiAGkAbgBuAGUAcgAiAC4A
bgBhAG0AZQApAA0ACgAgACAAIAAtAD4AIAAgAFMAbwByAHQAIAAgACgAYwBvAHMAdAA9ADYA
LgA3ADEALgAuADcALgAwADMAIAByAG8AdwBzAD0AMQAyADcAIAB3AGkAZAB0AGgAPQAzADAA
KQAgACgAYQBjAHQAdQBhAGwAIAB0AGkAbQBlAD0AMQAuADEAMQA3AC4ALgAxAC4AMgAwADcA
IAByAG8AdwBzAD0AMQAwADAAIABsAG8AbwBwAHMAPQAxACkADQAKACAAIAAgACAAIAAgACAA
IAAgAFMAbwByAHQAIABLAGUAeQA6ACAAbABvAGcALgBuAGEAbQBlAA0ACgAgACAAIAAgACAA
IAAgACAAIAAtAD4AIAAgAFMAZQBxACAAUwBjAGEAbgAgAG8AbgAgAGwAbwBnAF8AZQB4AGEA
bQBwAGwAZQBfADMAIABsAG8AZwAgACAAKABjAG8AcwB0AD0AMAAuADAAMAAuAC4AMgAuADIA
NwAgAHIAbwB3AHMAPQAxADIANwAgAHcAaQBkAHQAaAA9ADMAMAApACAAKABhAGMAdAB1AGEA
bAAgAHQAaQBtAGUAPQAwAC4AMAA1ADMALgAuADAALgAxADcANwAgAHIAbwB3AHMAPQAxADAA
MAAgAGwAbwBvAHAAcwA9ADEAKQANAAoAIAAgACAALQA+ACAAIABTAG8AcgB0ACAAIAAoAGMA
bwBzAHQAPQAyADAANwA2ADMAOQAuADIAOQAuAC4AMgAwADkAMAAwADgALgA3ADgAIAByAG8A
dwBzAD0ANQA0ADcANwA5ADYAIAB3AGkAZAB0AGgAPQAzADIAKQAgACgAYQBjAHQAdQBhAGwA
IAB0AGkAbQBlAD0AMwAwADkAMgAxAC4AMQA3ADEALgAuADMAMQA0ADYANwAuADEAMQA3ACAA
cgBvAHcAcwA9ADQANAAyADgANAA4ACAAbABvAG8AcABzAD0AMQApAA0ACgAgACAAIAAgACAA
IAAgACAAIABTAG8AcgB0ACAASwBlAHkAOgAgAHUAaAAuAG4AYQBtAGUADQAKACAAIAAgACAA
IAAgACAAIAAgAC0APgAgACAAUwB1AGIAcQB1AGUAcgB5ACAAUwBjAGEAbgAgAHUAaAAgACAA
KABjAG8AcwB0AD0AMQAxADEANAA0ADcALgA5ADAALgAuADEAMQA4ADIAOQA1AC4AMwA1ACAA
cgBvAHcAcwA9ADUANAA3ADcAOQA2ACAAdwBpAGQAdABoAD0AMwAyACkAIAAoAGEAYwB0AHUA
YQBsACAAdABpAG0AZQA9ADEAOQAyADcANwAuADkANgAzAC4ALgAyADEANQA5ADUALgA4ADcA
NAAgAHIAbwB3AHMAPQA1ADQANwA3ADkANgAgAGwAbwBvAHAAcwA9ADEAKQANAAoAIAAgACAA
IAAgACAAIAAgACAAIAAgACAAIAAgACAALQA+ACAAIABTAG8AcgB0ACAAIAAoAGMAbwBzAHQA
PQAxADEAMQA0ADQANwAuADkAMAAuAC4AMQAxADIAOAAxADcALgAzADkAIAByAG8AdwBzAD0A
NQA0ADcANwA5ADYAIAB3AGkAZAB0AGgAPQAzADIAKQAgACgAYQBjAHQAdQBhAGwAIAB0AGkA
bQBlAD0AMQA5ADIANwA3AC4AOQAwADgALgAuADIAMAAxADAANAAuADUANgA4ACAAcgBvAHcA
cwA9ADUANAA3ADcAOQA2ACAAbABvAG8AcABzAD0AMQApAA0ACgAgACAAIAAgACAAIAAgACAA
IAAgACAAIAAgACAAIAAgACAAIAAgACAAIABTAG8AcgB0ACAASwBlAHkAOgAgAG4AYQBtAGUA
DQAKACAAIAAgACAAIAAgACAAIAAgACAAIAAgACAAIAAgACAAIAAgACAAIAAgAC0APgAgACAA
UwBlAHEAIABTAGMAYQBuACAAbwBuACAAdQBzAGUAcgBfAGgAaQBzAHQAbwByAHkAIAAgACgA
YwBvAHMAdAA9ADAALgAwADAALgAuADIAMgAxADAAMwAuADkANgAgAHIAbwB3AHMAPQA1ADQA
NwA3ADkANgAgAHcAaQBkAHQAaAA9ADMAMgApACAAKABhAGMAdAB1AGEAbAAgAHQAaQBtAGUA
PQAwAC4AMAA1ADEALgAuADEANAA3ADQALgAxADQAMwAgAHIAbwB3AHMAPQA1ADQANwA3ADkA
NgAgAGwAbwBvAHAAcwA9ADEAKQA=
--------------060705000309000802050406--