Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D2E6D475FB7
	for <pgsql-sql@postgresql.org>; Tue, 22 Oct 2002 12:13:06 -0400 (EDT)
Received: from mailstore-1.mail.knowledge.com (frida.london.knowtion.net
	[213.170.2.69]) by postgresql.org (Postfix) with ESMTP id EA901475ADD
	for <pgsql-sql@postgresql.org>; Tue, 22 Oct 2002 12:13:05 -0400 (EDT)
Received: from [213.155.153.61] (helo=petergalbavydes)
	by mailstore-1.mail.knowledge.com with asmtp (Exim 3.33 #1)
	id 1841eZ-0002Ut-00
	for pgsql-sql@postgresql.org; Tue, 22 Oct 2002 17:13:11 +0100
Message-ID: <054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com>
From: "Peter Galbavy" <peter.galbavy@knowtion.net>
To: <pgsql-sql@postgresql.org>
Subject: 'fake' join and performance ?
Date: Tue, 22 Oct 2002 17:10:31 +0100
Organization: Knowtion Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/279
X-Sequence-Number: 9996

OK, I am now confused; postgresql 7.3beta2 on OpenBSD:


photos=# select * from metadata WHERE name = 'Make' and value = 'Canon'
limit 10;

*bang*, 10 values, sub second response.

photos=# select * from metadata m, images i WHERE m.name = 'Make' and
m.value = 'Canon' limit 10;

*yawn* - see you later...

Now, 'images' is a new and currently empty table that I intend to do a join
on later, but I started building a query to test my join'ing skills and
found this;

Explain'ing for both:

photos=# explain select * from metadata WHERE name = 'Make' and value =
'Canon' limit 10;
                                         QUERY PLAN
----------------------------------------------------------------------------
----------------
 Limit  (cost=0.00..27711.98 rows=6 width=92)
   ->  Index Scan using metadata_index_2 on metadata  (cost=0.00..31072.94
rows=7 width=92)
         Index Cond: (name = 'Make'::text)
         Filter: (value = 'Canon'::text)
(4 rows)

photos=# explain select * from metadata m, images i WHERE m.name = 'Make'
and m.value = 'Canon' limit 10;
                                             QUERY PLAN
----------------------------------------------------------------------------
------------------------
 Limit  (cost=0.00..27712.04 rows=6 width=816)
   ->  Nested Loop  (cost=0.00..31073.00 rows=7 width=816)
         ->  Index Scan using metadata_index_2 on metadata m
(cost=0.00..31072.94 rows=7 width=92)
               Index Cond: (name = 'Make'::text)
               Filter: (value = 'Canon'::text)
         ->  Seq Scan on images i  (cost=0.00..0.00 rows=1 width=724)
(6 rows)



Er, what's that nested loop. I *know* I have shot myself in the foot
somehow, but my initial reaction was that the optimiser should just make the
'fake' (i.e. unreferenced) reference to another table go away...

peter