pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (19+ messages)  latest in thread

Message-ID: <200311101259.03389.josh@agliodbs.com>
Permalink:  ../200311101259.03389.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200311101259.03389.josh@agliodbs.com

 · 

reply

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