X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 56A27D1B45C for ; Wed, 26 Nov 2003 17:37:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 82082-02 for ; Wed, 26 Nov 2003 13:37:20 -0400 (AST) Received: from mail1.bluewin.ch (mail1.bluewin.ch [195.186.1.74]) by svr1.postgresql.org (Postfix) with ESMTP id 45AA5D1B42F for ; Wed, 26 Nov 2003 13:37:14 -0400 (AST) Received: from bluewin.ch (81.62.221.139) by mail1.bluewin.ch (Bluewin AG 7.0.024) id 3FBC7E58000E1BA8; Wed, 26 Nov 2003 17:37:13 +0000 Message-ID: <3FC4E582.7080308@bluewin.ch> Date: Wed, 26 Nov 2003 18:40:18 +0100 From: Oli Sennhauser User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en, de-ch MIME-Version: 1.0 To: Chester Kustarz Cc: Grzegorz Dostatni , pgsql-admin@postgresql.org Subject: Re: Size on Disk References: In-Reply-To: Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms060902060509040702090302" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/448 X-Sequence-Number: 11435 This is a cryptographically signed message in MIME format. --------------ms060902060509040702090302 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Hello >SELECT CASE WHEN ((SELECT class3.relname FROM pg_class class3, pg_index WH= ERE ((class1.oid =3D pg_index.indexrelid) AND (class3.oid =3D pg_index.indr= elid))) IS NOT NULL) THEN (SELECT class3.relname FROM pg_class class3, pg_i= ndex WHERE ((class1.oid =3D pg_index.indexrelid) AND (class3.oid =3D pg_ind= ex.indrelid))) WHEN (class1.relname IS NOT NULL) THEN class1.relname ELSE N= ULL::name END AS "table", CASE WHEN (class1.relkind =3D 'r'::"char") THEN N= ULL::name ELSE class1.relname END AS "index", (class1.relpages * 8) AS "siz= e (KBytes)" FROM pg_class class1 WHERE ((class1.relkind =3D 'r'::"char") OR= (class1.relkind =3D 'i'::"char")) ORDER BY CASE WHEN ((SELECT class3.relna= me FROM pg_class class3, pg_index WHERE ((class1.oid =3D pg_index.indexreli= d) AND (class3.oid =3D pg_index.indrelid))) IS NOT NULL) THEN (SELECT class= 3.relname FROM pg_class class3, pg_index WHERE ((class1.oid =3D pg_index.in= dexrelid) AND (class3.oid =3D pg_index.indrelid))) WHEN (class1.relname IS = NOT NULL) THEN class1.relname ELSE NULL::name END, CASE WHEN (class1.relkin= d =3D 'r'::"char") THEN NULL::name ELSE class1.relname END, (class1.relpage= s * 8); >=20=20 > I was verry interested in your querry but I did not understood it.=20 Therefore I rewrote it. Now it is a little simpler to read and does (in=20 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 =3D i.indrelid AND i.indexrelid =3D c2.oid UNION SELECT relname, NULL, relpages * 8, relfilenode FROM pg_class WHERE relkind =3D 'r' ORDER BY tablename, indexname DESC, size_kb; Caution: This Sktipt does NOT exactly the same... but the results should=20 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 oli.sennhauser@bluewin.ch 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/i= mport --------------ms060902060509040702090302 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIK0jCCAmUwggHOoAMCAQICCQCsJKpLsmk5ZzANBgkqhkiG9w0BAQUF ADBwMSUwIwYDVQQDExxTd2lzc1NpZ24gUGVyc29uYWwgQ2xhc3MxIENBMSYw JAYJKoZIhvcNAQkBFhdjbGFzczEtY2FAU3dpc3NTaWduLmNvbTESMBAGA1UE ChMJU3dpc3NTaWduMQswCQYDVQQGEwJDSDAeFw0wMzEwMTYxODIxMjNaFw0w NDEwMTYxODIxMjNaMGIxNjA0BgNVBAMULVN3aXNzU2lnbiBCcm9uemUgSUQg b2xpLnNlbm5oYXVzZXJAYmx1ZXdpbi5jaDEoMCYGCSqGSIb3DQEJARYZb2xp LnNlbm5oYXVzZXJAYmx1ZXdpbi5jaDBcMA0GCSqGSIb3DQEBAQUAA0sAMEgC QQCxt+SmHgsTzgBw7MY+KYwNwfurrZWoUSqm6Vdw5Z9aQDuRgZ/+OhL6dDXK VHpsyUiG6tzmqcEC8LkIpKv20YZxAgMBAAGjWTBXMCQGA1UdEQQdMBuBGW9s aS5zZW5uaGF1c2VyQGJsdWV3aW4uY2gwDgYDVR0PAQH/BAQDAgWgMB8GA1Ud IwQYMBaAFO30uEqBiizjCfrm+jE/TLQfSf6EMA0GCSqGSIb3DQEBBQUAA4GB AIK6w1rQif2IvpmKBjZf7fSp2FryojtHgNnKAlzfnwflZc0xcyqbRCC2vo0C psbvUBlx/t0pSwb0OVuD/uzvqTs7clq3eRH7VJL8LLS2OrJv7gI3jJVRD/oP 7m0mD/qmj0boSr9ilb5WFjXAVwsgJKuv0iHcAFJTTmj8RCZmWIVhMIICZTCC Ac6gAwIBAgIJAKwkqkuyaTlnMA0GCSqGSIb3DQEBBQUAMHAxJTAjBgNVBAMT HFN3aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEW F2NsYXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24x CzAJBgNVBAYTAkNIMB4XDTAzMTAxNjE4MjEyM1oXDTA0MTAxNjE4MjEyM1ow YjE2MDQGA1UEAxQtU3dpc3NTaWduIEJyb256ZSBJRCBvbGkuc2VubmhhdXNl ckBibHVld2luLmNoMSgwJgYJKoZIhvcNAQkBFhlvbGkuc2VubmhhdXNlckBi bHVld2luLmNoMFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALG35KYeCxPOAHDs xj4pjA3B+6utlahRKqbpV3Dln1pAO5GBn/46Evp0NcpUemzJSIbq3OapwQLw uQikq/bRhnECAwEAAaNZMFcwJAYDVR0RBB0wG4EZb2xpLnNlbm5oYXVzZXJA Ymx1ZXdpbi5jaDAOBgNVHQ8BAf8EBAMCBaAwHwYDVR0jBBgwFoAU7fS4SoGK LOMJ+ub6MT9MtB9J/oQwDQYJKoZIhvcNAQEFBQADgYEAgrrDWtCJ/Yi+mYoG Nl/t9KnYWvKiO0eA2coCXN+fB+VlzTFzKptEILa+jQKmxu9QGXH+3SlLBvQ5 W4P+7O+pOztyWrd5EftUkvwstLY6sm/uAjeMlVEP+g/ubSYP+qaPRuhKv2KV vlYWNcBXCyAkq6/SIdwAUlNOaPxEJmZYhWEwggK6MIICI6ADAgECAghscFj+ 9LG+HzANBgkqhkiG9w0BAQUFADBnMRwwGgYDVQQDExNTd2lzc1NpZ24gQ2xh c3MxIENBMSYwJAYJKoZIhvcNAQkBFhdjbGFzczEtY2FAU3dpc3NTaWduLmNv bTESMBAGA1UEChMJU3dpc3NTaWduMQswCQYDVQQGEwJDSDAeFw0wMjEwMjQx MzM0MTZaFw0wNDEwMjQxMjAwMTRaMHAxJTAjBgNVBAMTHFN3aXNzU2lnbiBQ ZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBT d2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNI MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQCvb8N37MzyZnhRvbgSYCRa OYbCqH5/o9yoq3kyHP50e+QJVzvRZj9vw3GeDnhl+IrjmC0oPcXqmCgnvmZ3 2AlQITCU7jAfTKHjk2GZ8cFGgFTuR0lTbv0AfgMmYXYxoV0hu4v6WpDsigNx J6q5Pt6rWEFkOLu7IvgkBZ2RKQdz5wIDAQABo2YwZDAOBgNVHQ8BAf8EBAMC AQYwEgYDVR0TAQH/BAgwBgEB/wIBADAdBgNVHQ4EFgQU7fS4SoGKLOMJ+ub6 MT9MtB9J/oQwHwYDVR0jBBgwFoAUnKEHkQJI5q/Y1/5jBjNfheKPtbMwDQYJ KoZIhvcNAQEFBQADgYEAFMJrIPCoeXZxquyk6xqqflC/W+W3y5SNVzdASOjr tTzREC1oRwI36eqwMuAkbCr7znDV8rp2OC5S/DNw360FUvro/MjL2Ajztkum V/EF6enEAMbzCjBzJM8EfYEE3XyDyy5SzOtU5VPDMHOe9lPVOZ+1v2FpVLlK RW9Fxy6EG+wwggM+MIICJqADAgECAghVy6lgNCVu5DANBgkqhkiG9w0BAQUF ADB2MQswCQYDVQQGEwJDSDESMBAGA1UEChMJU3dpc3NTaWduMTIwMAYDVQQD EylTd2lzc1NpZ24gQ0EgKFJTQSBJSyBNYXkgNiAxOTk5IDE4OjAwOjU4KTEf MB0GCSqGSIb3DQEJARYQY2FAU3dpc3NTaWduLmNvbTAeFw0wMjEwMjQxMjAw MTRaFw0wNDEwMjQxMjAwMTRaMGcxHDAaBgNVBAMTE1N3aXNzU2lnbiBDbGFz czEgQ0ExJjAkBgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBTd2lzc1NpZ24uY29t MRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNIMIGfMA0GCSqGSIb3 DQEBAQUAA4GNADCBiQKBgQDJHZUwOQhgCPyjvVNBeMocJ/hsmImGuFFIa/QQ t31ExbzSfe58xgItElgqgPbMFevbVldcf2gafVMihh3aIYPcNTsDFn9FeiVP S7zohjKFVQxxDjprzCkixPAF9vuY9IKfRUoae0rDM6T2D5a2KdgtrJTgUu+y k0VjFd1hfHW5qwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/ BAUwAwEB/zAdBgNVHQ4EFgQUnKEHkQJI5q/Y1/5jBjNfheKPtbMwHwYDVR0j BBgwFoAUltdxzTkq1PyIsYqrU3hp749HfhYwDQYJKoZIhvcNAQEFBQADggEB ADxcvW2sVFFI48Pf9Bss++/M+a3sZ56EOdOcdYHhkk1Ax/tUsLCnjscKWHwj iJo0/ZunK070iwpSIbJAGDFXERx1VZZ3unUlFLANAgddIXAbIuvij5VoGZbG 6UiFxMTbcYxO1yYM/iQVSwB7NBKqUcTdHyYyw7oPh9BIayrYTkLBy2FPKe21 CAFGa9Ek8D/RyL6YsFgLB2neXqIxdIqID+QUufzKCUGOdgbc3Tvk8im3vR8C RVBxvZhY9I8+NIQR+nBPMoVp2PnDnp5sNLNGLIdKoZJXlQ2+8+fjnB/vrjYp Tn9W3nJEgJsbFScH4k+urq8TjK+QPpamJjySIikcJG8xggK3MIICswIBATB9 MHAxJTAjBgNVBAMTHFN3aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAk BgkqhkiG9w0BCQEWF2NsYXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQK EwlTd2lzc1NpZ24xCzAJBgNVBAYTAkNIAgkArCSqS7JpOWcwCQYFKw4DAhoF AKCCAdEwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUx DxcNMDMxMTI2MTc0MDE4WjAjBgkqhkiG9w0BCQQxFgQUokkwto/cOUejZXpO Wp9qc5lcjX0wUgYJKoZIhvcNAQkPMUUwQzAKBggqhkiG9w0DBzAOBggqhkiG 9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZIhvcNAwIC ASgwgYwGCSsGAQQBgjcQBDF/MH0wcDElMCMGA1UEAxMcU3dpc3NTaWduIFBl cnNvbmFsIENsYXNzMSBDQTEmMCQGCSqGSIb3DQEJARYXY2xhc3MxLWNhQFN3 aXNzU2lnbi5jb20xEjAQBgNVBAoTCVN3aXNzU2lnbjELMAkGA1UEBhMCQ0gC CQCsJKpLsmk5ZzCBjgYLKoZIhvcNAQkQAgsxf6B9MHAxJTAjBgNVBAMTHFN3 aXNzU2lnbiBQZXJzb25hbCBDbGFzczEgQ0ExJjAkBgkqhkiG9w0BCQEWF2Ns YXNzMS1jYUBTd2lzc1NpZ24uY29tMRIwEAYDVQQKEwlTd2lzc1NpZ24xCzAJ BgNVBAYTAkNIAgkArCSqS7JpOWcwDQYJKoZIhvcNAQEBBQAEQAYVpcojZavj ysQpbfHT6Bt2tPAcerFZ0oBmX/Q/6rpEVHuMCjc3bVIWsH0E4ipfFohoqHYD ZrHIWBqatXkGbsEAAAAAAAA= --------------ms060902060509040702090302--