[GENERAL] Yet another Performance Question
Hi, 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. Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED] Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Yet another Performance Question
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. Postgres "suffers" the same problem which is very logical if you think about it. Inserts must adjust the indexes for every record and don't really know that there are a lot of other rows comming. Also, due to the multiuser nature of Postgres, other users could be accessing data between your rows of inserts and that data must be valid at that time. A non-indexed version of a table is just as accessible as an indexed one (though not as fast) so you have to decide if it's better to slow down a query or two while you insert/index or spend much more time having a good index after each insert. What would be nice is a simple "disable indexes on this (these) tables" command. The enable indexes command would then do a vacuum analyze on the effected tables when you were done. This would make sure that ALL of the indexes got rebuilt (I occationally forget an index when doing it "by hand"). len morgan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Yet another Performance Question
On Wed, Apr 18, 2001 at 07:18:40AM -0500, Len Morgan 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. Postgres "suffers" the same problem which is very logical if you think about it. Inserts must adjust the indexes for every record and don't really know that there are a lot of other rows comming. Also, due to the multiuser nature of Postgres, other users could be accessing data between your rows of inserts and that data must be valid at that time. A non-indexed version of a table is just as accessible as an indexed one (though not as fast) so you have to decide if it's better to slow down a query or two while you insert/index or spend much more time having a good index after each insert. Frankly what matters is the time it takes alltogether. I have a script that first does a few million inserts, and than queries on this data... these queries don't terminate in a reasonable (10days) time, if I don't use indices. So I will add some drop/create index-commands to the script... Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED] Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] 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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Yet another Performance Question
Hi! I'm not 100% sure, but I think it would be much faster if you use COPY instead of INSERT when you read in a lot of data from a file. Regards, Patrik Kudo -- ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Knns det oklart? Frga p! On Wed, 18 Apr 2001, Konstantinos Agouros wrote: Frankly what matters is the time it takes alltogether. I have a script that first does a few million inserts, and than queries on this data... these queries don't terminate in a reasonable (10days) time, if I don't use indices. So I will add some drop/create index-commands to the script... Konstantin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Yet another Performance Question
On Wed, Apr 18, 2001 at 02:59:53PM +0200, Patrik Kudo wrote: Hi! I'm not 100% sure, but I think it would be much faster if you use COPY instead of INSERT when you read in a lot of data from a file. Well unless I am the database owner I only can do \copy and the script does some manipulation before it inserts... Konstantin Regards, Patrik Kudo -- ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Knns det oklart? Frga p! On Wed, 18 Apr 2001, Konstantinos Agouros wrote: Frankly what matters is the time it takes alltogether. I have a script that first does a few million inserts, and than queries on this data... these queries don't terminate in a reasonable (10days) time, if I don't use indices. So I will add some drop/create index-commands to the script... Konstantin -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED] Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Yet another Performance Question
Konstantinos Agouros [EMAIL PROTECTED] said: On Wed, Apr 18, 2001 at 02:59:53PM +0200, Patrik Kudo wrote: Hi! I'm not 100% sure, but I think it would be much faster if you use COPY instead of INSERT when you read in a lot of data from a file. Well unless I am the database owner I only can do copy and the script does some manipulation before it inserts... Don't forget you can do \copy from stdin so you can pipe the output of your script to psql. Failing that, try batching inserts in transactions, a few hundred at a time. That can speed things up. You'll need to find what figure works best for you. - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Yet another Performance Question
Have you tried running PostGres with the -F option so that disk flushes are not performed every time. Maybe this will make a difference to insert performance. NB In 7.1 this is replaced by WAL which I don't know much about MC -- NOTICE: The information contained in this electronic mail transmission is intended by Convergys Corporation for the use of the named individual or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone (collect), so that the sender's address records can be corrected. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Yet another Performance Question
In [EMAIL PROTECTED] [EMAIL PROTECTED] writes: Have you tried running PostGres with the -F option so that disk flushes are not performed every time. Maybe this will make a difference to insert performance. In the DBI-connect I still have a -F in the options, but now I am running 7.1RC2 (didn't have the time to do the upgrade to final yet). If this is called differently now, please let me know NB In 7.1 this is replaced by WAL which I don't know much about MC -- NOTICE: The information contained in this electronic mail transmission is intended by Convergys Corporation for the use of the named individual or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone (collect), so that the sender's address records can be corrected. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: [EMAIL PROTECTED] Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185 "Captain, this ship will not sustain the forming of the cosmos." B'Elana Torres ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
