Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a91gY-0001XV-5K for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Dec 2015 02:19:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a91gX-0000Bs-4U for pgsql-hackers@arkaria.postgresql.org; Wed, 16 Dec 2015 02:19:21 +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 1a91gW-0000Bl-Er for pgsql-hackers@postgresql.org; Wed, 16 Dec 2015 02:19:20 +0000 Received: from mail-bn1on0086.outbound.protection.outlook.com ([157.56.110.86] 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 1a91gO-0000cj-W4 for pgsql-hackers@postgresql.org; Wed, 16 Dec 2015 02:19:17 +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=ZDJhCLTbtcW0m47n/Jb75fLDliNEgdRctbB9K2bjDJA=; b=FF1v0MrFI3MGmL+K7YZRXX9TvsUhvhsyeDZHNEl7vy0gF5cmX4suEg7F1V2/fztCWSe0ROBckNjUTrrOCnMwUSrfvdR+4zSQjPtfxoBBNgbhu5IP6po89W1gMbHPsi8WLlpCvkExKWOSMIlwJ/TAGDbWGSALzh/+JLbMc2H/BKY= Authentication-Results: spf=none (sender IP is ) smtp.mailfrom=Jim.Nasby@bluetreble.com; Received: from [192.168.1.10] (97.77.122.57) by SN1PR11MB0687.namprd11.prod.outlook.com (10.163.207.145) with Microsoft SMTP Server (TLS) id 15.1.361.13; Wed, 16 Dec 2015 02:19:06 +0000 To: Pg Hackers From: Jim Nasby Subject: Clarify vacuum verbose message Message-ID: <5670CA14.7090208@BlueTreble.com> Date: Tue, 15 Dec 2015 20:19:00 -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 Content-Type: text/plain; charset="utf-8"; format=flowed Content-Transfer-Encoding: 7bit X-Originating-IP: [97.77.122.57] X-ClientProxiedBy: SN1PR0701CA0002.namprd07.prod.outlook.com (25.162.96.12) To SN1PR11MB0687.namprd11.prod.outlook.com (25.163.207.145) X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 2:5kxmGRPvrFExcZb6+dE6XTqXYYkNSKu6MQPn4AxI5h58MuZ1+widqZXbBKjLVevxR9VZ2o+bwqXOf8IhtaW/cgjrYI0ifOJhdmuMQrAg4wdZeSxjh//aYRqHNNfqmALSrbVdU4589xPPMPMNA69cZw==; 3:bIWDNNKb5Fpn3OV4t+eo91QwQi9Z/Fc8bUstB9kewezNVe1L/KJ0IbmuAxyXGPwRzOQw8mxH4hf8Ywk/Ciqx7coVJnawmuhs7hhzwrtSuzvx1ULaYgWwhFT31F86GseW; 25:mdtw5pp0DGoh9TY1Eb21gm3IhFX6InbuFPevutUn2E8QnPKNgv2Yny8LquQYfAdGtU0tYx+UdlkvAJ6ucmSyC5WTubhkOqEc5TdNJFROUwtaUUyoVrmY5euQFq55ZTTYZLt+0JhLmeQ2qPHGgu6O1SXHXfwLqJOgqNbwQu6WJhVvXZkqLrTbmiI2jzKCzrywKqg7xpJlwncSM+mm9wue2dN1PnY3ILkIT+07GfQRfck1/bFHY9ChvWcbTQU1k627; 20:t4UIoUuHLy1M9tKdTJt91GeDmr7CGEJRYxySdi8C/FGf30zEgRPWquZr4hnssSPg1YoLrxEA92S34USJQ80WPWFx4K+gKF7CBGqX9EQQPYxh3QLx54nGNTHofAD3AFgXFrYU0RjaK4G0BifN5ilfZylQmmWsYgbTskUI/4V2IGc= 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)(5005006)(520078)(8121501046)(10201501046)(3002001); SRVR:SN1PR11MB0687; BCL:0; PCL:0; RULEID:; SRVR:SN1PR11MB0687; X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 4:ly31RwKwqhVlTrnFZPiQqJwYbBHysIqSjPtHM78JNtCfbP1FNTz6IsQ2AvzQZeBnmeQU3NwJ3FZFElPKQ4aRPfxI3owQQBhZpNMlDv4xkvJPOYqwe6KEOlWfcDmWjPUh12zaodOyt07lk+lDc9ScgonL+N4gE0x2q2z7wqf8WweTpl8J6bSJe7bbkoPlOpjtsS04NrfLvHgkOWp1RBV1xlRm+dhCsCcPKp+naMUEji9iTnDJ811gLotYE7xaDUG5MRTiAU9UqOz5vmWs3CgXxKhXPFlpcVaxLqhiMuD80J7LoN235cUPEquuz8ARujOK6URS2uOGgAlyNvUMb8Ujlia+LHo6EBEXdtD6aPJEA6Vmm3zaz2KHxy+MN/6SCwxq X-Forefront-PRVS: 0792DBEAD0 X-Forefront-Antispam-Report: SFV:NSPM; SFS:(10009020)(6009001)(6049001)(199003)(46034005)(189002)(83506001)(5008740100001)(450100001)(80316001)(19580395003)(229853001)(40100003)(10710500006)(3846002)(86362001)(6116002)(1096002)(586003)(23676002)(36756003)(122386002)(50466002)(47776003)(101416001)(87976001)(33656002)(64126003)(106356001)(5004730100002)(65956001)(66066001)(230700001)(77096005)(15975445007)(105586002)(107886002)(81156007)(65806001)(4001350100001)(110136002)(5001960100002)(59896002)(97736004)(7110500001)(42186005)(92566002)(117156001)(50986999)(2420400006)(65816999)(189998001)(54356999)(87266999)(140573001)(85282002)(336755003)(18886065003); DIR:OUT; SFP:1101; SCL:1; SRVR:SN1PR11MB0687; H:[192.168.1.10]; FPR:; SPF:None; PTR:InfoNoRecords; A:1; MX:1; LANG:en; Received-SPF: None (protection.outlook.com: bluetreble.com does not designate permitted sender hosts) X-Microsoft-Exchange-Diagnostics: =?utf-8?B?MTtTTjFQUjExTUIwNjg3OzIzOllBUDE4UGNzTkdnajVZcUxoSXlYS2l3dGU3?= =?utf-8?B?QVNQWUJLOUU2dWNFVk14dTZENVBvYXFoeDNhQUhUZjlvRml0OFppNzkzVGNK?= =?utf-8?B?NHhGYXdjbFJMMEZKMGNYUFB0Y2VHNVNtd1hPZlBPcGlQRE9LUzlVUzBJV3Yz?= =?utf-8?B?Y09GV3A5UThCY0xiOGJtYzVIRXNnbkNkcHZxY2s1UDg2SmVkL1NQSHNOQ2tR?= =?utf-8?B?RmxTWFVCNy8zcVNYaFBrTE5Wcy92KzdoVmJOeFRYdmdTSGZUOHdPZ0kwK3Y5?= =?utf-8?B?WEsvcmQ5MThLaG40K2pIbXZ1UjBQTUw4dUU2TUtpb3pmNW9uWXpkUXFtMDI0?= =?utf-8?B?Yit0eEtsOWJvOXJyRDlTVDNMS243Y1ErZDZMZXRzVVJrNzVHbjhnekJ5dDNi?= =?utf-8?B?cFVVUURXditHbHZOZnlxdjJRc29YWUhrMC9FTGJaRDd3T040SUF0cTFWcXF5?= =?utf-8?B?MTQyd3RRcnRhZ3Y3WE5VWWxLT0szVHlOMFhVcDR3RWRDWVRqUFhOaXNLbGJ0?= =?utf-8?B?MTROLzUyOTVVQlkvdlMwTXI0UkhyU0RMRTVod2tLWVE3aDY0cjFscWtTb3E2?= =?utf-8?B?WTBuOUFKM3NjZEkxU2FPaG4wVkxtOHh5YUlrRFBhTWxuNEYrNG4yQlU5MzFB?= =?utf-8?B?MkQ2T1BBdzlIT3JabVJYRWY4TllhMG0yTjR6eklsdlhHakhFZGpCZk4rVHYv?= =?utf-8?B?cERkN0xjTHMwb0F4bmg0aXdIcHlScCtzcFdGTDNnMHZ2V2dOU3V5Y0xsR2Zy?= =?utf-8?B?eGlVUUFZU0EwMmdyNitkY1p5enNsekhydFNzWS94eElWYlR0UXk4Q2FTcU1l?= =?utf-8?B?MWdLOWdBTHJnQ1d6NGtiYnFnSE5QaXAyZDNrYm12bEFKMFJ2aWpGM1lrOXN3?= =?utf-8?B?VjR1WXU3YmE4RDljcXc4dmhxWVgrNDA0V0dHalVEU09XcEY3WlF4T01XNjFX?= =?utf-8?B?NXdhNXBFaVp6QVpOcGs1UWhRaWZyT1ZFTjZrZnFhOUtnRVI0Z0tkczRPVCtw?= =?utf-8?B?S2p4b1RTTFBweVVwWEVGbUlJaUtVeTE1RTZmbmdCMEVyTFNBMXVjT0RVWldT?= =?utf-8?B?VmhFRHZ1M3poenVvZnF3Y3ZTVXVVaHNMMkRjTklNeU1sMGp1MkRmK0hScm5V?= =?utf-8?B?V1pzbG1QdjlvTk5xTDVJVVk3QnNiZGZhdEh3eGsxVkQxR2kwMW4vZTNkZUxj?= =?utf-8?B?Y1k5NDB5NzBlT25Rbld0K2svbk01NWZWd0hxcFRqVG1WVjNlTUVHNWR3VWFh?= =?utf-8?B?aUdsTVV6SmdtM0ZOUHg3N1Y5VGV6NXJrUTdXbklvL1lsZXNsMWtPOC9EWVBm?= =?utf-8?B?RmROdWFmbXNtZ2N6RkVlazJueTkxSmtjRERZcjd3a1hzTHN5ZHIxQ1VXZE9z?= =?utf-8?B?SkxnemdFMk5weS9xb0h0Q0lBYUFIeG9VckZZRmhRV29ZOXRnakw1UmZ4akxv?= =?utf-8?B?WlZ1UVR2NDZPSnJNbk51dHA1ZWpzeEJTc204QmtyZ2dDZXJBMHlaM05DSTI4?= =?utf-8?B?akc2bVk4SUQyd3ByZFBObWc0YVkzaHkxdGFLK2pVL0ZJNzBmM0FnRi8rUVdT?= =?utf-8?B?Tm5kZGxUQ0tUb3dQZlYwWDNiSm5pamxTM05RMHAxQ2pUNEhkTU1pWlA3eVBS?= =?utf-8?B?QWtTUjdjbXZhTFg1Njc4MzRVRitUcXhzbk8zdzVTOVFodFhIMmRhdE1Fcngv?= =?utf-8?B?RmVvNmpLUDg3eDRUQmhRWTYrVkIvNVJMWGxabTlpa1VMT1pjRXhBTDFzejUr?= =?utf-8?B?NlpOeVQ2cjJ1ZGk1ek11MzZjWG1scXVXV0dWM1ZtaGJZRUMrUHk2dUlhM0Y1?= =?utf-8?B?S1VtdmlWQ3dZVVF0NGh1bnM0MW9WM3NoSHhYL1I4dnBpTFBEaHBIWWliWWdV?= =?utf-8?B?Q1VCeGN2RTlNS2pqdEdtb3BwRjUvN05ZaTZJWmY5V011QmdOM3dsRTdMNDVa?= =?utf-8?Q?3fkd6uzvjzG9tZGSO+JRfRlFMmM8dw=3D?= X-Microsoft-Exchange-Diagnostics: 1; SN1PR11MB0687; 5:2tJ1G923rCEM56vXEEF1I0fXLJTtJx3xmiuOnr7dsqPQM/pNUFHPuB6VyWLvzgn+D9/L9JTVufpAFY0Zqx8JOfQgC+KfS0v1WEFixqE2hQHV/ujETMi/tR5s5Vg3z6tXvPouPLCuGA1n0BBf4Yq/Gg==; 24:8ZWKBFxMzBSAz0By6Q4xp9aXMJ8PG6GfTjRNt6L4XI7DzVOflaLXldxW1RSCCeZXqTxn+pout3/acOr/78KXCXUUn64/RuqjEDQ5s7Wg8uc= SpamDiagnosticOutput: 1:23 SpamDiagnosticMetadata: NSPM X-OriginatorOrg: bluetreble.com X-MS-Exchange-CrossTenant-OriginalArrivalTime: 16 Dec 2015 02:19:06.7810 (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-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org VACUUM VERBOSE spits out two different messages for the heap, one of which is rather confusing: INFO: "trades": removed 625664 row versions in 20967 pages ... INFO: "trades": found 3282 removable, 56891627 nonremovable row versions in 1986034 out of 1986034 pages After discussion with RhodiumToad I think I now understand how this can happen: 20:00 < RhodiumToad> the LP_DEAD slot is where the index entries for the deleted row point to, so that has to stay 20:01 < RhodiumToad> so for example, if you delete a lot of rows, then try and do a lot of updates (which will hint the pages as needing pruning), 20:01 < RhodiumToad> then do more updates or a seqscan (to let prune look at the pages), 20:02 < RhodiumToad> then do a vacuum, the vacuum will see a lot of LP_DEAD slots to remove index entries for, but not actual tuples This example is from a table that was VACUUM FULL'd this weekend and had a nightly batch process run last night. That process INSERTs a bunch of rows and then does a bunch of UPDATEs on different subsets of those rows. I don't believe there would have been a large amount of deletes; I'll check with them tomorrow. IMHO we need to change the messages so they are explicit about line pointers vs actual tuples. Trying to obfuscate that just leads to confusion. heap_page_prune needs to report only non-rootlp tuples that were pruned. (None of the other callers care about the return value.) -- 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers