why toasted is created when domain is used ?

From: PegoraroF10 <marcos(at)f10(dot)com(dot)br>
To: pgsql-general(at)postgresql(dot)org
Subject: why toasted is created when domain is used ?
Date: 2019-08-05 12:07:58
Message-ID: 1565006878327-0.post@n3.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kenneth Marshall 2019-08-05 12:31:20 Re: Compression In Postgresql 9.6
Previous Message Alban Hertroys 2019-08-05 12:01:24 Re: adding more space to the existing server