Re: slow queries, possibly disk io

Josh Close <narshe(at)gmail(dot)com> writes:
> this_sQuery := \'
> SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
> FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \'
> WHERE tStamp > now() - interval \'\'5 mins\'\';
> \';

> 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

I think you really want that seqscan to be an indexscan, instead.
I'm betting this is PG 7.4.something? If so, probably the only
way to make it happen is to simplify the now() expression to a constant:

SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \'
WHERE tStamp > \\\'' || (now() - interval \'5 mins\')::text ||
\'\\\'\';

because pre-8.0 the planner won't realize that the inequality is
selective enough to favor an indexscan, unless it's comparing to
a simple constant.

(BTW, 8.0's dollar quoting makes this sort of thing a lot less painful)

regards, tom lane