Re: Compressed binary field

Em 13/09/2012 16:12, Kevin Grittner escreveu:
> Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
>
>> Anything else I can do from here?
>
> Did that result in more accurate numbers for pg_class.reltuples?
>
> -Kevin
>
I don't how number were not accurate - for me they always seemed
consistent with what I knew about it...
Let's repeat all tests again (see, data grows on daily basis, so numbers
will be a bit different - yes, I've run the vacuum again):

SELECT pg_size_pretty(pg_database_size('mydatabase'));
pg_size_pretty
----------------
7238 MB

SELECT pg_size_pretty(pg_relation_size('notafiscalarq'));
pg_size_pretty
----------------
52 MB

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 10;
relation | size
---------------------------------+---------
pg_toast.pg_toast_18409 | 4976 MB
pg_toast.pg_toast_18146 | 290 MB
public.cotacao | 229 MB
public.elementocalculado | 179 MB
public.cotacaotransicaosituacao | 155 MB
public.log | 112 MB
public.logradouro | 82 MB
public.cotacaonf | 60 MB
public.notafiscal | 60 MB
public.tabelacalculada | 60 MB

SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 10;
relation | total_size
---------------------------------+------------
public.notafiscalarq | 5102 MB
public.cotacao | 331 MB
public.elementocalculado | 313 MB
public.documentotransportearq | 294 MB
public.cotacaotransicaosituacao | 233 MB
public.log | 196 MB
public.logradouro | 149 MB
public.cotacaonf | 118 MB
public.tabelacalculada | 116 MB
public.notafiscal | 94 MB

SELECT relkind, oid, relfilenode, reltoastrelid,
relpages, reltuples
FROM pg_class
ORDER BY relpages DESC
LIMIT 10;
relkind | oid | relfilenode | reltoastrelid | relpages | reltuples
---------+-------+-------------+---------------+----------+-------------
t | 18413 | 18413 | 0 | 636949 |
2.64373e+06
t | 18150 | 18150 | 0 | 37086 |
149502
r | 18064 | 18064 | 18086 | 29347 | 639695
r | 18179 | 18179 | 0 | 22901 |
1.8172e+06
r | 18116 | 18116 | 18121 | 19779 | 724619
r | 18343 | 18343 | 18347 | 14325 | 928805
r | 18352 | 18352 | 0 | 10488 |
917134
r | 18092 | 18092 | 0 | 7695 |
640804
r | 18396 | 18396 | 18404 | 7671 | 172792
r | 18558 | 18558 | 0 | 7644 |
388332

show block_size;
block_size
------------
8192

Regards,

Edson