Thread: 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
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 > >
> 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.