Skip site navigation (1) Skip section navigation (2)

slow queries, possibly disk io

From: Josh Close <narshe(at)gmail(dot)com>
To: POSTGRES-PERFORMANCE <pgsql-performance(at)postgresql(dot)org>
Subject: slow queries, possibly disk io
Date: 2005-05-27 13:04:39
Message-ID: 4a0cafe205052706042d4e60e5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
> 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 changing.

>
> 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 := 0;
    FOR this_rServerIds IN
        SELECT iId
        FROM adaption.tblServers
    LOOP
        this_sQuery := \'
            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 := this_iMsgsPerMin + this_rNumSent.iNumSent;
        END LOOP;
    END LOOP;

    this_iMsgsPerMin := this_iMsgsPerMin / 5;

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

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

Aggregate  (cost=31038.04..31038.04 rows=1 width=4) (actual
time=14649.602..14649.604 rows=1 loops=1)
  ->  Seq Scan on tblbatchhistory_1  (cost=0.00..30907.03 rows=52401
width=4) (actual time=6339.223..14648.433 rows=919 loops=1)
        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

In response to

Responses

pgsql-performance by date

Next:From: Josh CloseDate: 2005-05-27 13:05:47
Subject: Re: slow queries, possibly disk io
Previous:From: Josh CloseDate: 2005-05-27 12:52:16
Subject: Re: slow queries, possibly disk io

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group