Re: Yet another Performance Question

From: "Len Morgan" <len-morgan(at)crcom(dot)net>
To: <pgsql-general(at)postgresql(dot)org>, "Konstantinos Agouros" <elwood(at)agouros(dot)de>
Subject: Re: Yet another Performance Question
Date: 2001-04-18 12:18:40
Message-ID: 000f01c0c801$b50378c0$0908a8c0@H233.bstx.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

>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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Konstantinos Agouros 2001-04-18 12:27:10 Re: Yet another Performance Question
Previous Message Konstantinos Agouros 2001-04-18 10:55:07 Yet another Performance Question