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

Re: GROUP BY Vs. Sub SELECT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Bruno Almeida do Lago" <teolupus(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: GROUP BY Vs. Sub SELECT
Date: 2006-04-23 23:34:24
Message-ID: 18881.1145835264@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Bruno Almeida do Lago" <teolupus(at)gmail(dot)com> writes:
> I'm just getting familiar with EXPLAIN ANALYZE output, so I'd like to get
> some help to identify which one of the following queries would be better:

Well, you're breaking one of the first laws of PG performance analysis,
which is to not try to extrapolate the behavior on large tables from the
behavior on toy tables.  You can't really see where the bottlenecks are
on a toy example, and what's more there's no reason to think that the
planner will use the same plan when presented with much larger tables.
So you need to load up a meaningful amount of data (don't forget to
ANALYZE afterward!) and then see what it does.

> I think 2nd would be better, since when database grow up the GROUP BY may
> become too costly. Is that right?

The two queries don't give the same answer, so asking which is faster
is a bit irrelevant.  (When there's more than one group, wouldn't the
per-group MAXes be different?)

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Mark KirkwoodDate: 2006-04-24 01:29:17
Subject: Re: Hardware: HP StorageWorks MSA 1500
Previous:From: Bruno Almeida do LagoDate: 2006-04-23 20:57:40
Subject: GROUP BY Vs. Sub SELECT

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