From: Josh Berkus <josh@agliodbs.com>
To: Marc G. Fournier <scrappy@postgresql.org>
To: pgsql-performance@postgresql.org
Subject: Re: *very* slow query to summarize data for a month ...
Date: Mon, 10 Nov 2003 12:59:03 -0800
Message-ID: <200311101259.03389.josh@agliodbs.com> (raw)
In-Reply-To: <20031110160144.C727@ganymede.hub.org>
References: <20031110160144.C727@ganymede.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, scrappy@postgresql.org
Subject: Re: *very* slow query to summarize data for a month ...
In-Reply-To: <200311101259.03389.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox