public inbox for [email protected]
help / color / mirror / Atom feedFrom: Oli Sennhauser <[email protected]>
To: Chester Kustarz <[email protected]>
Cc: Grzegorz Dostatni <[email protected]>
Cc: [email protected]
Subject: Re: Size on Disk
Date: Wed, 26 Nov 2003 18:40:18 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Hello
>SELECT CASE WHEN ((SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) IS NOT NULL) THEN (SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) WHEN (class1.relname IS NOT NULL) THEN class1.relname ELSE NULL::name END AS "table", CASE WHEN (class1.relkind = 'r'::"char") THEN NULL::name ELSE class1.relname END AS "index", (class1.relpages * 8) AS "size (KBytes)" FROM pg_class class1 WHERE ((class1.relkind = 'r'::"char") OR (class1.relkind = 'i'::"char")) ORDER BY CASE WHEN ((SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) IS NOT NULL) THEN (SELECT class3.relname FROM pg_class class3, pg_index WHERE ((class1.oid = pg_index.indexrelid) AND (class3.oid = pg_index.indrelid))) WHEN (class1.relname IS NOT NULL) THEN class1.relname ELSE NULL::name END, CASE WHEN (class1.relkind = 'r'::"char") THEN NULL::name ELSE class1.relname END, (class1.relpages * 8);
>
>
I was verry interested in your querry but I did not understood it.
Therefore I rewrote it. Now it is a little simpler to read and does (in
my opinion) the same?
--
-- Amount of space per object used after vacuum
--
VACUUM;
SELECT c1.relname AS "tablename", c2.relname AS "indexname",
c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tablename, indexname DESC, size_kb;
Caution: This Sktipt does NOT exactly the same... but the results should
be the same
Regrards
Oli
-------------------------------------------------------
Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland
Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail [email protected]
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/
Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch
Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import
Attachments:
[application/x-pkcs7-signature] smime.p7s (3.4K, 2-smime.p7s)
download
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Size on Disk
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox