Re: Compressed binary field

Em 12/09/2012 09:16, Kevin Grittner escreveu:
> Edson Richter wrote:
>> Em 12/09/2012 00:37, Edson Richter escreveu:
>>> Em 11/09/2012 14:59, Kevin Grittner escreveu:
>>>> Edson Richter wrote:
>
>>>>> [biggest relation was a table heap with 29321 pages]
>>>>> [block size is 8 KB]
>
>>>> So your biggest table is actually 229 MB. Something is not adding
>>>> up. I can't see any way to reconcile your previous statements
>>>> with this number. There also hasn't been any real explanation for
>>>> the statement that you have 250000 files. There must be something
>>>> which matters here which hasn't yet been mentioned. Any ideas?
>
>>> I don't know why, look result of the following query (arquivo is
>>> the bytea field):
>>>
>>> select count(*) from notafiscalarq where arquivo is not null;
>>> count
>>> --------
>>> 715084
>
> What is a count of active rows in that table supposed to show me?
>
>> Look at the size (5100MB) of this table alone (got after Vacuum
>> with PgAdmin 14):
>
> Please show (copy/paste if possible) *exactly* how you arrived at
> that number and *exactly* how you determined that this number
> represented the size of a table and how you determined which table.
> If the results you previously posted are from the same database, you
> simply don't have a table heap that large.
>
> -Kevin
Ok, maybe I've used wrong database by mistake (I have dozens databases
here, so it's easy to do so in such different timeframes), let's repeat
all operations in one session:

in pgsql dir, executing "du -h --max-depth 1" results:

8,6G ./data
0 ./backups
8,6G .

Executing query
SELECT pg_size_pretty(pg_database_size('mydatabase'));
pg_size_pretty
----------------
7234 MB

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

Executing query
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
(10 registros)

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 | 312 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
(10 registros)

Looking at PgAdmin 14, I get the following data for table notafiscalarq:
Table Size 52 MB
Toast Table Size 5033 MB
Indexes Size 15 MB

Executing query
SELECT relkind, oid, relfilenode, reltoastrelid,
relpages, reltuples
FROM pg_class
ORDER BY relpages DESC
LIMIT 10;

results
relkind | oid | relfilenode | reltoastrelid | relpages | reltuples
---------+--------+-------------+---------------+----------+-----------
r | 18064 | 18064 | 18086 | 29332 | 639639
r | 18179 | 18179 | 0 | 22797 | 1.811e+06
r | 18116 | 18116 | 18121 | 19772 | 724370
r | 18343 | 18343 | 18347 | 14311 | 928633
r | 18352 | 18352 | 0 | 10488 | 917134
r | 18092 | 18092 | 0 | 7691 | 640709
r | 18396 | 18396 | 18404 | 7670 | 172791
r | 18558 | 18558 | 0 | 7608 | 386907
i | 747805 | 747805 | 0 | 6976 | 1.811e+06
r | 18409 | 18409 | 18413 | 6684 | 715084

Executing query
show block_size

block_size
------------
8192

So, all of this information was get using unique database session, so
they must related to same database and data files

Regards,

Edson