Re: slow queries, possibly disk io

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: slow queries, possibly disk io
Дата
Msg-id 14584.1117204158@sss.pgh.pa.us
обсуждение исходный текст
Ответ на slow queries, possibly disk io  (Josh Close <narshe@gmail.com>)
Ответы Re: slow queries, possibly disk io  (Josh Close <narshe@gmail.com>)
Re: slow queries, possibly disk io  (Josh Close <narshe@gmail.com>)
Re: slow queries, possibly disk io  (Rudi Starcevic <tech@wildcash.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Martin Fandel"
Дата:
Сообщение: postgresql-8.0.1 performance tuning
Следующее
От: Josh Close
Дата:
Сообщение: Re: slow queries, possibly disk io