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
Date: Sun, 04 Nov 2018 12:12:41 +0000
Message-ID: <87pnvl2gki.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <CA+t6e1mtdVct+Cn=qs=q=LLL_yKSssO6dxiZk+b16xq4ccvWvw@mail.gmail.com>
References: <CA+t6e1mtdVct+Cn=qs=q=LLL_yKSssO6dxiZk+b16xq4ccvWvw@mail.gmail.com>
>>>>> "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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: andrew@tao11.riddles.org.uk, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: size of table + toasted tables + indexes != pg_total_relation_size
In-Reply-To: <87pnvl2gki.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox