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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
