pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Performance problems - Indexes and VACUUM
Date: Wed, 17 Oct 2001 11:31:25 -0400
Message-ID: <27113.1003332685@sss.pgh.pa.us> (raw)
In-Reply-To: <web-149622@davinci.ethosmedia.com>
References: <web-149622@davinci.ethosmedia.com>

"Josh Berkus" <josh@agliodbs.com> writes:
> This is on 7.1.2 (SuSE 7.2, ReiserFS, PG built from source).  Explicitly
> dropping the indexes before dropping the tables seems to have solved the
> problem.  My guess, without understanding the guts of the thing at all,
> is that the transactional nature of the drop and re-create causes the
> indexes not to be fully cleared before they are re-built.  Maybe it's
> even a reaction to the journaling file system.

I don't believe a single word of that explanation ... whatever is going
on here, that ain't it.  A new table is going to have a new OID, and
so will its indexes; there is no way that Postgres will confuse it with
the old one, even if bits of the old one were still hanging around
somehow (which I don't believe either).

One thing to think about, if you are dropping and recreating tables in
a plpgsql function, is that you probably need to do it with EXECUTE
to avoid plan caching.

> BTW, any issues with PostgreSQL and DMA disk access?

Not unless your kernel or hardware are broken.  But I have seen cases
where hardware problems (eg, bogus DMA controller) manifested themselves
only as database errors.  Evidently Postgres was pushing the disk harder
than anything else on the system, so it was more likely to get bit by
a sporadic hardware booboo.

			regards, tom lane



view thread (6+ messages)  latest in thread

Message-ID: <27113.1003332685@sss.pgh.pa.us>
Permalink:  ../27113.1003332685@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/27113.1003332685@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: Performance problems - Indexes and VACUUM
  In-Reply-To: <27113.1003332685@sss.pgh.pa.us>

* 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