Re: Help tracking down problem with inserts slowing down...

On Fri, Dec 05, 2003 at 09:54:52PM -0500, Robert Treat wrote:
> On Friday 05 December 2003 16:51, Steve Wampler wrote:
> > I need some help tracking down a sudden, massive slowdown
> > in inserts in one of our databases.
> >
> > PG: 7.2.3 (RedHat 8.0)
> >
> > Background. We currently run nearly identical systems
> > at two sites: Site A is a 'lab' site used for development,
> > Site B is a production site.
> >
> > The databases in question have identical structure:
> >
> > A simple table with 4 columns with a trigger function
> > on inserts (which checks to see if the entry already
> > exists, and if so, changes the insert into an update...)
> > A simple view with 4 columns into the above table.
> >
> > All access is through jdbc (JDK 1.3.1, jdbc 7.1-1.3),
> > postgresql.conf's are identical.
> >
> > The two sites were performing at comparable speeds until
> > a few days ago, when we deleted several million records
> > from each database and then did a vacuum full; analyze
> > on both. Now inserts at Site B are several orders of
> > magnitude slower than at Site A. The odd thing is that
> > Site B's DB now has only 60,000 records while Site A's is
> > up around 3 million. Inserts at A average 63ms, inserts
> > at B are now up at 4.5 seconds!
> >
> > EXPLAIN doesn't show any difference between the two.
> >
> > Can someone suggest ways to track this down? I don't know
> > much about postgresql internals/configuration.
> >
>
> What does explain analyze show for the insert query?
>
> Are there FK and/or Indexes involved here? Did you you reindex?
> A vacuum verbose could give you a good indication if you need to reindex,
> compare the # of pages in the index with the # in the table.

Thanks Robert!

It looks like reindex did the trick.

Now I have a general question - what are the relationships between:
vacuum, analyze, reindex, and dropping/recreating the indices?
That is, which is the following is 'best' (or is there a different
ordering that is better)?:

(1) vacuum
analyze
reindex

(2) vacuum
reindex
analyze

(3) drop indices
vacuum
create indices
analyze

(4) drop indices
vacuum
analyze
create indices

And, is reindex equivalent to dropping, then recreating the indices?
[it appears to be "no", from what I've just seen, but I don't know...]

Thanks!
Steve
--
Steve Wampler -- swampler(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.