Re: Difference in the size of database size and relations

Thanks Ian,

Actually the query I used to get the relations sizes also included
indexes and even pg_toast. This query gives me more detail about the
indexes related to every table but still can't see where the space is.

    size    | idx_size
------------+----------
 45 MB      | 15 MB
 8528 kB    | 1360 kB
 4352 kB    | 752 kB
 3184 kB    | 144 kB
 1304 kB    | 832 kB
 896 kB     | 1192 kB
 248 kB     | 344 kB
 80 kB      | 120 kB
 8192 bytes | 16 kB
 0 bytes    | 16 kB
 0 bytes    | 16 kB


Thanks

2013/12/10 Ian Lawrence Barwick <barwick@gmail.com>:
> 2013/12/10 Luis <luisico@gmail.com>:
>> 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