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(at)gmail(dot)com>:
> 2013/12/10 Luis <luisico(at)gmail(dot)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