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@xxxxxxxxxxxxxx
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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
