pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Reece Hart <rkh@gene.COM>
To: Guthrie, Jeremy <jeremy.guthrie@berbee.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: SF PostgreSQL <sfpug@postgresql.org>
Subject: Re: [PERFORM] slow table updates
Date: 23 Jul 2003 11:07:10 -0700
Message-ID: <1058983630.7281.101.camel@tallac> (raw)
In-Reply-To: <AEB367BD3D004340B2710636917B1E3E025E981C@CTG-MSNEX01.staff.berbee.com>
References: <AEB367BD3D004340B2710636917B1E3E025E981C@CTG-MSNEX01.staff.berbee.com>

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

view thread (5+ messages)  latest in thread

Message-ID: <1058983630.7281.101.camel@tallac>
Permalink:  ../1058983630.7281.101.camel@tallac/
Also on:    postgresql.org/message-id/1058983630.7281.101.camel@tallac

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rkh@gene.COM, jeremy.guthrie@berbee.com, pgsql-admin@postgresql.org, sfpug@postgresql.org
  Subject: Re: [PERFORM] slow table updates
  In-Reply-To: <1058983630.7281.101.camel@tallac>
  References: <AEB367BD3D004340B2710636917B1E3E025E981C@CTG-MSNEX01.staff.berbee.com> <1058983630.7281.101.camel@tallac>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox