Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] slow table updates

From: Reece Hart <rkh(at)gene(dot)COM>
To: "Guthrie, Jeremy" <jeremy(dot)guthrie(at)berbee(dot)com>
Cc: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>,"pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>,SF PostgreSQL <sfpug(at)postgresql(dot)org>
Subject: Re: [PERFORM] slow table updates
Date: 2003-07-23 18:07:10
Message-ID: 1058983630.7281.101.camel@tallac (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performancesfpug
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(at)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(at)gene(dot)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(at)in-machina(dot)com, GPG: 0x25EC91A0

pgsql-admin by date

Next:From: Andrew SullivanDate: 2003-07-23 20:29:52
Subject: Re: Replication/Failover/HA solution
Previous:From: Bruce MomjianDate: 2003-07-23 17:54:15
Subject: Re: using ssl some of the time

sfpug by date

Next:From: Josh BerkusDate: 2003-07-23 21:03:33
Subject: Need one hour consulting -- SSL
Previous:From: Reece HartDate: 2003-07-23 17:44:36
Subject: Re: [PERFORM] slow table updates

pgsql-performance by date

Next:From: Guthrie, JeremyDate: 2003-07-23 18:38:03
Subject: Re: slow table updates
Previous:From: Reece HartDate: 2003-07-23 17:44:36
Subject: Re: [PERFORM] slow table updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group