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