Hi All,
Please, see more info my env: PG 8.3.6 on RHE5-64bits.
1. there are more than one schemas, but the size of the tables is close
to 30-40kB, see some samples
schemaname | tablename | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
tap_schema | tables | 8192 bytes | 32 kB
tap_schema | columns | 8192 bytes | 32 kB
tap_schema | schemas | 8192 bytes | 32 kB
tap_schema | keys | 8192 bytes | 24 kB
tap_schema | key_columns | 8192 bytes | 8192 bytes
2. There are no BLOB's data type in db at this time :
cvodb=# select * from pg_largeobject;
loid | pageno | data
------+--------+------
(0 rows)
3. As Tom suggested , I excluded the table space restriction and
changed to pg_total_relation_size my original SQL :
SELECT 'the table size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
relname)) AS "s
ize"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
the table size without table space restrictions
(1 row)
relation | size
---------------------------+---------
caom.spatialentity | 3216 MB
caom.artifact | 2150 MB
caom.plane | 677 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation | 202 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 219 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 86 MB
caom.spectralentity | 73 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1 | 58 MB
caom.plane_position_i2 | 48 MB
caom.metric | 70 MB
caom.polarizationentity | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2 | 18 MB
caom.metric_i1 | 18 MB
caom.plane_position_i3 | 15 MB
(20 rows)
4. Where are the rest of 5,5 GB been used ? How can I get the system
catalog correct size ?
Thank you,
Isabella
Tom Lane wrote:
Isabella Ghiurea <isabella.ghiurea@xxxxxxxxxxxxxx> writes:
> 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. .
You are not counting everything --- the total DB size is clearly 12GB,
so the question is where are the other 5.5GB? Your first query shows
that schema caom accounts for 6+GB, but the second one does not prove
that schema caom contains all the big hogs. My guesses are:
1. Toast tables for tables that aren't in caom --- you used
pg_relation_size not pg_total_relation_size, and excluded toast
tables, so you are missing those.
2. pg_largeobject ... got any large objects?
3. Bloat in other system catalogs. 5GB of catalog bloat would be
pretty awful, but maybe that's what it is.
Try that last query without the namespace restrictions.
regards, tom lane
--
-----------------------------------------------------------
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
