DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Q5OBq0r02Z/XewwfBUVefHmIx/fzQ0KsRL4oqu4LXrCAM96SDoQf4VsZFh9gR0yRZK5xv3RIQdfSDDVdl9ooi1K1/rb05JxYFvx4bknmDAnAYwJZms5x8MJrhk4cQb8rZSlR9V9d+LpA80BZDhs9c4J+Lmlao1Y1DM3MMwg1V3c=
Message-ID: <4a0cafe2050527080038616a2f@mail.gmail.com>
Date: Fri, 27 May 2005 10:00:23 -0500
From: Josh Close <narshe@gmail.com>
Reply-To: Josh Close <narshe@gmail.com>
To: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
Subject: Re: slow queries, possibly disk io
In-Reply-To: <14584.1117204158@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
	<758d5e7f05052614232f6c9164@mail.gmail.com>
	<4a0cafe205052706043ef8f53f@mail.gmail.com>
	<4a0cafe205052706042d4e60e5@mail.gmail.com>
	<14584.1117204158@sss.pgh.pa.us>

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=3D32138.33..32138.33 rows=3D1 width=3D4)
->  Seq Scan on tblbatchhistory_1  (cost=3D0.00..31996.10 rows=3D56891 widt=
h=3D4)
Filter: ((tstamp)::text > ((now() - '00:05:00'::interval))::text)

Still not an index scan.

On 5/27/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Close <narshe@gmail.com> writes:
> >         this_sQuery :=3D \'
> >             SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
> >             FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || =
\'
> >             WHERE tStamp > now() - interval \'\'5 mins\'\';
> >         \';
>=20
> > Here is the explain analyze of one loops of the sum:
>=20
> > Aggregate  (cost=3D31038.04..31038.04 rows=3D1 width=3D4) (actual
> > time=3D14649.602..14649.604 rows=3D1 loops=3D1)
> >   ->  Seq Scan on tblbatchhistory_1  (cost=3D0.00..30907.03 rows=3D5240=
1
> > width=3D4) (actual time=3D6339.223..14648.433 rows=3D919 loops=3D1)
> >         Filter: (tstamp > (now() - '00:05:00'::interval))
> > Total runtime: 14649.709 ms
>=20
> 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:
>=20
>             SELECT COALESCE( SUM( iNumSent ), 0 ) AS iNumSent
>             FROM adaption.tblBatchHistory_\' || this_rServerIds.iId || \'
>             WHERE tStamp > \\\'' || (now() - interval \'5 mins\')::text |=
|
>             \'\\\'\';
>=20
> 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.
>=20
> (BTW, 8.0's dollar quoting makes this sort of thing a lot less painful)
>=20
>                         regards, tom lane
>=20


--=20
-Josh