From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Close <narshe@gmail.com>
Cc: POSTGRES-PERFORMANCE <pgsql-performance@postgresql.org>
Subject: Re: slow queries, possibly disk io
Date: Fri, 27 May 2005 10:29:18 -0400
Message-ID: <14584.1117204158@sss.pgh.pa.us> (raw)
In-Reply-To: <4a0cafe205052706042d4e60e5@mail.gmail.com>
References: <4a0cafe205052613242e0d4d10@mail.gmail.com>
<758d5e7f05052614232f6c9164@mail.gmail.com>
<4a0cafe205052706043ef8f53f@mail.gmail.com>
<4a0cafe205052706042d4e60e5@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, narshe@gmail.com
Subject: Re: slow queries, possibly disk io
In-Reply-To: <14584.1117204158@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox