Re: Speeding up query

> You really could do with updating that; 8.1.4 is very old.  8.1.15 is
> the latest in the 8.1 series and has lots of bug fixes.

Will update increase speed ?
Server is running for approx 4 years now and I havent encountered any bugs.

>> Db size is 862 MB
>>
>> Bigger tables:
>>      1         1214 pg_shdepend                         775 MB
>>      2         1232 pg_shdepend_depender_index          285 MB
>>      5         1233 pg_shdepend_reference_index         156 MB
>
> those look scary, scary big to me.  Have you been running without
> autovacuum for a while and creating *lots* of tables or something?

Log file shows many messages

autovacuum: processing database "mydb" every day.

So I expect it is running.

After VACUUM ANALYZE I ran

VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb

after that I got

      1         1214 pg_shdepend                         440 MB
      2         1232 pg_shdepend_depender_index          285 MB
      3         1233 pg_shdepend_reference_index         155 MB
      4        19701 rid                                 103 MB
      5        19301 bilkaib                             93 MB
      6        19335 dok                                 46 MB


> Your database looks quite bloated; if you can afford the downtime I'd be
> tempted to do a full backup and restore.  This will reduce bloat a lot
> and also provide a good opportunity to update PG.  The good thing about
> doing it from a restore is that you don't have to go through REINDEXing
> everything by hand and potentially miss lots of things out.  If things
> are going to shrink a lot, restoring is normally quicker as well.
>
> A good way to test would be to do a backup and see how big the resulting
> file is.  I'd expect the database to be three or four times the size of
> the plain text backup (depending on table design and index use it can
> vary quite a bit either way), so if the dump is less than a hundred MB
> you're probably better off doing a restore.

I have acces to this db only from port 5432
Thus Text backup takes a lot of time and server upgrade is not possible.

I ran

VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb

and hope this produces the same results and backup/restore.

Andrus.