Thread

stephane tessier <stephanetessier911@yahoo.fr> — 2002-10-18T13:58:50Z

Hi,

i have a problem with index in pgsql 7.2.1(i think it's indexes the problem). I insert a lot of records in a table and delete in a ratio of 1:5... When I do request on this table..it's working fine for 10-12 hours but after it's very slow (10-15 minutes) but when I delete the indexes and recreate it.. it's ok for 10-12 hours again.. it's a live db 24/7... I cannot stop the db often... process insert record all the time...

have idea? pgsql problem?

 

thanks

 

Stephane



---------------------------------
Yahoo! Mail -- Une adresse @yahoo.fr gratuite et en français !

Re:

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-21T14:30:46Z

On 18 Oct 2002 at 15:58, stephane tessier wrote:

> 
> Hi,
> i have a problem withindex in pgsql 7.2.1(i think it's indexes the problem). I 
> insert a lot of records in a table and delete in a ratio of 1:5... When I do 
> request on this table..it's working fine for 10-12 hours but after it's very 
> slow (10-15 minutes) but when I delete the indexes and recreate it.. it's ok 
> for 10-12 hours again.. it's a live db 24/7... I cannot stop the db often... 
> process insert record all the time...
> have idea? pgsql problem?

You should do a vacuum analyze the db often. Something like for each 5000 
deletes+insert. Put it in a cron job if required.

And insertion in indexed table is always going to be slower for large amount of 
data. There is no way out..

HTH

Bye
 Shridhar

--
Mustgo, n.:	Any item of food that has been sitting in the refrigerator so	long 
it has become a science project.		-- Sniglets, "Rich Hall & Friends"

Re:

Doug McNaught <doug@wireboard.com> — 2002-10-21T15:20:55Z

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:

> On 18 Oct 2002 at 15:58, stephane tessier wrote:
> 
> > i have a problem withindex in pgsql 7.2.1(i think it's indexes the
> > problem). I insert a lot of records in a table and delete in a
> > ratio of 1:5... When I do request on this table..it's working fine
> > for 10-12 hours but after it's very slow (10-15 minutes) but when
> > I delete the indexes and recreate it.. it's ok for 10-12 hours
> > again.. it's a live db 24/7... I cannot stop the db often...
> > process insert record all the time...  have idea? pgsql problem?
> 
> You should do a vacuum analyze the db often. Something like for each 5000 
> deletes+insert. Put it in a cron job if required.

He should probably also REINDEX the database every night, since VACUUM
doesn't handle all types of index growth.

-Doug