To: Josh Close <narshe@gmail.com>
Cc: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
Subject: Re: slow queries, possibly disk io 
In-reply-to: <4a0cafe205052706042d4e60e5@mail.gmail.com> 
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
	<758d5e7f05052614232f6c9164@mail.gmail.com>
	<4a0cafe205052706043ef8f53f@mail.gmail.com>
	<4a0cafe205052706042d4e60e5@mail.gmail.com>
Comments: In-reply-to Josh Close <narshe@gmail.com>
	message dated "Fri, 27 May 2005 08:04:39 -0500"
Date: Fri, 27 May 2005 10:29:18 -0400
Message-ID: <14584.1117204158@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Close <narshe@gmail.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