From: Juan Casero <caseroj@comcast.net>
To: pgsql-performance@postgresql.org
Subject: Re: MySQL is faster than PgSQL but a large margin in
Date: Thu, 22 Dec 2005 21:44:32 -0500
User-Agent: KMail/1.8.2
References: <BFCF9173.194ED%llonergan@greenplum.com>
	<43AAAB58.6050109@logix-tt.com>
In-Reply-To: <43AAAB58.6050109@logix-tt.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200512222144.32665.caseroj@comcast.net>

Agreed.  I have a 13 million row table that gets a 100,000 new records every 
week.   There are six indexes on this table.   Right about the time when it 
reached the 10 million row mark updating the table with new records started 
to take many hours if I left the indexes in place during the update.   Indeed 
there was even some suspicion that the indexes were starting to get corrupted 
during the load.  So I decided to fist drop the indexes when I needed to 
update the table.  Now inserting 100,000 records into the table is nearly 
instantaneous although it does take me a couple of hours to build the indexes 
anew.   This is still big improvement since at one time it was taking almost 
12 hours to update the table with the indexes in place.  


Juan

On Thursday 22 December 2005 08:34, Markus Schaber wrote:
> Hi, Madison,
> Hi, Luke,
>
> Luke Lonergan wrote:
> > Note that indexes will also slow down loading.
>
> For large loading bunches, it often makes sense to temporarily drop the
> indices before the load, and recreate them afterwards, at least, if you
> don't have normal users accessing the database concurrently.
>
> Markus