pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (39+ messages)  latest in thread

Message-ID: <20030317062929.GI23355@perrin.int.nxad.com>
Permalink:  ../20030317062929.GI23355@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030317062929.GI23355@perrin.int.nxad.com

 · 

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