> 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
