pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: Doug McNaught <doug@mcnaught.org>
To: pgsql-performance@postgresql.org
Subject: Re: Slow select, insert, update
Date: Tue, 10 Aug 2004 14:44:00 -0700 (PDT)
Message-ID: <20040810214400.55597.qmail@web13123.mail.yahoo.com> (raw)
In-Reply-To: <87r7qeg1oe.fsf@asmodeus.mcnaught.org>

Does that mean reindex is not needed
for PG version 7.4?

In what kind situations under PG 7.4, 
reindex is worthwhile?

Thanks,
 

Here is doc from 7.3:
PostgreSQL is unable to reuse B-tree index pages in
certain cases. The problem is that if indexed rows are
deleted, those index pages can only be reused by rows
with similar values. For example, if indexed rows are
deleted and newly inserted/updated rows have much
higher values, the new rows can't use the index space
made available by the deleted rows. Instead, such new
rows must be placed on new index pages. In such cases,
disk space used by the index will grow indefinitely,
even if VACUUM is run frequently. 

As a solution, you can use the REINDEX command
periodically to discard pages used by deleted rows.
There is also contrib/reindexdb which can reindex an
entire database. 

The counterpart of 7.4 is:
In some situations it is worthwhile to rebuild indexes
periodically with the REINDEX command. (There is also
contrib/reindexdb which can reindex an entire
database.) However, PostgreSQL 7.4 has substantially
reduced the need for this activity compared to earlier
releases. 


--- Doug McNaught <doug@mcnaught.org> wrote:

> Paul Langard <pjl@intercellsolutions.com> writes:
> 
> > Having trouble with one table (see time to count
> records below!).
> >
> > Fairly new to postgres so any help much
> appreciated.
> >
> > It only contains 9,106 records - as you can see
> from:
> >
> >
> > select count(id) from project
> >
> > count
> > 9106
> > 1 row(s)
> > Total runtime: 45,778.813 ms
> 
> > ... the database is regularly vaccuumed.
> 
> Hmm.  You might try a VACUUM FULL and a REINDEX on
> the table (you
> don't say what version you are running--REINDEX is
> sometimes needed on
> 7.3 and below).
> 
> Also, use EXPLAIN ANALYZE on your query and post the
> result--that's
> helpful diagnostic information.
> 
> -Doug
> -- 
> Let us cross over the river, and rest under the
> shade of the trees.
>    --T. J. Jackson, 1863
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 



		
__________________________________
Do you Yahoo!?
Read only the mail you want - Yahoo! Mail SpamGuard.
http://promotions.yahoo.com/new_mail 



view thread (5+ messages)

Message-ID: <20040810214400.55597.qmail@web13123.mail.yahoo.com>
Permalink:  ../20040810214400.55597.qmail@web13123.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040810214400.55597.qmail@web13123.mail.yahoo.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: litaowu@yahoo.com, doug@mcnaught.org
  Subject: Re: Slow select, insert, update
  In-Reply-To: <20040810214400.55597.qmail@web13123.mail.yahoo.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