pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: How can an index be larger than a table
Date: Thu, 21 Apr 2005 13:51:18 -0400
Message-ID: <60br882f3d.fsf@dba2.int.libertyrms.com> (raw)
References: <1114078390.26441.232391808@webmail.messagingengine.com>
	<200504211031.51567.josh@agliodbs.com>

josh@agliodbs.com (Josh Berkus) writes:
> David,
>
>> What also seems weird to me is that the control table has some unique
>> indexes created on it, but the data_upate_events table just has a unique
>> constraint.  Will postgres use an index in the background to enforce
>> this constraint?
>
> If you somehow have a unique constraint without a unique index, something is 
> seriously broken.   I suspect hacking of system tables.
>
> Otherwise, it sounds like you have index bloat due to mass deletions.  Run 
> REINDEX, or, preferably, VACUUM FULL and then REINDEX.

There is in a sense no "best order" for this; VACUUM FULL will wind up
further injuring the indices when it reorganizes the table, which
means that whether you do it first or last, you'll have "index injury."

This actually seems a plausible case for CLUSTER.  (And as Douglas &
Douglas says, "You can become a hero by using CLUSTER."  :-))
-- 
(format nil "~S@~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78.  "I will not tell my Legions of Terror
"And he must  be taken alive!" The command will be:  ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/;



view thread (3+ messages)

Message-ID: <60br882f3d.fsf@dba2.int.libertyrms.com>
Permalink:  ../60br882f3d.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/60br882f3d.fsf@dba2.int.libertyrms.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: How can an index be larger than a table
  In-Reply-To: <60br882f3d.fsf@dba2.int.libertyrms.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