Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aAn7S-0002pp-FT for pgsql-performance@arkaria.postgresql.org; Sun, 20 Dec 2015 23:10:26 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aAn7R-00086A-Ko for pgsql-performance@arkaria.postgresql.org; Sun, 20 Dec 2015 23:10:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aAn5m-0006CI-K5 for pgsql-performance@postgresql.org; Sun, 20 Dec 2015 23:08:43 +0000 Received: from mail-bn1on0054.outbound.protection.outlook.com ([157.56.110.54] helo=na01-bn1-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aAn5e-0008Bs-OB for pgsql-performance@postgresql.org; Sun, 20 Dec 2015 23:08:41 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=BlueTreble.onmicrosoft.com; s=selector1-bluetreble-com; h=From:To:Date:Subject:Message-ID:Content-Type:MIME-Version; bh=W63bbH1QAO3Brv7GrqX03hNhk3hoTbeTUM3eX1nxxKQ=; b=IU3Atym73NrfYQWtWXd6adWQhfhZXpQqMdqdN7pSdzz23qn+y1sgv1BPZYvvTWKqEvcqCDCWrkwyjlLEt48wpNt/c7EfVKHqrYbSo1PheTmQ75qwitO0khWJXH0uZTr29pkWToMRVi7R6AaYGZHtxIMutVMDLVRgZ0WfTvQPpJw= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from decina.local (70.113.16.71) by SN1PR11MB0687.namprd11.prod.outlook.com (10.163.207.145) with Microsoft SMTP Server (TLS) id 15.1.361.13; Sun, 20 Dec 2015 23:08:23 +0000 Subject: Re: Can't explain db size To: Matteo Grolla , References: From: Jim Nasby Message-ID: <567734D4.4070202@BlueTreble.com> Date: Sun, 20 Dec 2015 17:08:04 -0600 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [70.113.16.71] X-ClientProxiedBy: BY2PR06CA059.namprd06.prod.outlook.com (10.141.250.177) To SN1PR11MB0687.namprd11.prod.outlook.com (25.163.207.145) X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 2:/KNGB8gojDFkCjjz3Z48Y3qN/dVuervCobuFMJR7PHdB7QKE7UH3r0QwvIYhWfYYOqVRVoVWJdzCWoSoEQTVVnsi+3Pv2am8k2PjNZa9Cw3Bpts3aFiuCEvmYHuK7knI4mRvL+r8kaE+ZBSlCjj2yA==; 3:WFyBuwbn89nhL9kVaTp6HUipiCanYYor3yhBI3CB91jdZSBtewt/59wsc2naKY+YRanIXR711s4/ZbZj9jhARBivSd03PUIMsVAxFr683ukclFfhTOuoDrPvtBBo3q13; 25:dQL3zS5d6Ul+4DqbdZVng0JavHZF3CqMoAwKp5cL2utyPPATfxyJxDIDtQ4+iTYVtD3CHQiZq+RfWQ3VBgdZwtTNOkV0O9q0Xot+2POzvEMxJN7/nB1nqb1xQ/PAim49kEdpBOIZD8ZF+XO7yy9soV7PBvAaIyiOBl2iH5P9VuT5B84r1momdiIDLbSOvh9uR8iavFLX9YvEdWzDHot29G4qdsP18ELpYNWbsFLaWEdQS5L2RSB5DlaG50ViNoi4/nPqFTNkPNvfbfeyI6YW9A==; 20:K9JUmumxTpyAlruxkibOCY6LRw4CAZEvhsCb+gtRY0H/eOGO+nOOydxKb6uKsdr/PCkXKYkfURrbElmWbrwYR3EwTc5HtH96xbvPaEoiHzx9dfKqQDcBrana1ax7ichwcJ7YMhbNBogMHllKgykOTG6QxdWSLxIp78NwDBxk5Go= X-Microsoft-Antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:SN1PR11MB0687; X-Microsoft-Antispam-PRVS: X-Exchange-Antispam-Report-Test: UriScan:; X-Exchange-Antispam-Report-CFA-Test: BCL:0; PCL:0; RULEID:(601004)(2401047)(520078)(5005006)(8121501046)(3002001)(10201501046); SRVR:SN1PR11MB0687; BCL:0; PCL:0; RULEID:; SRVR:SN1PR11MB0687; X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 4:qlLTK4/C1KKH1wkfJ3AbnWofYRoxtEzLyz1goFQE7zadX6xXJc2yMBXwySBAeU8MKh+ZnROLfOgboKPZEwOXawKogzBcH1dfx+CgfEcBloI6nU7/0vwFwXK+cL/xpNGFFQRsLT8JVysmdWvEGCRK1ODLYneNMz6bWT68brSZJHM9thruZ9bi/QmYDIWR36VhOjlmuOeIkImJjIQso63op425cNXJhjG01CayET7E1fq8FS3kaI3kXkSPmYk+bZfmgkk/V5kmDZ4jVCKTB0GL/FTlyFg4SB9KEuONiE9H8eAgpa34WURSUQcxy6s0wyrDRjLZ8zgh2plK1b2BzDA00/FV8Y6lklYxazhAQ0bEDBk4mWLHdeuNUse3X7qWENtx X-Forefront-PRVS: 0796EBEDE1 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(479174004)(189002)(199003)(377454003)(24454002)(15975445007)(551984002)(5001770100001)(66066001)(5004730100002)(19580395003)(230700001)(47776003)(59896002)(65806001)(81156007)(64126003)(5001960100002)(77096005)(50986999)(97736004)(42186005)(107886002)(106356001)(105586002)(87976001)(92566002)(4001350100001)(76176999)(87266999)(54356999)(189998001)(5008740100001)(65816999)(83506001)(80316001)(2950100001)(99136001)(65956001)(101416001)(33656002)(86362001)(6116002)(50466002)(3846002)(122386002)(36756003)(1096002)(586003)(23676002)(40100003)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:SN1PR11MB0687; H:decina.local; FPR:; SPF:None; PTR:InfoNoRecords; MX:1; A:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtTTjFQUjExTUIwNjg3OzIzOnRWV01sclMzd24yalVxWTRBdURCWjA4aElW?= =?utf-8?B?MThVaHdRcVZCbmVqc0YweGphdFUwRHBZbm1uL09ac1ZnOUVPSkx4eTlLSGwr?= =?utf-8?B?WUNMSjBsUHY1UEhxbkFTN2pXSy80aGpLVktJUVRlc0o0VzEzR1dLUm1kNFZn?= =?utf-8?B?WW5qMUJYbGFuN2Rad2dKRWh5Umh0dDRwTDErVzY1TFpPN05uamRZUmFvOVA4?= =?utf-8?B?UTgvakFHcm45WWQ5OUh4RkN4eSt2TEJ4Uk9ZaHVYdWpNcjUvbGk0dzlIQTB3?= =?utf-8?B?WDZPWUtsbnBqclc2WWNMa0ZnbHZFd3NQMEdFT2hWODQxSEpzSlVrUVBUWVRZ?= =?utf-8?B?MEtsaS9taUc0RG5xTVZnVytISDdIN0FETXlyNkdFazVGREdFSlBkS3B6UWFj?= =?utf-8?B?bVhoWUQ2cXljMW4xcG9iRlVlQzNSTW9GNGxPd3NtdG42Z0ZBcXhZSGt2c05s?= =?utf-8?B?c0ppRHV6RTZGRHI3TlA1ZzhkbjlFa2oxeHhiRy9UVzlzQkxWcXdDcmdrbUth?= =?utf-8?B?YWFnRThEazJ5TlRrZHpURThnUGVnWU5DOE0rUWhzNnBJZ1IvSTRqV0hpL3V5?= =?utf-8?B?aVU5cHA1TEtwM1ZMWms1alJHZHhCTVVKd2wzMndwRUJnaGthcHllekV6ak1B?= =?utf-8?B?VWFaZnVaMUNsd2k1REtWRXF3VFlIajAyQmJyRjhoV0xvWnZBUXp3R0Z2akJs?= =?utf-8?B?NzFyUGprbEVXZE8rUDVHSmxiRSsxOEdVVWxHYWFZbG9HcXJ1VnhDUHNFb3N3?= =?utf-8?B?ZXc2VGI2cm8ycUtWdTZVdkU0KzFzUzQxZUNQZGM0T25Nc0RTTEQ4MHV4Z0dw?= =?utf-8?B?Q0w4UElTRFJmcU1NbnhJTndkZjBBQjBpbkx4M3Yvb2w1NGxwVDY4Rnhjajd2?= =?utf-8?B?SXV0bFN3Wi9sU2ZZWEpxZVZ1a0lpd1d5V0VteldxVEJzWi9GOEIxQXRSOXdw?= =?utf-8?B?ZEZkbkQ0TVZMTURDaU82cnQ0UDJYdU9iSjhvbEQvOUVWK3ZLTTI3WkdSWFI4?= =?utf-8?B?dFMwNTZpckZHVVVJREw1MDZOMTRxOHlVYnVUdStlVjBkVjcwWENJZ1BsQ3RY?= =?utf-8?B?U0pTZ0dvOFNzRUlrQndLL3UzYzI5QzVOZnlDQ0ljTDNkRjA1UzJ6SUNEd1RY?= =?utf-8?B?K0dNSUJaNEdpeGx3cmFsVnhwZnlvUE9DNmV5WGF5OFlSUWNiS3RBRXoyV200?= =?utf-8?B?bHQvUmtsclN5YzRieEVEWXpSc3hCeEU0cGxMNjMrZmFWVE5WVk9pQWFKbS8w?= =?utf-8?B?bGV0WEZTNElTK3J0bGFmRUtEMG1hT25RTVZKaVQ1SkFrNStXcWgxaG5jRXZw?= =?utf-8?B?WEN6V2ZyUGhwejRDSzR5RnRnRFNtcWlvK3lnbmFGcFdNZ0J2T242MGozbGJH?= =?utf-8?B?WkRIZEpOMG9iL0NxNHY3K1YwbHVmVVRIM3BhK1BDOTlyb2FFdUVleUVCeEt1?= =?utf-8?B?ZVdJYUxyZzVYZTc0NWJCUW1zY2NSaWZKczJncmJ2NVF3R2dxTTRteVh6TVB1?= =?utf-8?B?aStMZ0dveVVnTzVsdlJRRW9ncTV2MG4yak9LczZRQURGbTVEbmd4Y0l2VlRY?= =?utf-8?B?VlJqZHp4SjdrZlN4cTNiaVZqalMyNlVUSG5nN0lJVlFwaDBjKzN0aXdTdkph?= =?utf-8?B?a2FySjhheFd5NEZCY09rcVhNYk4vdkc2SGc5UFNxcmM3Wk4wdjZrcDc0OWJM?= =?utf-8?B?a0psekZvWVBRUUZOT1FwUW1yY21FREljYnBtUzNTNkxDMG8yNzNFNFVyQkpy?= =?utf-8?B?VE9sbEtCNGpxS1oxNkY1Yjl3ZjJnUk9Ud3pjVzgrd2MvMkptU1hTazVMVlJM?= =?utf-8?B?YWVqTXRodWFQSFI3K1pVRlFjMEYwSzRSSitmWEd6VzBQNkRSSGxPT0s4SDkx?= =?utf-8?B?dVRtVGk3ckFuaExQL25RVDNkeXVQSHhTSGNaeFdSa1A0VGptWnl4ZFlaUDN1?= =?utf-8?B?czg0Y04xT013PT0=?= X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 5:cRXTQOojiFev6AsJe/0UsmjrufGy0U2l8Rr6YoTV1h8uVCtE8pqGiC9Ck/s9At7KOBlGvqvwoL5gkkhMrzO4zwOWzeCpUtGPKWc+8DmC/H51V2d/DJwN4gz1olNPk8iPIrT1qQnmcD7pGlVVfOzDDA==; 24:3Gh3J1f0wAUaTVGzXda/SituhnDp3tbFIticDv7p4GAcQ+f9qOqon4J7ZwYqZxJVbvq9z4TwCe3YCT9G8il/mBrKIPP0ozouyLwcDIgltnk= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 20 Dec 2015 23:08:23.3292 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-Transport-CrossTenantHeadersStamped: SN1PR11MB0687 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On 12/15/15 3:52 AM, Matteo Grolla wrote: > 1) Can you explain me the big difference between the result in A for > table alf_node_properties: 17GB and the result in B: ~6GB ? 11GB of indexes would explain it. > 2) Can you explain me the difference between the result in B: ~6GB and > the result in C, the sum of all column sizes, 3717MB ? Probably per-page and per-tuple overhead. What does SELECT reltuples, relpages FROM pg_class WHERE oid = 'public.alf_node_properties'::regclass show? -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance