Buenos dias a todos
Tengo una duda sobre l estimacion del tamaño de una tabla.
Partamos de esta pregunta de un examen de cetificacion postgres
*In the SQL description below, the table was defined and 100,000 rows
inserted.
Select the most appropriate size estimate for the table file.
One block is defined as 8192 bytes. Do not include the size of the index
file.
CREATE TABLE sales(
id BIGINT PRIMARY KEY,
salesclerk INTEGER NOT NULL,
sales_date TIMESTAMP NOT NULL);
*1. 1 Megabytes 2. 2 Megabytes 3. 5 Megabytes 4. 10 Megabytes 5. 20
Megabytes*
*Entonces para desasnarme hize esto:
--crear la tabla
CREATE TABLE sch_desarrollo.sales(
id BIGINT PRIMARY KEY,
salesclerk INTEGER NOT NULL,
sales_date TIMESTAMP NOT NULL);
--insertar 100,000 registros
insert into sch_desarrollo.sales (id,salesclerk, sales_date) select foo,foo,
now() from generate_series(1,100000) foo;
--comprobar data
select * from sch_desarrollo.sales;
1;1;"2009-05-20 08:53:46.017087"
2;2;"2009-05-20 08:53:46.017087"
3;3;"2009-05-20 08:53:46.017087"
4;4;"2009-05-20 08:53:46.017087"
5;5;"2009-05-20 08:53:46.017087"
6;6;"2009-05-20 08:53:46.017087"
7;7;"2009-05-20 08:53:46.017087"
8;8;"2009-05-20 08:53:46.017087"
. . . .. . . .. . . .. . . .. . . .. . . .. . . .
. . . .. . . .. . . .. . . .. . . .. . . .. . . .
99998;99998;"2009-05-20 08:53:46.017087"
99999;99999;"2009-05-20 08:53:46.017087"
100000;100000;"2009-05-20 08:53:46.017087"
--Tamaño de los objetos en megabytes
SELECT relname, round(((relpages*8)::double precision/1024::double
precision)::numeric,2) as MB
FROM pg_class where relname like '%sales%'
ORDER BY relpages DESC;
"sales";4.98
"sales_pkey";2.16
--mostrar tamaño por columna(supongo)
select
pg_size_pretty(pg_column_size('id')),pg_size_pretty(pg_column_size('salesclerk')),pg_size_pretty(pg_column_size('sales_date'))
from sch_desarrollo.sales
"3 bytes";"11 bytes";"11 bytes"
"3 bytes";"11 bytes";"11 bytes"
"3 bytes";"11 bytes";"11 bytes"
"3 bytes";"11 bytes";"11 bytes"
"3 bytes";"11 bytes";"11 bytes"
............................................
............................................
--tamaño de un registro
select pg_size_pretty(pg_column_size('id') + pg_column_size('salesclerk')+
pg_column_size('sales_date')) from sch_desarrollo.sales
"25 bytes"
--tamaño de los 100,000 registros
select pg_size_pretty(sum(pg_column_size('id') +
pg_column_size('salesclerk')+ pg_column_size('sales_date'))) from
sch_desarrollo.sales
"2441 kB"
Ahora la pregunta alguien me podria explicar que paso porque no concuerda o
el concepto de pg_column_size para que sirve o como lo debo de entender
Gracias por sus respuestas
Saludos
--
Luis Zevallos
DBA Administrador de DB & SRVR
Lobo Sistemas S.A.C
tel: +5154958310833
http://www.lobosistemas.com
