select count(*) Total_Tables,
count(*) filter (where t2.oid is not null) Toasted_Tables,
count(*) filter (where t2.reltuples > 0) Toasted_with_records
from pg_class t1 left join pg_class t2 on t1.reltoastrelid = t2.oid and
t2.relkind = 't'
where t1.relkind = 'r'
total_tables toasted_tables toasted_with_records
11564 9521 263
Then seeing structure of my tables only few of them really needs toasting,
so it´s ok to have few toast tables with records. The question is, why those
toasted tables were created ?
Then, doing a simple test using one of those tables ...
create table with_toast(
id_pk i32, Field2 i32 not null, Field3 i16 not null, Field4 numeric(7, 2),
Field5 i32 default public.DefaultValueForField(), Field6 datahora default
CURRENT_TIMESTAMP);
create table without_toast(
id_pk integer, Field2 integer not null, Field3 smallint not null, Field4
numeric(7, 2),
Field5 integer default public.DefaultValueForField(), Field6 timestamp
default CURRENT_TIMESTAMP);
As you can see this table doesn´t need to have a toast and probably will
never have records on it.
select t1.oid, t1.relname, t1.relkind, t1.oid, t2.oid, t2.relname
from pg_class t1 left join pg_class t2 on t1.reltoastrelid = t2.oid and
t2.relkind = 't'
inner join pg_namespace ns on ns.oid = t1.relnamespace where t1.relkind =
'r' and t1.relname ~* 'test';
oid relname relkind oid oid relname
9375155 with_toast r 9375155 9375160 pg_toast_9375155
9375167 without_toast r 9375167 NULL NULL
So, the question is, when I create a table using domains it creates a toast
for it, why ?
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
