|
Home > Archive > PostgreSQL Performance > June 2005 > Re: Another question on indexes (drop and recreate)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Another question on indexes (drop and recreate)
|
|
| John A Meinel 2005-06-21, 11:23 am |
| 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
=:->
| |
| John A Meinel 2005-06-21, 11:23 am |
| 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
=:->
| |
| John A Meinel 2005-06-21, 1:24 pm |
| 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
=:->
|
|
|
|
|