Re: *very* slow query to summarize data for a month ... - Mailing list pgsql-performance

Marc,

I'd say your machine is very low on available RAM, particularly sort_mem.
The steps which are taking a long time are:

>  Aggregate  (cost=32000.94..32083.07 rows=821 width=41) (actual
time=32983.36..47586.17 rows=144 loops=1)
>    ->  Group  (cost=32000.94..32062.54 rows=8213 width=41) (actual
time=32957.40..42817.88 rows=462198 loops=1)

and:

>                ->  Merge Join  (cost=31321.45..31466.92 rows=8213 width=41)
(actual time=13983.07..22642.14 rows=462198 loops=1)
>                      Merge Cond: ("outer".company_id = "inner".company_id)
>                      ->  Sort  (cost=24.41..25.29 rows=352 width=25) (actual
time=5.52..7.40 rows=348 loops=1)

There are also *large* delays between steps.    Either your I/O is saturated,
or you haven't run a VACUUM FULL ANALYZE in a while (which would also explain
the estimates being off).


--
-Josh Berkus
 Aglio Database Solutions
 San Francisco