Re: query optimization: aggregate and distinct

On Wed, Aug 20, 2003 at 16:26:26 -0700,
Jeff Davis <jdavis-pgsql(at)empires(dot)org> wrote:
>
> That produces the results that I need, but it seems inefficient to join a
> table with itself like that. My real query (not this simplified example)
> takes 5+ seconds and I suspect this join is why.
>
> Is there a better way?
>
> For my real query, it's using index scans where I'd expect, and I frequently
> VACUUM ANALYZE the big table and I have all the stats turned on. Also, I have
> more shared buffers than needed to put everything in RAM.
>
> Right now I'm using 7.2.1. Any improvements in 7.3 or 7.4 that would help this
> issue?

I think there is a chance you might benefit from hash aggregates in 7.4.
Explain analyze might give you a better idea where the time is being
spent. If it is sorting the data for the group bys and there are only a few
groups relative to the total number of rows in the table, you will probably
get a big speed up in 7.4.