Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 2F3132E003F for ; Sat, 24 May 2008 16:07:21 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 94653-10 for ; Sat, 24 May 2008 16:07:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by developer.postgresql.org (Postfix) with ESMTP id A88E92E002E for ; Sat, 24 May 2008 16:07:17 -0300 (ADT) Received: from [68.241.231.116] (68-241-231-116.area5.spcsdns.net [68.241.231.116]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id C836C56463; Sat, 24 May 2008 14:07:13 -0500 (CDT) In-Reply-To: References: <920841.71690.qm@web44808.mail.sp1.yahoo.com> <482B1D5F.EE98.0025.0@wicourts.gov> <20080515020553.GN9838@alvh.no-ip.org> <20080515155232.GC8221@alvh.no-ip.org> <20080516201528.GJ13061@alvh.no-ip.org> <18382.1210992302@sss.pgh.pa.us> Mime-Version: 1.0 (Apple Message framework v753) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-10--726898619; protocol="application/pkcs7-signature" Message-Id: Cc: pgsql-performance@postgresql.org From: Decibel! Subject: Re: I/O on select count(*) Date: Sat, 24 May 2008 15:06:56 -0400 To: Greg Smith X-Mailer: Apple Mail (2.753) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200805/327 X-Sequence-Number: 30295 --Apple-Mail-10--726898619 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On May 18, 2008, at 1:28 AM, Greg Smith wrote: > I just collected all the good internals information included in > this thread and popped it onto http://wiki.postgresql.org/wiki/ > Hint_Bits where I'll continue to hack away at the text until it's > readable. Thanks to everyone who answered my questions here, > that's good progress toward clearing up a very underdocumented area. > > I note a couple of potential TODO items not on the official list > yet that came up during this discussion: > > -Smooth latency spikes when switching commit log pages by > preallocating cleared pages before they are needed > > -Improve bulk loading by setting "frozen" hint bits for tuple > inserts which occur within the same database transaction as the > creation of the table into which they're being inserted > > Did I miss anything? I think everything brought up falls either > into one of those two or the existing "Consider having the > background writer update the transaction status hint bits..." TODO. -Evaluate impact of improved caching of CLOG per Greenplum: Per Luke Longergan: I'll find out if we can extract our code that did the work. It was simple but scattered in a few routines. In concept it worked like this: 1 - Ignore if hint bits are unset, use them if set. This affects heapam and vacuum I think. 2 - implement a cache for clog lookups based on the optimistic assumption that the data was inserted in bulk. Put the cache one call away from heapgetnext() I forget the details of (2). As I recall, if we fall off of the assumption, the penalty for long scans get large-ish (maybe 2X), but since when do people full table scan when they're updates/inserts are so scattered across TIDs? It's an obvious big win for DW work. -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 --Apple-Mail-10--726898619 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGtTCCAz8w ggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJBgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0 ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEaMBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcx KDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2VydmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTErMCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxA dGhhd3RlLmNvbTAeFw0wMzA3MTcwMDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpB MSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUg UGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEA xKY8VXNV+065yplaHmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7d yfArhVqqP3FWy688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/ p7bRPGEEQB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDow OKA2oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0xMzgw DQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYfqi2fNi/A 9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9lX5Xa9/eH1sYI Tq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8wggNuMIIC16ADAgECAhACpH6ZVSGg5xZh 5O/wRN/4MA0GCSqGSIb3DQEBBQUAMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29u c3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNz dWluZyBDQTAeFw0wNzEwMjkxOTE0NDdaFw0wODEwMjgxOTE0NDdaMIGqMQ4wDAYDVQQEEwVOYXNi eTETMBEGA1UEKhMKSmFtZXMgQ2FybDEZMBcGA1UEAxMQSmFtZXMgQ2FybCBOYXNieTEcMBoGCSqG SIb3DQEJARYNamltQG5hc2J5Lm5ldDEiMCAGCSqGSIb3DQEJARYTZGVjaWJlbEBkZWNpYmVsLm9y ZzEmMCQGCSqGSIb3DQEJARYXZGVjaWJlbEBkaXN0cmlidXRlZC5uZXQwggEiMA0GCSqGSIb3DQEB AQUAA4IBDwAwggEKAoIBAQDSgNcbvYYycE3FBX+z9i9zXtQxBalsoeDP92/jSG30Pq6HMlir3+lu rgZd5wBHRt/DMj+5ZfUDY9zQ7hfUio/WKQxRkXH46deV5xh9KNFf8kgxLiLKOBpesWFaXvNlnxU9 JKEpNZUnWeE8N/2mMWWs9L0Rfq0AxYa8TPfsbklALy1n9fY2yU1dNoT/s5tjRQK0UvzElimzMlOK 5PQuZ4atL9Jl6WCZb8811Qr+VUCFWz3e7HCgYDygwL+lBHlXEOLXPJ/lB8l61ugeooGjvuVdsaNf pM30pi5mwRECfOxo9zUL6uCHT6f4Dir6DsOIsTLb4N/vQjz+SGrLwtavSwRTAgMBAAGjWDBWMEYG A1UdEQQ/MD2BDWppbUBuYXNieS5uZXSBE2RlY2liZWxAZGVjaWJlbC5vcmeBF2RlY2liZWxAZGlz dHJpYnV0ZWQubmV0MAwGA1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEFBQADgYEApk/O70t8/38Jkp0M 9ywgSU2kn7w+Z3JN39pH3PnrLjmYV7D//2zJavR3zrZ4AeH2TDc/ioQTk1unfOYroYl+jUKD8NvU 8hbelR8pr+4hKMDx3+6plhKL0ZI9Zbw9DaJcZ3o8QK5fNGQooiz5+aCntbxdD81qG7+lkjQUUcvC wI0xggMQMIIDDAIBATB2MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGlu ZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBD QQIQAqR+mVUhoOcWYeTv8ETf+DAJBgUrDgMCGgUAoIIBbzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcN AQcBMBwGCSqGSIb3DQEJBTEPFw0wODA1MjQxOTA2NTdaMCMGCSqGSIb3DQEJBDEWBBReElApPpQN fQUjYBe7zg5VOC5a5zCBhQYJKwYBBAGCNxAEMXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMc VGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZy ZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE3/gwgYcGCyqGSIb3DQEJEAILMXigdjBi MQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoG A1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEAKkfplVIaDnFmHk7/BE 3/gwDQYJKoZIhvcNAQEBBQAEggEAi5yh0kwxvET/4ObkyZLGD3AUUtyVOd70Q5j9vXE3G9DpXSUl q5KGKdo4S6JwtpfTgfKTPuDI6rh7mXWaYYBKGE67P4mEAUGagNYeUcWcdkU4f8HYrEj1wFCNQnlc BjpKkAjmR06TLmHiGncKaWv6UQIm1xwWT/29endaLfs7ZudRo8V9LZApsbLvLb4Tmw1fEmZ60mtR OCoBp2JuGccAzpxGcrJTGO/SdLxez9vaS0qKMdA+ckcH4jGy2bAb8dzeL/jj9MbLzd+X4Sj6Y1MV yY26AEWT4UahT+1PZByjSCgTBilU8Wjt0jWsbN0Pof12HERbyMt9kg4650kgBNW4BgAAAAAAAA== --Apple-Mail-10--726898619--