X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 094369DCDA4 for ; Thu, 23 Mar 2006 21:16:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67426-07 for ; Thu, 23 Mar 2006 21:16:13 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id D4C039DCCD4 for ; Thu, 23 Mar 2006 21:16:05 -0400 (AST) Received: by nproxy.gmail.com with SMTP id b2so360361nfe for ; Thu, 23 Mar 2006 17:16:09 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=IOaS1PEBcxo32LAxI7XbJ4sPpN7FiN6nko2ryU/y3UCvLOdXcGpZBlm8TMKuCsoHuSR2PZe6X5XxgQQ7Pvfl86s713HuG5K6MFtyi91Q1/OnHsNj+XvRnsWxVkXEDiM/jkzsqlXOnwwUKT/StIWs9hGb0bCe4E223jLtyqC41yU= Received: by 10.48.215.9 with SMTP id n9mr811592nfg; Thu, 23 Mar 2006 17:16:09 -0800 (PST) Received: by 10.48.144.19 with HTTP; Thu, 23 Mar 2006 17:16:09 -0800 (PST) Message-ID: Date: Thu, 23 Mar 2006 20:16:09 -0500 From: "Gregory Maxwell" To: pgsql-admin@postgresql.org Subject: Bloated pg_shdepend_depender_index MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.736 required=5 tests=[AWL=-0.596, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 0.736 X-Spam-Level: X-Archive-Number: 200603/224 X-Sequence-Number: 20981 So, I've run a number of PG databases for a number of years.. and I've now run into something I've never seen before.... in the most trivial of places. A couple of months back my girlfriend installed a music player called amarok on her system... I don't know much about it, but it stores its metadata in a backend database (postgresql here). I recently noticed that this database has grown to a huge size. ... Which I found to be somewhat odd because none of the tables have more than around 1000 rows. I hadn't been vacuuming because I didn't think that anything would ever be deleted.... so I performed a vacuum full... but no luck, it was still about 6.4GB. With some help of the folks on IRC I discovered... postgres=3D# select relname, pg_relation_size(oid) FROM pg_class ORDER BY 2 DESC LIMIT 2; relname | pg_relation_size -----------------------------+------------------ pg_shdepend_depender_index | 159465472 pg_shdepend_reference_index | 97271808 (2 rows) The pg_shdepend table has only about 50 rows.. why doesn't vacuum shrink the indexes? I understand that I can take the database into single user mode and reindex that table... In fact I could just drop the whole database, since the application can just rebuild it.... So my primary concern isn't fixing this, as I'm pretty sure I'll have no problem fixing it.=20 I'd just like to know why it got into this state, and make sure there isn't some PG bug here worthy of exploration.