Re: Query Question (one group by vs two separate)

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;
>
> I know that in general you always want to keep the number of queries to
> a minimum and that Option 1 is the most logical/preferred way to
> proceed. However, I've greatly simplified Option 1. In practice I'm
> going to be pulling a lot more columns and will be inner joining to
> additional tables. /So my real question is whether having a lot of
> items in the group by clause will have an adverse effect on performance./

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