> 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()
    this_rServerIds  RECORD;
    this_sQuery      TEXT;
    this_iMsgsPerMin INT;
    this_rNumSent    RECORD;

    this_iMsgsPerMin := 0;
    FOR this_rServerIds IN
        SELECT iId
        FROM adaption.tblServers
        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;

    this_iMsgsPerMin := this_iMsgsPerMin / 5;

    RETURN this_iMsgsPerMin;

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to