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
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 |