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=YBmy3eVF7PbZWoCOCjUO3w2telGakS3mpmF/4NPLG7D5ujZQwUHCEoeXXvQwv4PxL5BLA1pyVLE+BrIEjwwvKHdsUiyVt5pppuyDCZa2tnRh8XQColP1DIaRLZwx1PrnO53NaeAACEOyDwKbbR/XsgFoychIL3OMKghAdmT+q8s=
Message-ID: <4a0cafe205052707541373ab8a@mail.gmail.com>
Date: Fri, 27 May 2005 09:54:52 -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>

> 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 |=
|
>             \'\\\'\';

The dollar sign thing would be a lot easier. I can't get this to work.
I'm using a db manager where I can just use ' instead of \'. How would
it look for that? In other words, it doesn't have the "create or
replace function as ' --stuff ' language 'plpgsql'" it just has the
actual function. Makes things a little easier. I'm getting an error at
or near "5".

>=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