Re: Compressed binary field - Mailing list pgsql-general

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