Re: db size and tables size difference

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 Hi All,
 Tom, please see bellow are the results for  the  modified query with ORDER
BY

select pg_size_pretty(pg_database_size('db1')); ---->  
pg_size_pretty
----------------
12 GB 

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_total_relation_size(nspname || '.' || relname) DESC
  LIMIT 1000;

------------------------------------------------------+------------
 caom.spatialentity                                   | 3216 MB
 caom.artifact                                        | 2150 MB
 caom.plane                                           | 677 MB
 caom.positionsample                                  | 219 MB
 caom.simpleobservation                               | 202 MB
 caom.artifact_i1                                     | 171 MB
 caom.spatialentity_i1                                | 162 MB
 caom.temporalentity                                  | 86 MB
 caom.plane_psi2                                      | 86 MB
 caom.spectralentity                                  | 73 MB
 caom.metric                                          | 70 MB
 caom.plane_energy_i1                                 | 67 MB
 caom.plane_time_i1                                   | 58 MB
 caom.plane_position_i2                               | 48 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_i1                                        | 15 MB
 caom.plane_position_i3                               | 15 MB
 caom.plane_polar_i1                                  | 15 MB
 caom.plane_time_i2                                   | 15 MB
 caom.plane_energy_i2                                 | 15 MB
 caom.plane_i2                                        | 15 MB
 caom.simpleobservation_i1                            | 12 MB
 caom.temporalentity_i1                               | 9496 kB
 caom.spectralentity_i1                               | 4384 kB
 caom.polarizationentity_i1                           | 4368 kB
 caom.harvestskip                                     | 2056 kB
 pg_catalog.pg_depend                                 | 1008 kB
 pg_catalog.pg_proc                                   | 880 kB
 caom.harveststate                                    | 856 kB
 pg_catalog.pg_attribute                              | 648 kB
 caom.positionhole                                    | 584 kB
 pg_catalog.pg_statistic                              | 576 kB
 caom.plane_phi2                                      | 496 kB
 caom.harvestskip_i1                                  | 480 kB
 pg_catalog.pg_proc_proname_args_nsp_index            | 328 kB
 pg_catalog.pg_operator                               | 296 kB
 pg_catalog.pg_description                            | 280 kB
 pg_catalog.pg_depend_depender_index                  | 264 kB
 pg_catalog.pg_depend_reference_index                 | 264 kB
 pg_catalog.pg_rewrite                                | 256 kB
 pg_catalog.pg_attribute_relid_attnam_index           | 240 kB
 pg_toast.pg_toast_2618                               | 160 kB
 pg_catalog.pg_type                                   | 144 kB
 pg_catalog.pg_class                                  | 136 kB
 pg_catalog.pg_amop                                   | 128 kB
 pg_catalog.pg_operator_oprname_l_r_n_index           | 112 kB
 pg_catalog.pg_description_o_c_o_index                | 96 kB
 pg_toast.pg_toast_2619                               | 96 kB
 pg_catalog.pg_constraint                             | 80 kB
 pg_catalog.pg_conversion                             | 80 kB
 pg_catalog.pg_amproc                                 | 72 kB
 pg_catalog.pg_attribute_relid_attnum_index           | 72 kB
 pg_catalog.pg_proc_oid_index                         | 72 kB
 pg_catalog.pg_opclass                                | 72 kB
 pg_catalog.pg_trigger                                | 56 kB
 pg_catalog.pg_type_typname_nsp_index                 | 56 kB
 pg_catalog.pg_index                                  | 56 kB
 information_schema.sql_features                      | 48 kB
 pg_catalog.pg_cast                                   | 48 kB
 pg_catalog.pg_class_relname_nsp_index                | 48 kB
 pg_catalog.pg_database                               | 48 kB
 pg_catalog.pg_authid                                 | 48 kB
 pg_catalog.pg_ts_config_map                          | 48 kB
 pg_catalog.pg_opfamily                               | 48 kB
 pg_catalog.pg_language                               | 40 kB
 pg_catalog.pg_shdepend                               | 40 kB
 pg_catalog.pg_ts_parser                              | 40 kB
 pg_catalog.pg_ts_template                            | 40 kB
 pg_catalog.pg_auth_members                           | 40 kB
 pg_catalog.pg_ts_config                              | 40 kB
 pg_catalog.pg_ts_dict                                | 40 kB
 pg_catalog.pg_operator_oid_index                     | 40 kB
 pg_catalog.pg_tablespace                             | 40 kB
 pg_catalog.pg_namespace                              | 40 kB
 pg_catalog.pg_am                                     | 40 kB
 pg_toast.pg_toast_45054                              | 32 kB
 pg_catalog.pg_conversion_name_nsp_index              | 32 kB
 pg_catalog.pg_shdescription                          | 32 kB
 tap_schema.tables                                    | 32 kB
 pg_catalog.pg_statistic_relid_att_index              | 32 kB
 pg_catalog.pg_amop_fam_strat_index                   | 32 kB
 tap_schema.schemas                                   | 32 kB
 pg_catalog.pg_amproc_fam_proc_index                  | 32 kB
 pg_catalog.pg_ts_config_map_index                    | 32 kB
 tap_schema.columns                                   | 32 kB
 caom.plane_phi1                                      | 32 kB
 pg_catalog.pg_amop_oid_index                         | 32 kB
 pg_catalog.pg_amop_opr_fam_index                     | 32 kB
 pg_catalog.pg_opclass_am_name_nsp_index              | 32 kB
 caom.deletedplane                                    | 24 kB
 pg_catalog.pg_pltemplate                             | 24 kB
 pg_catalog.pg_attrdef                                | 24 kB
 tap_schema.keys                                      | 24 kB
 caom.deletedspatialentity                            | 24 kB
 caom.deletedpolarizationentity                       | 24 kB
 caom.compositeobservation                            | 24 kB
 caom.deletedtemporalentity                           | 24 kB
 caom.deletedartifact                                 | 24 kB
 caom.deletedspectralentity                           | 24 kB
 pg_catalog.pg_aggregate                              | 24 kB
 information_schema.sql_implementation_info           | 16 kB
 information_schema.sql_languages                     | 16 kB
 pg_catalog.pg_enum                                   | 16 kB
 pg_catalog.pg_ts_config_oid_index                    | 16 kB
 pg_catalog.pg_trigger_oid_index                      | 16 kB
 tap_schema.schemas_pkey                              | 16 kB
 pg_catalog.pg_constraint_conrelid_index              | 16 kB
 pg_catalog.pg_conversion_default_index               | 16 kB
 pg_catalog.pg_namespace_oid_index                    | 16 kB
 pg_catalog.pg_namespace_nspname_index                | 16 kB
 pg_catalog.pg_cast_source_target_index               | 16 kB
 pg_catalog.pg_constraint_contypid_index              | 16 kB
 information_schema.sql_packages                      | 16 kB
 pg_catalog.pg_aggregate_fnoid_index                  | 16 kB
 pg_catalog.pg_rewrite_oid_index                      | 16 kB
 pg_toast.pg_toast_2618_index                         | 16 kB
 pg_catalog.pg_trigger_tgconstrname_index             | 16 kB
 pg_catalog.pg_trigger_tgrelid_tgname_index           | 16 kB
 information_schema.sql_parts                         | 16 kB
 pg_catalog.pg_authid_rolname_index                   | 16 kB
 pg_catalog.pg_authid_oid_index                       | 16 kB
 pg_catalog.pg_auth_members_role_member_index         | 16 kB
 pg_catalog.pg_auth_members_member_role_index         | 16 kB
 information_schema.sql_sizing                        | 16 kB
 tap_schema.keys_pkey                                 | 16 kB
 tap_schema.columns_pkey                              | 16 kB
 caom.deletedplane_i1                                 | 16 kB
 pg_catalog.pg_database_datname_index                 | 16 kB
 pg_catalog.pg_database_oid_index                     | 16 kB
 caom.deletedpolarizationentity_i1                    | 16 kB
 pg_catalog.pg_shdescription_o_c_index                | 16 kB
 pg_catalog.pg_language_name_index                    | 16 kB
 pg_catalog.pg_language_oid_index                     | 16 kB
 pg_catalog.pg_index_indrelid_index                   | 16 kB
 caom.timesample                                      | 16 kB
 pg_catalog.pg_am_name_index                          | 16 kB
 pg_toast.pg_toast_2619_index                         | 16 kB
 pg_catalog.pg_constraint_conname_nsp_index           | 16 kB
 pg_catalog.pg_index_indexrelid_index                 | 16 kB
 pg_catalog.pg_am_oid_index                           | 16 kB
 pg_catalog.pg_pltemplate_name_index                  | 16 kB
 pg_catalog.pg_shdepend_depender_index                | 16 kB
 pg_catalog.pg_shdepend_reference_index               | 16 kB
 pg_catalog.pg_tablespace_oid_index                   | 16 kB
 pg_catalog.pg_tablespace_spcname_index               | 16 kB
 pg_toast.pg_toast_45054_index                        | 16 kB
 caom.deletedtemporalentity_i1                        | 16 kB
 pg_catalog.pg_ts_dict_oid_index                      | 16 kB
 caom.deletedspectralentity_i1                        | 16 kB
 caom.deletedspatialentity_i1                         | 16 kB
 caom.deletedartifact_i1                              | 16 kB
 pg_catalog.pg_constraint_oid_index                   | 16 kB
 pg_catalog.pg_opfamily_am_name_nsp_index             | 16 kB
 pg_catalog.pg_opfamily_oid_index                     | 16 kB
 pg_catalog.pg_opclass_oid_index                      | 16 kB
 pg_catalog.pg_amproc_oid_index                       | 16 kB
 pg_catalog.pg_class_oid_index                        | 16 kB
 pg_catalog.pg_rewrite_rel_rulename_index             | 16 kB
 pg_catalog.pg_ts_config_cfgname_index                | 16 kB
 tap_schema.tables_pkey                               | 16 kB


Thank you
Isabella


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

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 


-- 
View this message in context: http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux