From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: size of table + toasted tables + indexes !=
 pg_total_relation_size
In-Reply-To: 
 <CA+t6e1mtdVct+Cn=qs=q=LLL_yKSssO6dxiZk+b16xq4ccvWvw@mail.gmail.com>
	(Mariel Cherkassky's message of "Sun, 4 Nov 2018 12:14:04 +0200")
Message-ID: <87pnvl2gki.fsf@news-spur.riddles.org.uk>
References: 
 <CA+t6e1mtdVct+Cn=qs=q=LLL_yKSssO6dxiZk+b16xq4ccvWvw@mail.gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Sun, 04 Nov 2018 12:12:41 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "Mariel" == Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:

 Mariel> output : 
 Mariel> image.png

In future please use plain text for query output, not screenshots

 Mariel> When I searched for the biggest objects in DB1 I saw that the
 Mariel> total sum isnt even close to 100G :

You should probably check whether there are files in the db's directory
that do not correspond to the filenode of any table (use
select pg_relation_filenode(oid) from pg_class; to get a list of
filenodes)

Crashes (or other unclean shutdown) in the middle of doing a vacuum full
or other table-rewriting operation is known to leave orphaned files
around in the data dir.

 Mariel> Now, I thought that the pg_total_relation_size should include
 Mariel> also all the dead tuples and the toasted tables.

It does, and your results agree with that. Perhaps you missed that
pg_table_size() includes the toast table too?

 Mariel> In addition, I saw from pg_stat_activity that the session table
 Mariel> has about 198 dead tuples and the toasted table has about 83833
 Mariel> dead tuples. It seems that the autoanalyze has never run on the
 Mariel> toasted table.

There's nothing in a toast table that needs analyzing.

-- 
Andrew (irc:RhodiumToad)