Re: Vacuum, analyze, and setting reltuples of pg_class

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Tom Lane replied:
>> Short version: is it optimal for vacuum to always populate reltuples
>> with live rows + dead rows?

> If we didn't do that, it would tend to encourage the use of seqscans on
> tables with lots of dead rows, which is probably a bad thing.

Bleh. Isn't that what a plain analyze would encourage then? Should analyze
be considering the dead rows somehow as well?

>> Is there any way to encourage those dead rows to go away,

> Close your open transactions.

There are no long-running transactions running, but it is a very busy database,
so the chances of something else on the cluster being in a transaction at
any point in time is very high. Still, why would an open transaction elsewhere
block other databases / other tables for a vacuum full? E.g.:

prod=# create database gtest;
CREATE DATABASE

prod=# \c gtest
You are now connected to database "gtest".

gtest=# create table gtest(a int);
CREATE TABLE

gtest=# insert into gtest select 1 from generate_series(1,10);
INSERT 0 10

gtest=# delete from gtest;
DELETE 10

gtest=# vacuum full gtest;
VACUUM

gtest=# analyze verbose gtest;
INFO:  analyzing "public.gtest"
INFO:  "gtest": scanned 1 of 1 pages, containing 0 live rows and      10 dead rows; 0 rows in sample, 0 estimated total
rows
ANALYZE

This is 8.1.3, by the way. At the very least, I'll submit a doc patch at
the end of all this. :)

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200612111226
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFfZYLvJuQZxSWSsgRAmeDAKCPK2h9trzLn+1V6yN7cUjsnd/3VwCfT3Il
hdCrUGCVso01xkDRDKLUlpI=
=VOrr
-----END PGP SIGNATURE-----