From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Galbavy <peter.galbavy@knowtion.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: 'fake' join and performance ?
Date: Tue, 22 Oct 2002 13:39:25 -0400
Message-ID: <24033.1035308365@sss.pgh.pa.us> (raw)
In-Reply-To: <054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com>
References: <054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com>
"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
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: tgl@sss.pgh.pa.us, peter.galbavy@knowtion.net
Subject: Re: 'fake' join and performance ?
In-Reply-To: <24033.1035308365@sss.pgh.pa.us>
* 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