pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAnother question on indexes (drop and recreate)
7+ messages / 2 participants
[nested] [flat]
* Another question on indexes (drop and recreate)
@ 2005-06-21 15:17 Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-21 15:17 UTC (permalink / raw)
To: pgsql-performance
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
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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 15:22 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-06-21 15:22 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 15:28 Yves Vindevogel <yves.vindevogel@implements.be>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-21 15:28 UTC (permalink / raw)
To: pgsql-performance
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: 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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 15:49 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-06-21 15:49 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 16:43 Yves Vindevogel <yves.vindevogel@implements.be>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-21 16:43 UTC (permalink / raw)
To: pgsql-performance
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: 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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 16:54 John A Meinel <john@arbash-meinel.com>
parent: Yves Vindevogel <yves.vindevogel@implements.be>
0 siblings, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-06-21 16:54 UTC (permalink / raw)
To: Yves Vindevogel <yves.vindevogel@implements.be>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Another question on indexes (drop and recreate)
@ 2005-06-21 19:30 Yves Vindevogel <yves.vindevogel@implements.be>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 7+ messages in thread
From: Yves Vindevogel @ 2005-06-21 19:30 UTC (permalink / raw)
To: pgsql-performance
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: 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.
Attachments:
Pasted Graphic 2.tiff (image/tiff, 5.6K) download
view image
end of thread, other threads:[~2005-06-21 19:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-21 15:17 Another question on indexes (drop and recreate) Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 15:22 ` John A Meinel <john@arbash-meinel.com>
2005-06-21 15:28 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 15:49 ` John A Meinel <john@arbash-meinel.com>
2005-06-21 16:43 ` Yves Vindevogel <yves.vindevogel@implements.be>
2005-06-21 16:54 ` John A Meinel <john@arbash-meinel.com>
2005-06-21 19:30 ` Yves Vindevogel <yves.vindevogel@implements.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox