Thread: insertion times ..

insertion times ..

From
Anand Raman
Date:
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

Re: insertion times ..

From
"Gordan Bobic"
Date:
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
>
>


Re: insertion times ..

From
"Bryan White"
Date:
> 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.