To: "Peter Galbavy" <peter.galbavy@knowtion.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: 'fake' join and performance ? 
In-reply-to: <054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com> 
References: <054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com>
Comments: In-reply-to "Peter Galbavy" <peter.galbavy@knowtion.net>
	message dated "Tue, 22 Oct 2002 17:10:31 +0100"
Date: Tue, 22 Oct 2002 13:39:25 -0400
Message-ID: <24033.1035308365@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Peter Galbavy" <peter.galbavy@knowtion.net> writes:
> photos=# select * from metadata m, images i WHERE m.name = 'Make' and
> m.value = 'Canon' limit 10;

> Er, what's that nested loop. I *know* I have shot myself in the foot
> somehow,

Yeah, you didn't restrict the reference to images at all.

> but my initial reaction was that the optimiser should just make the
> 'fake' (i.e. unreferenced) reference to another table go away...

That would be in violation of the SQL spec.  The query is defined to
return each join row from the cross product of the FROM tables that
meets the condition of the WHERE clause.  As you wrote the query, each
metadata row that meets the WHERE clause will be returned exactly as
many times as there are rows in the images table.  There is no such
thing as an "unreferenced" FROM entry as far as SQL is concerned.

			regards, tom lane