Re: performance problem

Ok, adding the index back in worked the first time but then I tried
generating the database from scratch again, this time adding the index right
from the start. It added the 45000 rows in about a minute but then was
going dog slow on the updates again. So I did an explain and sure enough it
was not using the index. After some investigation I determined that it was
not using the index because when the transaction started there were only 4
rows in the table so at that point it didn't want to use it. It apparently
doesn't gather analysis data fast enough to handle this kind of transaction.

I worked around this by starting the transaction and inserting the 45,000
rows and then killing it. The I removed the index and readded it which
apparently gathered some stats and since there were all of the dead tuples
in there from the failed transaction it now decided that it should use the
index. I reran the script and this time it took 5 minutes again instead of
1 1/2 hours.

I am using 7.2.4. Has this improved in later versions? I'm not concerened
since this is a very rare thing to need to do and it's obviously possible to
work around but it would be nice if postgres could figure things like that
out on it's own. (It certainly would have saved me a lot of time and
confusion last night at about 3 am). Is there a way to for the use of a
specific index on a query?

rg

----- Original Message -----
From: "Rick Gigger" <rick(at)alpinenetworking(dot)com>
To: "Mike Mascari" <mascarm(at)mascari(dot)com>
Cc: "PgSQL General ML" <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, November 18, 2003 2:41 PM
Subject: Re: [GENERAL] performance problem

> Uh, I feel a little silly now. I had and index on the field in question
> (needed to locate the row to update) but later recreated the table and
> forgot to readd it. I had assumed that it was there but double checked
just
> now and it was gone. I then readded the index and and it finished in a
few
> minutes.
> Sorry about that one. Thanks for the help.
>
> rg
>
> ----- Original Message -----
> From: "Mike Mascari" <mascarm(at)mascari(dot)com>
> To: "Rick Gigger" <rick(at)alpinenetworking(dot)com>
> Cc: "PgSQL General ML" <pgsql-general(at)postgresql(dot)org>
> Sent: Tuesday, November 18, 2003 2:03 PM
> Subject: Re: [GENERAL] performance problem
>
>
> > Rick Gigger wrote:
> >
> > > I am currently trying to import a text data file without about 45,000
> > > records. At the end of the import it does an update on each of the
> 45,000
> > > records. Doing all of the inserts completes in a fairly short amount
of
> > > time (about 2 1/2 minutes). Once it gets to the the updates though it
> slows
> > > to a craw. After about 10 minutes it's only done about 3000 records.
> > >
> > > Is that normal? Is it because it's inside such a large transaction?
Is
> > > there anything I can do to speed that up. It seems awfully slow to
me.
> > >
> > > I didn't think that giving it more shared buffers would help but I
tried
> > > anyway. It didn't help.
> > >
> > > I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up
a
> lot
> > > of stuff but it didn't speed up the updates at all.
> > >
> > > I am using a dual 800mhz xeon box with 2 gb of ram. I've tried
anywhere
> > > from about 16,000 to 65000 shared buffers.
> > >
> > > What other factors are involved here?
> >
> > It is difficult to say without knowing either the definition of the
> > relation(s) or the update queries involved. Are there indexes being
> > created after the import that would allow PostgreSQL to locate the
> > rows being updated quickly, or is the update an unqualified update (no
> > WHERE clause) that affects all tuples?
> >
> > EXPLAIN ANALYZE is your friend...
> >
> > Mike Mascari
> > mascarm(at)mascari(dot)com
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>