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)
Ответы: Re: slow queries, possibly disk io  (Josh Close)
Re: slow queries, possibly disk io  (Josh Close)
Re: slow queries, possibly disk io  (Rudi Starcevic)
Список: pgsql-performance

Скрыть дерево обсуждения

slow queries, possibly disk io  (Josh Close, )
 Re: slow queries, possibly disk io  (John Arbash Meinel, )
  Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Simon Riggs, )
    TIP 9: the planner will ignore... & datatypes  ("Marc Mamin", )
     Re: TIP 9: the planner will ignore... & datatypes  (Alvaro Herrera, )
 Re: slow queries, possibly disk io  (Dawid Kuroczko, )
 Re: slow queries, possibly disk io  (Christopher Kings-Lynne, )
  Re: slow queries, possibly disk io  (Josh Close, )
 slow queries, possibly disk io  (Josh Close, )
  Re: slow queries, possibly disk io  (Tom Lane, )
   Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Josh Close, )
   Re: slow queries, possibly disk io  (Rudi Starcevic, )
    Re: slow queries, possibly disk io  (Josh Close, )
     Re: slow queries, possibly disk io  (Tom Lane, )
     Re: slow queries, possibly disk io  (Josh Close, )
      Re: slow queries, possibly disk io  (Manfred Koizar, )

Josh Close <> 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 по дате сообщения:

От: "Eric Lauzon"
Дата:
Сообщение: OID vs overall system performances on high load databases.
От: Jeffrey Tenny
Дата:
Сообщение: Redundant indexes?