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
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