When you upgrade to PostgreSQL 9.2, run vacuum right away.

Thursday, December 13. 2012

When you upgrade to PostgreSQL 9.2, run vacuum right away.

One of PostgreSQL's nice new features in the 9.2 release is index-only scans. However, it's only likely to be used when the table's relallvisible setting in pg_class has been set, and unfortunately the ANALYSE command doesn't do this - only VACUUM does. So, if you want index-only scans to work right after the upgrade (whether this is done by importing a dump or by running pg_upgrade), you should probably run a database-wide VACUUM ANALYSE as soon as your new database is available. Otherwise you'll have to wait until autovacuum kicks in, assuming you have it enabled, or your next vacuum cycle.

Posted by Andrew Dunstan in PostgreSQL at 17:11 | Comments (3) | Trackbacks (0)

Trackbacks

Trackback specific URI for this entry

No Trackbacks

Comments

Display comments as (Linear | Threaded)

s/ANALYSE/ANALYZE/g

#1 Andreas on 2012-12-13 20:22 (Reply)

No, you're actually allowed to spell it both ways. Try it and see. And since I was brought up in Australia, which generally follows British spelling, I habitually spell it the British way.

#1.1 Andrew Dunstan on 2012-12-13 20:36 (Reply)

Ihad no idea about that, thanks!

#1.1.1 Andreas on 2012-12-14 02:49 (Reply)

Add Comment

Name
Email
Homepage
In reply to
Comment	Enclosing asterisks marks text as bold (word), underscore are made via _word_. Standard emoticons like :-) and ;-) are converted to images. Wiki format allowed
	Remember Information? Subscribe to this entry