slow queries, possibly disk io - Mailing list pgsql-performance

From Josh Close
Subject slow queries, possibly disk io
Date
Msg-id 4a0cafe205052706042d4e60e5@mail.gmail.com
Whole thread Raw
In response to slow queries, possibly disk io  (Josh Close <narshe@gmail.com>)
Responses Re: slow queries, possibly disk io
List 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

pgsql-performance by date:

Previous
From: Josh Close
Date:
Subject: Re: slow queries, possibly disk io
Next
From: Josh Close
Date:
Subject: Re: slow queries, possibly disk io