Re: Compressed binary field - Mailing list pgsql-general

Em 13/09/2012 16:12, Kevin Grittner escreveu:
> Edson Richter <edsonrichter@hotmail.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