От: Reece Hart
Тема: Re: slow table updates
Дата: ,
Msg-id: 1058983630.7281.101.camel@tallac
(см: обсуждение, исходный текст)
Ответ на: slow table updates  (Reece Hart)
Список: pgsql-performance

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

slow table updates  (Reece Hart, )
 Re: slow table updates  (Richard Huxton, )
  Re: slow table updates  (Reece Hart, )
 Re: slow table updates  (Reece Hart, )
 Re: slow table updates  ("Guthrie, Jeremy", )

On Wed, 2003-07-23 at 10:47, Guthrie, Jeremy wrote:
Have you checked the sizes of your indexes?  You may need to rebuild them...

Multiply the relpages colum by 8192.

So, what does this tell me? I'm guessing that you're implying that I should expect 8192 keys per page, and that this therefore indicates the sparseness of the key pages. Guessing that, I did:

rkh@csb=> SELECT c2.relname, c2.relpages, c2.relpages*8192 as "*8192",
   43413476::real/(c2.relpages*8192) FROM pg_class c, pg_class c2, pg_index i
   where c.oid = i.indrelid AND c2.oid = i.indexrelid and c2.relname~'^p2th|^papro'
   ORDER BY c2.relname;

             relname             | relpages |   *8192    |      ?column?
---------------------------------+----------+------------+--------------------p2thread_p2params_id            |   122912 | 1006895104 | 0.0431161854174633p2thread_pmodel_id              |   123243 | 1009606656 | 0.0430003860830331paprospect2_redundant_alignment |   229934 | 1883619328 | 0.0230479032332376

What do you make of 'em apples?


Reece Hart, Ph.D.                       , http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     , GPG: 0x25EC91A0

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

От: "Guthrie, Jeremy"
Сообщение: Re: slow table updates
От: Jörg Schulz
Сообщение: Re: different query plan for same select