Skip site navigation (1) Skip section navigation (2)

Re: slow queries, possibly disk io

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Close <narshe(at)gmail(dot)com>
Cc: POSTGRES-PERFORMANCE <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow queries, possibly disk io
Date: 2005-05-27 14:29:18
Message-ID: 14584.1117204158@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Josh Close <narshe(at)gmail(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Josh CloseDate: 2005-05-27 14:54:52
Subject: Re: slow queries, possibly disk io
Previous:From: Martin FandelDate: 2005-05-27 13:41:52
Subject: postgresql-8.0.1 performance tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group