Skip site navigation (1) Skip section navigation (2)

Understanding query tuning

From: "ericnielsen(at)earthlink(dot)net" <ericnielsen(at)earthlink(dot)net>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Understanding query tuning
Date: 2001-06-29 17:22:59
Message-ID: 200106291322840.SM00788@m2w031 (view raw or flat)
Thread:
Lists: pgsql-novice
I'm going back through my first application using postgres and am trying to improve the performance of the various queries and 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 foo which isn't usually needed).  Both tables have indices for their primary key and the database has had VACUUM and VACUUM ANALYZE 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 definate performance penality even when indexed and the scripting tool gets rid of the extra typing.  Even foreign key lookups could be rewritten as case 1 with some scrpting tool and avoid the joins.  Before I thought that joins, while a performance limiter, 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 even greater with larger tables?

Thank you.

Eric


--------------------------------------------------------------------
Mail2Web - Check your email from the web at
http://www.mail2web.com/ .


pgsql-novice by date

Next:From: Barry HoggardDate: 2001-06-29 21:53:33
Subject: Include another file in this SQL file?
Previous:From: Hafsteinn Þór HafsteinssonDate: 2001-06-29 10:41:55
Subject: Compare bit and int4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group