From: Sean Chittenden <sean@chittenden.org>
To: Neil Conway <neilc@samurai.com>
Cc: logan@datacurrent.com
Cc: pgsql-performance@postgresql.org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: Sun, 16 Mar 2003 22:29:29 -0800
Message-ID: <20030317062929.GI23355@perrin.int.nxad.com> (raw)
In-Reply-To: <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com>
References: <20030316060125.GD19570@vt.edu>
<3E741B93.1090203@joeconway.com>
<20030316075206.GE19570@vt.edu>
<Pine.LNX.4.53.0303170059580.8767@neo.magick.org>
<20030317061011.GH23355@perrin.int.nxad.com>
<33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com>
> >> A "select count(*) FROM tblwordidx" initially took about 1 second to
> >> return a count of 2 million but after a few months it took several
> >> minutes of really hard HDD grinding.
> >
> > That's because there are dead entries in the index that weren't being
> > reused or cleaned up. As I said, this has been fixed.
>
> That's doubtful: "select count(*) FROM foo" won't use an
> index. There are a bunch of other factors (e.g. dead heap tuples,
> changes in the pages cached in the buffer, disk fragmentation, etc.)
> that could effect performance in that situation, however.
*blush* Yeah, jumped the gun on that when I read that queries were
getting slower (churn of an index == slow creaping death for
performance). A SELECT COUNT(*), however, wouldn't be affected by the
index growth problem. Is the COUNT() on a view that uses an index? I
haven't had any real problems with this kind of degredation outside of
indexes. :-/ -sc
--
Sean Chittenden
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: sean@chittenden.org, neilc@samurai.com, logan@datacurrent.com
Subject: Re: postgresql meltdown on PlanetMath.org
In-Reply-To: <20030317062929.GI23355@perrin.int.nxad.com>
References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <Pine.LNX.4.53.0303170059580.8767@neo.magick.org> <20030317061011.GH23355@perrin.int.nxad.com> <33137.130.15.224.150.1047881939.squirrel@mailbox.samurai.com> <20030317062929.GI23355@perrin.int.nxad.com>
* 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