Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |
object_referenz | text |
Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|
object_referenz | varchar(300) |
in beide Tabellen die gleichen 100 000 000 rows inserted
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±-----------±--------
373985765 | homegate | test2 | 9.99381e+07 | 6025355264 | 0 | | 6025355264 | 5746 MB | 0 bytes | | 5746 MB
373985759 | homegate | test1 | 1.0001e+08 | 5480660992 | 0 | 8192 | 5480652800 | 5227 MB | 0 bytes | 8192 bytes | 5227 MB
okay des test2 tabelle ist leicht kleiner
dann habe ich den gleichen test nochmals gemacht mit nur dem date/timestamp feld wieder mit den 100 000 000
Table “homegate.test1”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | date |
Table “homegate.test2”
Column | Type | Modifiers
-----------------±-----±----------
tstamp | timestamp|
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = ‘r’
and relname in (‘test1’, ‘test2’)
) a
) a;
oid | table_schema | table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-----------±-------------±-----------±-------------±------------±------------±------------±------------±--------±--------±------±--------
373985765 | homegate | test2 | 0 | 3624812544 | 0 | | 3624812544 | 3457 MB | 0 bytes | | 3457 MB
373985759 | homegate | test1 | 0 | 3624779776 | 0 | | 3624779776 | 3457 MB | 0 bytes | | 3457 MB
(2 rows)
jetzt sind beide Tabellen ungefährt gleich gross
also waren beim ersten vergleich der grössere Platzverbrauch wegen dem 2. Feld das statt varchar(300) --> text war?
denn nun sind ja beide Tabellen gleich gross
das würde ja deiner Aussage widersprechen? oder sehe ich da was falsch?
Besten Dank