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
