Обсуждение: Another question on indexes (drop and recreate)
Hi, I have another question regarding indexes. I have a table with a lot of indexes on it. Those are needed to perform my searches. Once a day, a bunch of records is inserted in my table. Say, my table has 1.000.000 records and I add 10.000 records (1% new) What would be faster. 1) Dropping my indexes and recreating them after the inserts 2) Just inserting it and have PG manage the indexes Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>Hi, I have another question regarding indexes. I have a table with a lot of indexes on it. Those are needed to perform my searches. Once a day, a bunch of records is inserted in my table. Say, my table has 1.000.000 records and I add 10.000 records (1% new) What would be faster. 1) Dropping my indexes and recreating them after the inserts 2) Just inserting it and have PG manage the indexes Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Вложения
Yves Vindevogel wrote: > Hi, > > I have another question regarding indexes. > > I have a table with a lot of indexes on it. Those are needed to > perform my searches. > Once a day, a bunch of records is inserted in my table. > > Say, my table has 1.000.000 records and I add 10.000 records (1% new) > What would be faster. > > 1) Dropping my indexes and recreating them after the inserts > 2) Just inserting it and have PG manage the indexes > > Met vriendelijke groeten, > Bien à vous, > Kind regards, > > *Yves Vindevogel* > *Implements* I'm guessing for 1% new that (2) would be faster. John =:->
Вложения
And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it. On 21 Jun 2005, at 17:22, John A Meinel wrote: <excerpt>Yves Vindevogel wrote: <excerpt>Hi, I have another question regarding indexes. I have a table with a lot of indexes on it. Those are needed to perform my searches. Once a day, a bunch of records is inserted in my table. Say, my table has 1.000.000 records and I add 10.000 records (1% new) What would be faster. 1) Dropping my indexes and recreating them after the inserts 2) Just inserting it and have PG manage the indexes Met vriendelijke groeten, Bien à vous, Kind regards, *Yves Vindevogel* *Implements* </excerpt> I'm guessing for 1% new that (2) would be faster. John =:-> </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it. On 21 Jun 2005, at 17:22, John A Meinel wrote: > Yves Vindevogel wrote: > >> Hi, >> >> I have another question regarding indexes. >> >> I have a table with a lot of indexes on it. Those are needed to >> perform my searches. >> Once a day, a bunch of records is inserted in my table. >> >> Say, my table has 1.000.000 records and I add 10.000 records (1% new) >> What would be faster. >> >> 1) Dropping my indexes and recreating them after the inserts >> 2) Just inserting it and have PG manage the indexes >> >> Met vriendelijke groeten, >> Bien à vous, >> Kind regards, >> >> *Yves Vindevogel* >> *Implements* > > > I'm guessing for 1% new that (2) would be faster. > John > =:-> > > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Вложения
Yves Vindevogel wrote: > And, after let's say a week, would that index still be optimal or > would it be a good idea to drop it in the weekend and recreate it. It depends a little bit on the postgres version you are using. If you are only ever adding to the table, and you are not updating it or deleting from it, I think the index is always optimal. Once you start deleting from it there are a few cases where older versions would not properly re-use the empty entries, requiring a REINDEX. (Deleting low numbers and always adding high numbers was one of the cases) However, I believe that as long as you vacuum often enough, so that the system knows where the unused entries are, you don't ever have to drop and re-create the index. John =:->
Вложения
I only add records, and most of the values are "random" Except the columns for dates, .... On 21 Jun 2005, at 17:49, John A Meinel wrote: <excerpt>Yves Vindevogel wrote: <excerpt>And, after let's say a week, would that index still be optimal or would it be a good idea to drop it in the weekend and recreate it. </excerpt> It depends a little bit on the postgres version you are using. If you are only ever adding to the table, and you are not updating it or deleting from it, I think the index is always optimal. Once you start deleting from it there are a few cases where older versions would not properly re-use the empty entries, requiring a REINDEX. (Deleting low numbers and always adding high numbers was one of the cases) However, I believe that as long as you vacuum often enough, so that the system knows where the unused entries are, you don't ever have to drop and re-create the index. John =:-> </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>I only add records, and most of the values are "random" Except the columns for dates, .... On 21 Jun 2005, at 17:49, John A Meinel wrote: > Yves Vindevogel wrote: > >> And, after let's say a week, would that index still be optimal or >> would it be a good idea to drop it in the weekend and recreate it. > > > It depends a little bit on the postgres version you are using. If you > are only ever adding to the table, and you are not updating it or > deleting from it, I think the index is always optimal. > Once you start deleting from it there are a few cases where older > versions would not properly re-use the empty entries, requiring a > REINDEX. (Deleting low numbers and always adding high numbers was one > of > the cases) > > However, I believe that as long as you vacuum often enough, so that the > system knows where the unused entries are, you don't ever have to drop > and re-create the index. > > John > =:-> > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.
Вложения
Yves Vindevogel wrote: > I only add records, and most of the values are "random" > Except the columns for dates, .... I doubt that you would need to recreate indexes. That really only needs to be done in pathological cases, most of which have been fixed in the latest postgres. If you are only inserting (never updating or deleting), the index can never bloat, since you are only adding new stuff. (You cannot get dead items to bloat your index if you never delete anything.) John =:->
Вложения
Ok, tnx !! On 21 Jun 2005, at 18:54, John A Meinel wrote: <excerpt>Yves Vindevogel wrote: <excerpt>I only add records, and most of the values are "random" Except the columns for dates, .... </excerpt> I doubt that you would need to recreate indexes. That really only needs to be done in pathological cases, most of which have been fixed in the latest postgres. If you are only inserting (never updating or deleting), the index can never bloat, since you are only adding new stuff. (You cannot get dead items to bloat your index if you never delete anything.) John =:-> </excerpt>Met vriendelijke groeten, Bien à vous, Kind regards, <bold>Yves Vindevogel</bold> <bold>Implements</bold> <smaller> </smaller>Ok, tnx !! On 21 Jun 2005, at 18:54, John A Meinel wrote: > Yves Vindevogel wrote: > >> I only add records, and most of the values are "random" >> Except the columns for dates, .... > > I doubt that you would need to recreate indexes. That really only needs > to be done in pathological cases, most of which have been fixed in the > latest postgres. > > If you are only inserting (never updating or deleting), the index can > never bloat, since you are only adding new stuff. > (You cannot get dead items to bloat your index if you never delete > anything.) > > John > =:-> > > Met vriendelijke groeten, Bien à vous, Kind regards, Yves Vindevogel Implements <smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be <italic><x-tad-smaller> First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.</x-tad-smaller></italic></smaller> Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91 Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76 Web: http://www.implements.be First they ignore you. Then they laugh at you. Then they fight you. Then you win. Mahatma Ghandi.