Re: Speeding up query

On Wed, Nov 05, 2008 at 09:52:29PM +0200, Andrus wrote:
> >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.

Not much for speed I don't think, the main reason for upgrading is that
it should fix a few cases where it can corrupt itself. Have a read
through here:

http://www.postgresql.org/docs/8.1/static/release.html

> Log file shows many messages
>
> autovacuum: processing database "mydb" every day.
>
> So I expect it is running.

A normal VACUUM only takes care of marking deleted/updated data as
being available for reuse. If you insert lots of data and then delete
it again, this space will remain marked for reuse (assuming your FSM
settings are large enough) even though it's never going to be reused.
In these cases it's good to do a FULL vacuum, but normally it's best to
just leave the autovacuum doing its thing.

Not sure what's going on with the "pg_shdepend" table+indexes at all
though, or even if it's bad!

> VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb

Hum, I'd never noticed the REINDEX DATABASE command before! that should
save a bit of fiddling.

> 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

these all still seem quite big, at least in my (somewhat limited)
experience. If anyone else has experience of what would cause these to
grow I'd be interested in finding out!

> 4 19701 rid 103 MB
> 5 19301 bilkaib 93 MB
> 6 19335 dok 46 MB

So, performance of these should be a bit better. A seqscan of half the
data should take half the time...

Sam