Re: [PERFORM] Another question on indexes (drop and recreate)
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 Mail: [EMAIL PROTECTED] - 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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Another question on indexes (drop and recreate)
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 =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Another question on indexes (drop and recreate)
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 Mail: [EMAIL PROTECTED] - 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Another question on indexes (drop and recreate)
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 =:-> signature.asc Description: OpenPGP digital signature
Re: [PERFORM] Another question on indexes (drop and recreate)
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 Mail: [EMAIL PROTECTED] - 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Another question on indexes (drop and recreate)
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 =:-> signature.asc Description: OpenPGP digital signature
