Doing the query explain SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent FROM adaption.tblBatchHistory_1 WHERE tStamp > ( now() - interval '5 mins' )::text
gives me this: Aggregate (cost=32138.33..32138.33 rows=1 width=4) -> Seq Scan on tblbatchhistory_1 (cost=0.00..31996.10 rows=56891 width=4) Filter: ((tstamp)::text > ((now() - '00:05:00'::interval))::text) Still not an index scan. On 5/27/05, Tom Lane <[EMAIL PROTECTED]> wrote: > Josh Close <[EMAIL PROTECTED]> 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 > -- -Josh ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
