Re: Slow query. Any way to speed up?

От: Tom Lane
Тема: Re: Slow query. Any way to speed up?
Дата: ,
Msg-id: 22453.1136579049@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Slow query. Any way to speed up?  (Patrick Hatcher)
Список: pgsql-performance

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

Slow query. Any way to speed up?  (Patrick Hatcher, )
 Re: Slow query. Any way to speed up?  (Tom Lane, )
  Re: Slow query. Any way to speed up?  (Patrick Hatcher, )
   Re: Slow query. Any way to speed up?  (Tom Lane, )

Patrick Hatcher <> writes:
>                           ->  Seq Scan on cdm_ddw_tran_item a1
> (cost=0.00..1547562.88 rows=8754773 width=23) (actual
> time=14.219..535704.691 rows=10838135 loops=1)
>                                 Filter: ((((appl_id)::text = 'MCOM'::text)
> OR ((appl_id)::text = 'NET'::text)) AND ((tran_typ_id = 'S'::bpchar) OR
> (tran_typ_id = 'R'::bpchar)))

The bulk of the time is evidently going into this step.  You didn't say
how big cdm_ddw_tran_item is, but unless it's in the billion-row range,
an indexscan isn't going to help for pulling out 10 million rows.
This may be about the best you can do :-(

If it *is* in the billion-row range, PG 8.1's bitmap indexscan facility
would probably help.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: David Lang
Дата:
Сообщение: Re: help tuning queries on large database
От: Michael Stone
Дата:
Сообщение: Re: improving write performance for logging