X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 4B0CE9FB1D2
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Sep 2006 09:29:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 54356-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 19 Sep 2006 09:29:43 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from my.endian.it (unknown [62.146.87.34])
	by postgresql.org (Postfix) with ESMTP id 556309FB1C8
	for <pgsql-sql@postgresql.org>; Tue, 19 Sep 2006 09:29:41 -0300 (ADT)
Received: from ultra.home.lan
	(host27-10-dynamic.0-87-r.retail.telecomitalia.it [87.0.10.27])
	(authenticated (0 bits))
	by my.endian.it (8.11.6/8.11.6) with ESMTP id k8JCdOA26233;
	Tue, 19 Sep 2006 14:39:24 +0200
Subject: Re: inner join is much faster! is that right?
From: Chris Mair <chrisnospam@1006.org>
To: James Im <im-james@hotmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl>
References: <BAY7-F18EF4F5BF559DFDA8D9C9F96220@phx.gbl>
Content-Type: text/plain
Date: Tue, 19 Sep 2006 14:29:37 +0200
Message-Id: <1158668977.15510.19.camel@ultra.home.lan>
Mime-Version: 1.0
X-Mailer: Evolution 2.6.3 (2.6.3-1.fc5.5) 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.649 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, PLING_QUERY
X-Spam-Level: 
X-Archive-Number: 200609/122
X-Sequence-Number: 26333


> I really thought that Postgresql would rewrite a query from
> 
> select *
> from file a, file_tag b, tag c
> where a.id_file=b.id_file and b.id_tag=c.id_tag and a.id_file=10000001000000
> 
> to something like:
> 
> select *
> from (file a inner join file_tag b on (a.id_file=b.id_file)) inner join
> tag c on (b.id_tag=c.id_tag)
> where a.id_file=10000001000000

These shouldn't be other than two ways to express the same (inner)
join.

Your timings seem to suggest that in the first case PG computes
the whole join between 3 tables and only then applies the filter
in a.

Can you send the outputs of "explain <query>" for these two and
let us know what version of PG this is?

Bye, Chris.


-- 

Chris Mair
http://www.1006.org