Continuous performance regression

[Przemyslaw Mazur]

Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.

[Nikolay A Mirin]

What's about reindexing your tables?
I doubt that vacuum takes care about it.
See the SQL command "REINDEX"
And I don't think doing full vacuum makes sense since the
space is not a crucial thing for you.
I would use VACUUM ANALYZE.

"Przemyslaw Mazur" <pm****@post.pl> wrote in message
news:ct**********@atlantis.news.tpi.pl...

Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.

[Przemyslaw Mazur]

THANK YOU VERY MUCH!

REINDEX was what I needed.

VACUUM FULL was an act of despair...

BR:
P.M.

"Nikolay A Mirin" <ni*****@mail.ru> wrote in message
news:gZ**************@newsread3.news.pas.earthlink .net...

What's about reindexing your tables?
I doubt that vacuum takes care about it.
See the SQL command "REINDEX"
And I don't think doing full vacuum makes sense since the
space is not a crucial thing for you.
I would use VACUUM ANALYZE.

"Przemyslaw Mazur" <pm****@post.pl> wrote in message
news:ct**********@atlantis.news.tpi.pl...

Hello.

I use postgress to manage a small amount of data (ca. 25MB), but often
changed.
This is a mirror of several tables taken form four different databases.

All data are updated once per hour (or sometimes on demand)

Update of every table is closes within a transaction (constant data
availability is most important):
BEGIN;
INSERT...
UPDATE...
UDPDATE...
COMMIT;

After all updated (every hour) I issue 'analyse' command.
Once a day, during night I issue 'vacuum full' command.

Since database creation I observe constant performance degradation during
update,
though data amount still persists the same.
At the beginning all updates take i.e. 1 minute
After 2 weeks the same update takes 4 minutes

When I make full backup, drop the database and recreate it from backup
everything starts from
the beginning (1 minute).

I tried this both on Postgres 7.4.x on Fedora2 (Pentium 2GHz/256Mb RAM)
and Solaris 2.6 (2x Ultra Sparc 400Mhz/ 512 Mb RAM) - on both the same
behaviour.

Any idea what I do wrong?
(such a bug in postgres engine is unbelivable)...

Regards:
P.M.