Re: [PERFORM] SLOW query with aggregates

[Tom Lane]

A Palmblad [EMAIL PROTECTED] writes:
  GroupAggregate  (cost=0.00..338300.34 rows=884 width=345) (actual
 time=86943.272..382718.104 rows=3117 loops=1)
-  Merge Join  (cost=0.00..93642.52 rows=1135610 width=345) (actual
 time=0.148..24006.748 rows=1120974 loops=1)

You do not have a planning problem here, and trying to change the plan
is a waste of time.  The slowness is in the actual computation of the
aggregate functions; ergo the only way to speed it up is to change what
you're computing.  What aggregates are you computing exactly, and over
what datatypes?

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] SLOW query with aggregates

[Greg Stark]

A Palmblad [EMAIL PROTECTED] writes:

  GroupAggregate  (cost=0.00..338300.34 rows=884 width=345) (actual
 time=86943.272..382718.104 rows=3117 loops=1)
-  Merge Join  (cost=0.00..93642.52 rows=1135610 width=345) (actual
 time=0.148..24006.748 rows=1120974 loops=1)

I think the reason you're getting a GroupAggregate here instead of a
HashAggregate is that the MergeJoin is already producing the records in the
desired order, so the GroupAggregate doesn't require an extra sort, ie, it's
effectively free.

You might be able to verify this by running the query with 

enable_indexscan = off  and/or  enable_mergejoin = off

some combination of which might get the planner to do a seqscan of the large
table with a hash join to the small table and then a HashAggregate.

If you're reading a lot of the large table the seqscan could be a little
faster, not much though. And given the accurate statistics guesses here the
planner may well have gotten this one right and the seqscan is slower. Can't
hurt to be verify it though.

-- 
greg


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings