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
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 |