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.