X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 57204D1C7F2; Wed, 23 Jul 2003 18:07:17 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 46319-09; Wed, 23 Jul 2003 15:06:36 -0300 (ADT) Received: from djinn.gene.com (socks-open.gene.com [192.12.78.2]) by svr1.postgresql.org (Postfix) with ESMTP id E8F66D1C7EB; Wed, 23 Jul 2003 15:07:15 -0300 (ADT) Received: from faraday.gene.com (smtp.gene.com [128.137.49.205]) by djinn.gene.com (8.12.9/8.12.9) with ESMTP id h6NI7BDe224917; Wed, 23 Jul 2003 11:07:11 -0700 (PDT) Received: from tallac.gene.com (mail.gene.com [128.137.49.207]) by faraday.gene.com (Switch-3.1.0/Switch-3.1.0) with ESMTP id h6NI7Aca012343; Wed, 23 Jul 2003 11:07:11 -0700 (PDT) Subject: Re: [PERFORM] slow table updates From: Reece Hart To: "Guthrie, Jeremy" Cc: "pgsql-admin@postgresql.org" , "pgsql-performance@postgresql.org" , SF PostgreSQL In-Reply-To: References: Content-Type: multipart/alternative; boundary="=-Ru+lwTMy1GLQ47eoVyFi" Organization: Genentech, Inc. Message-Id: <1058983630.7281.101.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 23 Jul 2003 11:07:10 -0700 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/247 X-Sequence-Number: 9587 --=-Ru+lwTMy1GLQ47eoVyFi Content-Type: text/plain Content-Transfer-Encoding: 7bit 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.0431161854174633 p2thread_pmodel_id | 123243 | 1009606656 | 0.0430003860830331 paprospect2_redundant_alignment | 229934 | 1883619328 | 0.0230479032332376 What do you make of 'em apples? Thanks, Reece -- Reece Hart, Ph.D. rkh@gene.com, 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 reece@in-machina.com, GPG: 0x25EC91A0 --=-Ru+lwTMy1GLQ47eoVyFi Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 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.0431161854174633
 p2thread_pmodel_id              |   123243 | 1009606656 | 0.0430003860830331
 paprospect2_redundant_alignment |   229934 | 1883619328 | 0.0230479032332376

What do you make of 'em apples?

Thanks,
Reece

-- 
Reece Hart, Ph.D.                       rkh@gene.com, 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     reece@in-machina.com, GPG: 0x25EC91A0
--=-Ru+lwTMy1GLQ47eoVyFi--