Thanks Joris.
I'm familiar with explain and I do use it quite frequently. My site is just starting out so I don't think performance is that crucial of an issue at the onset (premature optimization). Also, there is not that much data yet so its difficult to have a very accurate picture of what a full production load would look like.
So I guess I was looking for some type of rule of thumb for when the different options would be better. I guess I'll just have to keep an eye out for my trouble queries and work with my indexes and techniques as I progress. Thanks again.
On Sat, Dec 13, 2008 at 6:51 PM, Joris Dobbelsteen
<joris@familiedobbelsteen.nl> wrote:
Jonathon Suggs wrote, On 10-12-08 20:12:
I'm asking this as a more general question on which will perform better. I'm trying to get a set of comments and their score/rankings from two tables.
*comments*
cid (integer, primary key)
title
body
*comment_ratings*
cid (integer, primary key)
uid (integer, primary key)
score
*Option 1* (Single group by query)
select c.cid,c.title,c.body,coalsece(sum(r.score),0) as score
from comments c left outer join comment_ratings r on c.cid = r.cid
group by c.cid,c.title,c.body;
*Option 2* (Two simple queries)
select cid,title,body from comments;
select cid,sum(score) from comment_ratings;
You can answer that yourself... Create the database and fill it with a decent set of sample data (if that doesn't already exists). Run analyze to get your statistics. Use explain/explain analyze on the queries you are interested in.
The manual has very good references. If you like, pgadmin can even create graphical representation (which is what I prefer).
The obvious advantage depends on what you want to achieve? If its just a dump of a table and an aggregate, you second option might be better.
If you need both data to transform it into something the user will see, probably the first option is better, as you leave combining to the highly specialized and optimized software and don't have to do it yourself. The choice has to do with index scans and table scans, that depend on the situation.
In fact, if the first one doesn't come out nice (first join than aggregate, do aggeration in a subquery). So: learn explain!!! Every (decent) RDBMS has such a facility.
- Joris