Re: DB Performance decreases due to often written/accessed

От: Jim C. Nasby
Тема: Re: DB Performance decreases due to often written/accessed
Дата: ,
Msg-id: 20061019172249.GT71084@nasby.net
(см: обсуждение, исходный текст)
Ответ на: Re: DB Performance decreases due to often written/accessed  (Richard Huxton)
Ответы: Re: DB Performance decreases due to often written/accessed  (Richard Huxton)
Список: pgsql-performance

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

DB Performance decreases due to often written/accessed table  ("Jens Schipkowski", )
 Re: DB Performance decreases due to often written/accessed  (Richard Huxton, )
  Re: DB Performance decreases due to often written/accessed table  ("Jens Schipkowski", )
   Re: DB Performance decreases due to often written/accessed  (Richard Huxton, )
    Re: DB Performance decreases due to often written/accessed  ("Jim C. Nasby", )
     Re: DB Performance decreases due to often written/accessed  (Richard Huxton, )
 Re: DB Performance decreases due to often written/accessed table  ("Merlin Moncure", )
  Re: DB Performance decreases due to often written/accessed table  ("Jens Schipkowski", )
   Re: DB Performance decreases due to often written/accessed table  ("Merlin Moncure", )

On Thu, Oct 19, 2006 at 06:19:16PM +0100, Richard Huxton wrote:
> OK - these plans look about the same, but the time is greatly different.
> Both have rows=140247 as the estimated number of rows in tbl_reg. Either
>  you have many more rows in the second case (in which case you're not
> running ANALYSE enough) or you have lots of gaps in the table (you're
> not running VACUUM enough).

Look closer... the actual stats show that the sorts in the second case
are returning far more rows. And yes, analyze probably needs to happen.

> I'd then try putting an index on (attr1,attr2,attr3...attr6) and see if
> that helps reduce time.

With bitmap index scans, I think it'd be much better to create 6 indexes
and see which ones actually get used (and then drop the others).
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


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

От: "Kevin"
Дата:
Сообщение: Re: Swappiness setting on a linux pg server
От: Ben Suffolk
Дата:
Сообщение: New hardware thoughts