Thread

1. insertion times ..

   Anand Raman <araman@india-today.com> — 2000-11-29T12:44:16Z

   hi guys
   
   I am trying to create a search facility for our site using udm search..
   While reading about udm search along with postgresql i cam across this
   line in one of the mailing list archived mails.
   
   ****************************************
   > I don't know what kind of queries are executed during the indexing, if
   > the insertions don't need indexes you could try to drop the index and
   > recreate them when the indexing is done.
   
   THIS SHOULD SIGNIFICANTLY INCREASE INDEXING TIME.
   ****************************************
   
   Is it true that creating indexes after the insertion has been done takes
   more time..
   If so why ..
   
   Thanx
   Anand
   
   

2. Re: insertion times ..

   Gordan Bobic <gordan@freeuk.com> — 2000-11-29T13:36:06Z

   My understanding (an experience) indicate that inserting about 100,000
   records and then creating an index is faster than creating the index, then
   inserting the records, because the index has to be updated every time a
   record is inserted. By creating the index afterwards, you only have one big
   index creation which is more efficient than doing them one by one...
   
   Or at least that seems to be the case for my database...
   
   ----- Original Message -----
   From: "Anand Raman" <araman@india-today.com>
   To: <pgsql-general@postgresql.org>
   Sent: Wednesday, November 29, 2000 12:44 PM
   Subject: [GENERAL] insertion times ..
   
   
   > hi guys
   >
   > I am trying to create a search facility for our site using udm search..
   > While reading about udm search along with postgresql i cam across this
   > line in one of the mailing list archived mails.
   >
   > ****************************************
   > > I don't know what kind of queries are executed during the indexing, if
   > > the insertions don't need indexes you could try to drop the index and
   > > recreate them when the indexing is done.
   >
   > THIS SHOULD SIGNIFICANTLY INCREASE INDEXING TIME.
   > ****************************************
   >
   > Is it true that creating indexes after the insertion has been done takes
   > more time..
   > If so why ..
   >
   > Thanx
   > Anand
   >
   >
   
   
   

3. Re: insertion times ..

   Bryan White <bryan@arcamax.com> — 2000-11-29T15:11:26Z

   > I am trying to create a search facility for our site using udm search..
   > While reading about udm search along with postgresql i cam across this
   > line in one of the mailing list archived mails.
   >
   > ****************************************
   > > I don't know what kind of queries are executed during the indexing, if
   > > the insertions don't need indexes you could try to drop the index and
   > > recreate them when the indexing is done.
   >
   > THIS SHOULD SIGNIFICANTLY INCREASE INDEXING TIME.
   > ****************************************
   >
   > Is it true that creating indexes after the insertion has been done takes
   > more time..
   > If so why ..
   
   Creating indexes will obviouly take longer on a populated table then an
   empty table.  However the total time for insert then index will be less then
   the time to index then insert.
   
   Note that the COPY command (as used in dump/reload) is faster than INSERT.
   
   If you must use INSERT then group the inserts in a transaction.  This will
   speed it up.  I don't know if there is a problem with huge numbers of
   INSERTs in a single transaction.  I would create a separate transaction for
   every few hundred records.