2013/12/10 Luis <luisico@xxxxxxxxx>:
> Hi,
>
> I have noticed a weird thing in different databases.
> When I run pg_database_size on some databases they report a pretty
> high value and if I take a look to all relations the sum of all of
> them is not even the half of the one reported by pg_database_size.
>
> Although I've seen this in different databases, this is a specific example.
>
>
> This are the sizes reported by this query:
>
> SELECT pg_size_pretty(pg_relation_size(C.oid)) AS "size"
> FROM pg_class C
> LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> ORDER BY pg_relation_size(C.oid) DESC
> LIMIT 20;
>
>
> size
> ---------
(…)
> And this is the database size reported by this query:
> SELECT pg_size_pretty(pg_database_size('mydb'));
>
> pg_size_pretty
> ----------------
> 3539 MB
>
> I've read about LOBs but I don't seem to have any.
>
> Any idea where the space is being used?
Indexes for a start. Try:
SELECT C.relname, pg_size_pretty(pg_relation_size(C.oid)) AS "size",
pg_size_pretty(pg_indexes_size(C.oid)) AS "idx_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE relkind='r'
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
Regards
Ian Barwick
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
