Re: Yet another Performance Question

On Wed, Apr 18, 2001 at 12:55:07PM +0200, Konstantinos Agouros wrote:

> I once read in Oracle Performance Tuning, that if one inserts or changes large
> amounts of data in a table, it might be better to drop indices before doing
> this and recreating them afterwards. Could someone give a hint on how this
> is in Postgres 7.1? Currently I am experiencing a massive slowdown in importing
> data.

I use a little script that create two sql files for dropping and recreating
indexes of a database. Sxript is for "es" shell, but it is easily adaptable
to sh:

------------------------------------------------------------------------

#!/usr/bin/es
#
# David Espada 2000.
#

BD = multi # Name of database.
QUERY = 'select indexdef from pg_indexes;'
F_CREA = create_indices.sql
F_BORRA = drop_indices.sql

psql $BD -c $QUERY | grep -i 'create' | grep -v 'pg_.*_index' | awk '{print $0, ";"}' > $F_CREA

awk '{ gsub(/CREATE.*INDEX/, "DROP INDEX"); print $1, $2, $3, ";" }' $F_CREA > $F_BORRA

-------------------------------------------------------------------------

When executing script, you have two files that can use like:

$ psql -f drop_indices.sql
$ psql -f create_indices.sql

I hope it helps you.

Greets.

David