db size and tables size difference - Mailing list pgsql-admin

Hi Pg Admin list.
I'm trying to understand why  there are  GB's difference  when checking
for db size using pg_size_pretty()  and  querying for  tables + indexes
size. .
The  sum  of tables +index sizes is  showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this  are the results after a
full vacuum and reindexdb,  also the sum of OS db files size is ~ 6.5GB.
Any tips what I'm missing : are some  "unallocated" db pages or anything
else ?

select pg_size_pretty(pg_database_size('db1'));
 pg_size_pretty
----------------
 12 GB
(1 row)
  *** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname |          tablename          | size_pretty | total_size_pretty
------------+-----------------------------+-------------+-------------------
 caom       | spatialentity               | 3053 MB     | 3216 MB
 caom       | artifact                    | 1979 MB     | 2150 MB
 caom       | plane                       | 413 MB      | 677 MB
 caom       | positionsample              | 110 MB      | 219 MB
 caom       | simpleobservation           | 165 MB      | 202 MB
 caom       | temporalentity              | 77 MB       | 86 MB
 caom       | spectralentity              | 68 MB       | 73 MB
 caom       | metric                      | 33 MB       | 70 MB
 caom       | polarizationentity          | 29 MB       | 33 MB
 caom       | harvestskip                 | 1576 kB     | 2056 kB
 caom       | harveststate                | 840 kB      | 856 kB
 caom       | positionhole                | 48 kB       | 584 kB




 *** OR  : check for the biggest tables+index size:
SELECT ' Top 20  biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND nspname !~ '^pg_toast'
    AND pg_relation_size(nspname || '.' || relname)>0
  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
  LIMIT 20;
(1 row)

         relation          |      size
---------------------------+---------
 caom.spatialentity        |  3053 MB
 caom.artifact             |   1979 MB
 caom.plane                |    413 MB
 caom.artifact_i1          |  171 MB
 caom.simpleobservation    | 165 MB
 caom.spatialentity_i1     | 162 MB
 caom.positionsample       | 110 MB
 caom.plane_psi2           | 86 MB
 caom.temporalentity       | 77 MB
 caom.spectralentity       | 68 MB
 caom.plane_energy_i1      | 67 MB
 caom.plane_time_i1        | 58 MB
 caom.plane_position_i2    | 48 MB
 caom.metric               | 33 MB
 caom.polarizationentity   | 29 MB
 caom.simpleobservation_i2 | 25 MB
 caom.plane_psi1           | 23 MB
 caom.metric_i2            | 18 MB

Thank you
Isabella

--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045