'fake' join and performance ?

From: "Peter Galbavy" <peter(dot)galbavy(at)knowtion(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: 'fake' join and performance ?
Date: 2002-10-22 16:10:31
Message-ID: 054501c279e5$8bfcfe00$4528a8c0@cblan.mblox.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2002-10-22 16:16:09 Re: Can I search for an array in csf?
Previous Message Stephan Szabo 2002-10-22 16:09:17 Re: using deferred on PK/FK relationships