On Thu, May 19, 2011 at 1:05 AM, Andreas Kretschmer < akretsch...@spamfence.net> wrote:
> Georgi Ivanov <georgi.r.iva...@gmail.com> wrote: > > > Hi, > > I wander what is taking up my space on disk ... > > > > btv=# SELECT pg_size_pretty(pg_database_size('btv_good')); > > pg_size_pretty > > ---------------- > > 10 GB > > (1 row) > > > > > > btv=# SELECT nspname || '.' || relname AS "relation", > > pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" > > FROM pg_class C > > LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) > > WHERE nspname NOT IN ('pg_catalog', 'information_schema') > > AND C.relkind <> 'i' > > AND nspname !~ '^pg_toast' > > ORDER BY pg_total_relation_size(C.oid) DESC > > LIMIT 15; > > relation | total_size > > --------------------------------------+------------ > > users.users | 703 MB > > btv.material | 557 MB > > btv_admin.material | 269 MB > > btv_admin.block | 24 MB > > btv.block | 20 MB > > btv_admin.block_list | 9136 kB > > btv.block_list | 9112 kB > > multimedia.rel_image_collection2size | 2984 kB > > multimedia.rel_image_collection2tag | 1024 kB > > btv_admin.block_common | 976 kB > > multimedia.image_collection | 936 kB > > btv.block_common | 832 kB > > users_admin.invalidate_notify | 752 kB > > btv_admin.tv_program | 656 kB > > btv.rel_material2tag | 592 kB > > (15 rows) > > > > The sum of biggest tables is not even close to the total db size . > > Some index going wild ? > > Your sum doesn't contains indexes and toast-tables. > > > Andreas > -- > Really, I'm not out to destroy Microsoft. That will just be a completely > unintentional side effect. (Linus Torvalds) > "If I was god, I would recompile penguin with --enable-fly." (unknown) > Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Since he used pg_total_relation_size(), according to the manual ( http://www.postgresql.org/docs/current/interactive/functions-admin.html) indexes and toast should be included in the numbers reported for the tables. Unfortunately, I don't have any insight as to why pg_database_size() is returning a number roughly 5x larger than the sum of pg_total_relation_size() here. -Eric