I'm going back through my first application using postgres and am trying to improve the performance of the various
queriesand came across a few things that are surprising to me and suspect that I'm missing some key concept.
So lets say you hav two tables, foo and bar, which share the same primary key (say bar contains supplemental data about
foowhich isn't usually needed). Both tables have indices for their primary key and the database has had VACUUM and
VACUUMANALYZE run recently (in this case right before the following queries).
(explain's total estimate in parens)
Case 1:
Select * from foo where id=10; (0..1.55)
select * from bar where id=10; (0..1.52)
case 2:
select * from foo,bar where foo.id=bar.id and foo.id=10; (0..3.50)
case 3:
select * from foo,bar where foo.id=10 and bar.id=10; (0..3.08)
Questions:
1. Is there a reason why case 2 and 3 are different?
2. If using a scripting tool is there any reason not to use queries like style 1, as it looks like the join is a
definateperformance penality even when indexed and the scripting tool gets rid of the extra typing. Even foreign key
lookupscould be rewritten as case 1 with some scrpting tool and avoid the joins. Before I thought that joins, while a
performancelimiter, were still more effecient thant multipl queries, but it appears I was wrong. What good are they
then?
3. My db is small right now ~80 entries in the tables, but I would suspect that the differences in performance are
evengreater with larger tables?
Thank you.
Eric
--------------------------------------------------------------------
Mail2Web - Check your email from the web at
http://www.mail2web.com/ .