DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=eUnCzTilxCM5xdQoMS+4eP7CVq0AwuZzTvuhJy5oAfKIkLQAn87h5t5CHBFMSP9gBGatGjEPPL/ZEKdXCTIN7+xNXUsE3YNflpO80HIee6x6YRbXWUPHFnKt1S/MFaxOiMrlvUWVQbmUJxJu90sJoVgZqIDuoONn4CApA1wCizU=
Message-ID: <4a0cafe205052706042d4e60e5@mail.gmail.com>
Date: Fri, 27 May 2005 08:04:39 -0500
From: Josh Close <narshe@gmail.com>
Reply-To: Josh Close <narshe@gmail.com>
To: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
Subject: slow queries, possibly disk io
In-Reply-To: <4a0cafe205052706043ef8f53f@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
	<758d5e7f05052614232f6c9164@mail.gmail.com>
	<4a0cafe205052706043ef8f53f@mail.gmail.com>

> Few "mandatory" questions:
>
> 1. Do you vacuum your db on regular basis? :)

It's vacuumed once every hour. The table sizes and data are constantly chan=
ging.

>
> 2. Perhaps statistics for tables in question are out of date, did you
>     try alter table set statistics?

No I haven't. What would that do for me?

>
> 3. explain analyze of the slow query?

Here is the function that is ran:

CREATE OR REPLACE FUNCTION adaption.funmsgspermin()
  RETURNS int4 AS
'
DECLARE
    this_rServerIds  RECORD;
    this_sQuery      TEXT;
    this_iMsgsPerMin INT;
    this_rNumSent    RECORD;

BEGIN
    this_iMsgsPerMin :=3D 0;
    FOR this_rServerIds IN
        SELECT iId
        FROM adaption.tblServers
    LOOP
        this_sQuery :=3D \'
            SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
            FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \'
            WHERE tStamp > now() - interval \'\'5 mins\'\';
        \';
        FOR this_rNumSent IN EXECUTE this_sQuery LOOP
            this_iMsgsPerMin :=3D this_iMsgsPerMin + this_rNumSent.iNumSent=
;
        END LOOP;
    END LOOP;

    this_iMsgsPerMin :=3D this_iMsgsPerMin / 5;

    RETURN this_iMsgsPerMin;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

Here is the explain analyze of one loops of the sum:

Aggregate  (cost=3D31038.04..31038.04 rows=3D1 width=3D4) (actual
time=3D14649.602..14649.604 rows=3D1 loops=3D1)
  ->  Seq Scan on tblbatchhistory_1  (cost=3D0.00..30907.03 rows=3D52401
width=3D4) (actual time=3D6339.223..14648.433 rows=3D919 loops=3D1)
        Filter: (tstamp > (now() - '00:05:00'::interval))
Total runtime: 14649.709 ms

>
> 4. if you for some reason cannot give explain analyze, please try to
> describe the type of query (what kind of join(s)) and amount of data
> found in the tables.
>
> 2 minutes from 10 seconds is a huge leap, and it may mean that
> PostgreSQL for some reason is not planning as well as it could.
> Throwing more RAM at the problem can help, but it would be better
> to hint the planner to do the right thing.  It may be a good time to
> play with planner variables. :)

Is there any documentation on planner vars? And how would I throw more
ram at it? It has 2 gigs right now. How do I know if postgres is using
that?

-Josh