X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 416939FA463 for ; Tue, 18 Jul 2006 08:29:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 19254-01-2 for ; Tue, 18 Jul 2006 08:29:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.l39a.com (host123-67.pool8288.interbusiness.it [82.88.67.123]) by postgresql.org (Postfix) with ESMTP id A98149FB34B for ; Tue, 18 Jul 2006 04:53:46 -0300 (ADT) Received: from [172.16.0.1] (unknown [151.57.246.150]) by mail.l39a.com (Postfix) with ESMTP id EE7F325F05E; Tue, 18 Jul 2006 09:25:41 +0200 (CEST) Subject: Re: Big differences in plans between 8.0 and 8.1 From: Gabriele Turchi To: Joe Conway Cc: pgsql-performance@postgresql.org, Marco Colombo In-Reply-To: <44BA8087.9010007@joeconway.com> References: <1152972852.3541.0.camel@apollo5.casa.intranet> <20060715190210.GA76277@winnie.fuhr.org> <1152993350.3541.22.camel@apollo5.casa.intranet> <44B94A51.9050108@joeconway.com> <1152994970.3541.31.camel@apollo5.casa.intranet> <44BA8087.9010007@joeconway.com> Content-Type: multipart/signed; micalg=sha1; protocol="application/x-pkcs7-signature"; boundary="=-PXfyne6Da8wGlTjYuCnZ" Organization: L39A S.r.l. Date: Tue, 18 Jul 2006 09:25:40 +0200 Message-Id: <1153207541.3323.10.camel@apollo5.casa.intranet> Mime-Version: 1.0 X-Mailer: Evolution 2.6.2 (2.6.2-1.fc5.5) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200607/175 X-Sequence-Number: 20035 --=-PXfyne6Da8wGlTjYuCnZ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Il giorno dom, 16/07/2006 alle 11.08 -0700, Joe Conway ha scritto: > Gabriele Turchi wrote: > > Il giorno sab, 15/07/2006 alle 13.04 -0700, Joe Conway ha scritto: > >>Why not just periodically (once an hour?) run "ANALYZE registrazioni;"=20 > >>during the day. This will only update the statistics, and should be ver= y=20 > >>low impact. > >=20 > > This is my "solution" too... but: is enough? Or else: there is a better > > way to do this? If the performance in the better case is 50 times faste= r > > than the worse case, during an hour (50/100 record inserted in > > "registrazioni") how much the performance can fall before the new > > "ANALYZE" is run? Otherwise, running ANALYZE more frequently can badly > > affect the overall performance? >=20 > One thing I noticed is that in both plans there is a seq scan on=20 > registrazioni. Given that performance degrades so quickly as records are=20 > inserted into registrazioni, I'm wondering if you're missing an index.=20 > What indexes do you have on registrazioni? >=20 > Joe No one. The application was not fine-tuned, because the original performance (under 8.0) was "more than enough". I thought that creating an index on a table with no more than some hundred of records was not useful... My biggest doubt is anyway related to the very big difference between the plans in 8.0 and 8.1 under the same conditions.=20 Thank you, Gabriele --=-PXfyne6Da8wGlTjYuCnZ Content-Type: application/x-pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIITwDCCBhsw ggQDoAMCAQICAQUwDQYJKoZIhvcNAQEFBQAwgaExCzAJBgNVBAYTAklUMQ8wDQYDVQQIEwZNaWxh bm8xDzANBgNVBAcTBk1pbGFubzEUMBIGA1UEChMLTDM5QSBTLnIubC4xGTAXBgNVBAsTEE5ldHdv cmsgU2VydmljZXMxIzAhBgNVBAMTGkwzOUEgQ0EgLSBOZXR3b3JrIFNlcnZpY2VzMRowGAYJKoZI hvcNAQkBFgtjYUBsMzlhLmNvbTAeFw0wNjA2MTYyMDA3NDNaFw0wNzA2MTYyMDA3NDNaMIGIMQsw CQYDVQQGEwJJVDEPMA0GA1UECBMGTWlsYW5vMQ8wDQYDVQQHEwZNaWxhbm8xFDASBgNVBAoTC0wz OUEgUy5yLmwuMRgwFgYDVQQDEw9HYWJyaWVsZSBUdXJjaGkxJzAlBgkqhkiG9w0BCQEWGGdhYnJp ZWxlLnR1cmNoaUBsMzlhLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBALxyBPhE N4pw/mC4rmStsbJRu94X7u4Me/D5Mw7mRbo7Q/d6HI8+NjPnr+SHf0Q6PSD1qnd8z3QpTBuT6OYg Hxw5AoUHtF2HQTL+lVxfPzDWfG/KQPLdkRz8RGeRWC4rwnjV4qVqWwzXpfiTFqDly/0MoE/YyZkY mz9aHyao6SX+2fWm5nA7xA8c1dHtAoeBzZAkxzusV1M7xJzRqh7QMLUmLxXfKOrTGwfgLkgifuPG lQ3DHtYAWh5wm8hk8APuJBL6E+TXNwko6cFTYO1gJJLeXkBCFtAJ5PehDUvx2DLb7JRFpLrMkI0b NVUCAP8A328DtG/2bZNa1sTCv/Z0KocCAwEAAaOCAXMwggFvMAkGA1UdEwQCMAAwEQYJYIZIAYb4 QgEBBAQDAgSwMCsGCWCGSAGG+EIBDQQeFhxUaW55Q0EgR2VuZXJhdGVkIENlcnRpZmljYXRlMB0G A1UdDgQWBBTi7lQV2CuA9AlbEF+Ey0r7v/6kIDCBtQYDVR0jBIGtMIGqgBS0j92r9jZFDtjQ2Bbi W+oNAWS2faGBjqSBizCBiDELMAkGA1UEBhMCSVQxDzANBgNVBAgTBk1pbGFubzEPMA0GA1UEBxMG TWlsYW5vMRQwEgYDVQQKEwtMMzlBIFMuci5sLjElMCMGA1UEAxMcTDM5QSBDZXJ0aWZpY2F0aW9u IEF1dGhvcml0eTEaMBgGCSqGSIb3DQEJARYLY2FAbDM5YS5jb22CAQEwFgYDVR0SBA8wDYELY2FA bDM5YS5jb20wIwYDVR0RBBwwGoEYZ2FicmllbGUudHVyY2hpQGwzOWEuY29tMA4GA1UdDwEB/wQE AwIFoDANBgkqhkiG9w0BAQUFAAOCAgEAMTOj9fYhh/Gqr08Xt5eAaTldPCwPwS2uzGpQrAby5/vZ 7AgRZQ/ZW7juJYV/vFmzP7B5RaJJ212gXzS8pd//98obILEVe872e0G6QRNtQYOLmkSxtCPmoMO3 sabxQt/zyfCtC+1P2S9SHWCfrw3s/y9pvBxnBumgTStJjmLPtYPsh02ANN6OgAjxyQndwoiuMKp+ 4VBILxsCTylh/ivhIhvGIYga6A61WN1tDEVLOf6I4vbzgZajS/mHeGTD0ldEQVTJrkZp4iF4DMfP zvJzAF01BUPe8Y7WgDuwldSJgokTnKUSIv64jJ21IJzHXtcxP38Op6qzG9N2I2zGFIqmgLfOkDL4 d1kv55iINueydrNOSv/59TmdMVI/xe1B7usg8sa/r2vjjey0gibQUHmr2LAJ12jNOZPTd/AxvDHJ UbYZJ2C4tW0aVfC0fo0i+DAcjHVRofRFpf6l0SIBFFSm0AeODr8jtSxvdroDc5JWkVMgewRDEIZV KL5UiYR2/xiSQWqhq0d8+e8RZ5KNG60fRksyoNu3oIstNuGeLj5ILSPFsGqYCl/6Wp9ftiIcmZUz dWWNotjUwjggltqHnFA8mXwHmLCNT74XDHuZx6MtWDS7JXkj5hqkRY5zsn0mUpJ7DmrAdcE0UVy2 kaplPQEXGkiF0mFVfKV+yvTkWMeVoa0wggYbMIIEA6ADAgECAgEFMA0GCSqGSIb3DQEBBQUAMIGh MQswCQYDVQQGEwJJVDEPMA0GA1UECBMGTWlsYW5vMQ8wDQYDVQQHEwZNaWxhbm8xFDASBgNVBAoT C0wzOUEgUy5yLmwuMRkwFwYDVQQLExBOZXR3b3JrIFNlcnZpY2VzMSMwIQYDVQQDExpMMzlBIENB IC0gTmV0d29yayBTZXJ2aWNlczEaMBgGCSqGSIb3DQEJARYLY2FAbDM5YS5jb20wHhcNMDYwNjE2 MjAwNzQzWhcNMDcwNjE2MjAwNzQzWjCBiDELMAkGA1UEBhMCSVQxDzANBgNVBAgTBk1pbGFubzEP MA0GA1UEBxMGTWlsYW5vMRQwEgYDVQQKEwtMMzlBIFMuci5sLjEYMBYGA1UEAxMPR2FicmllbGUg VHVyY2hpMScwJQYJKoZIhvcNAQkBFhhnYWJyaWVsZS50dXJjaGlAbDM5YS5jb20wggEiMA0GCSqG SIb3DQEBAQUAA4IBDwAwggEKAoIBAQC8cgT4RDeKcP5guK5krbGyUbveF+7uDHvw+TMO5kW6O0P3 ehyPPjYz56/kh39EOj0g9ap3fM90KUwbk+jmIB8cOQKFB7Rdh0Ey/pVcXz8w1nxvykDy3ZEc/ERn kVguK8J41eKlalsM16X4kxag5cv9DKBP2MmZGJs/Wh8mqOkl/tn1puZwO8QPHNXR7QKHgc2QJMc7 rFdTO8Sc0aoe0DC1Ji8V3yjq0xsH4C5IIn7jxpUNwx7WAFoecJvIZPAD7iQS+hPk1zcJKOnBU2Dt YCSS3l5AQhbQCeT3oQ1L8dgy2+yURaS6zJCNGzVVAgD/AN9vA7Rv9m2TWtbEwr/2dCqHAgMBAAGj ggFzMIIBbzAJBgNVHRMEAjAAMBEGCWCGSAGG+EIBAQQEAwIEsDArBglghkgBhvhCAQ0EHhYcVGlu eUNBIEdlbmVyYXRlZCBDZXJ0aWZpY2F0ZTAdBgNVHQ4EFgQU4u5UFdgrgPQJWxBfhMtK+7/+pCAw gbUGA1UdIwSBrTCBqoAUtI/dq/Y2RQ7Y0NgW4lvqDQFktn2hgY6kgYswgYgxCzAJBgNVBAYTAklU MQ8wDQYDVQQIEwZNaWxhbm8xDzANBgNVBAcTBk1pbGFubzEUMBIGA1UEChMLTDM5QSBTLnIubC4x JTAjBgNVBAMTHEwzOUEgQ2VydGlmaWNhdGlvbiBBdXRob3JpdHkxGjAYBgkqhkiG9w0BCQEWC2Nh QGwzOWEuY29tggEBMBYGA1UdEgQPMA2BC2NhQGwzOWEuY29tMCMGA1UdEQQcMBqBGGdhYnJpZWxl LnR1cmNoaUBsMzlhLmNvbTAOBgNVHQ8BAf8EBAMCBaAwDQYJKoZIhvcNAQEFBQADggIBADEzo/X2 IYfxqq9PF7eXgGk5XTwsD8EtrsxqUKwG8uf72ewIEWUP2Vu47iWFf7xZsz+weUWiSdtdoF80vKXf //fKGyCxFXvO9ntBukETbUGDi5pEsbQj5qDDt7Gm8ULf88nwrQvtT9kvUh1gn68N7P8vabwcZwbp oE0rSY5iz7WD7IdNgDTejoAI8ckJ3cKIrjCqfuFQSC8bAk8pYf4r4SIbxiGIGugOtVjdbQxFSzn+ iOL284GWo0v5h3hkw9JXREFUya5GaeIheAzHz87ycwBdNQVD3vGO1oA7sJXUiYKJE5ylEiL+uIyd tSCcx17XMT9/DqeqsxvTdiNsxhSKpoC3zpAy+HdZL+eYiDbnsnazTkr/+fU5nTFSP8XtQe7rIPLG v69r443stIIm0FB5q9iwCddozTmT03fwMbwxyVG2GSdguLVtGlXwtH6NIvgwHIx1UaH0RaX+pdEi ARRUptAHjg6/I7Usb3a6A3OSVpFTIHsEQxCGVSi+VImEdv8YkkFqoatHfPnvEWeSjRutH0ZLMqDb t6CLLTbhni4+SC0jxbBqmApf+lqfX7YiHJmVM3VljaLY1MI4IJbah5xQPJl8B5iwjU++Fwx7mcej LVg0uyV5I+YapEWOc7J9JlKSew5qwHXBNFFctpGqZT0BFxpIhdJhVXylfsr05FjHlaGtMIIHfjCC BWagAwIBAgIBATANBgkqhkiG9w0BAQUFADCBiDELMAkGA1UEBhMCSVQxDzANBgNVBAgTBk1pbGFu bzEPMA0GA1UEBxMGTWlsYW5vMRQwEgYDVQQKEwtMMzlBIFMuci5sLjElMCMGA1UEAxMcTDM5QSBD ZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTEaMBgGCSqGSIb3DQEJARYLY2FAbDM5YS5jb20wHhcNMDYw NjE2MTk1NzQ5WhcNMTYwNjEzMTk1NzQ5WjCBoTELMAkGA1UEBhMCSVQxDzANBgNVBAgTBk1pbGFu bzEPMA0GA1UEBxMGTWlsYW5vMRQwEgYDVQQKEwtMMzlBIFMuci5sLjEZMBcGA1UECxMQTmV0d29y ayBTZXJ2aWNlczEjMCEGA1UEAxMaTDM5QSBDQSAtIE5ldHdvcmsgU2VydmljZXMxGjAYBgkqhkiG 9w0BCQEWC2NhQGwzOWEuY29tMIICIjANBgkqhkiG9w0BAQEFAAOCAg8AMIICCgKCAgEAqw0RxtTA yBcyp7+wJXAaQUrQLVnplfSkeZCNp/o9noMOkVwiZC+heihKEMGwGeHL/5fEB0LlKu0H1tEwYiD0 Q8DxfCRnRM4eqN2JMN+j2U+beP5Jo7HLKknXWzQxJdST9N55+YNTA4DuPZ8xPyEsz9VLz9g39aOX RKQnSN6ErY+XCzP0guR8Yg9MsrmIF6OqsZ9/FtariqpsnZwdYNhHvgImWOFcahbS2TJmq0S1KEGE qXHLgqekg21eIdBT05DPbCBsMF8Qnf1hOZJU2gkaTlQmuAsO7FBGgwGulb3HgVcoY4OhPVQNQ9ZV WmMIggAsW5w6qXnMe2NEbNFFnFRsUIc/XDekN/oE8yBsaDMgadm8QyQ22jYJ6pZmML+oAI3UrD81 3XbPLO51Sx1VQ0TwFVK7yByHqRHT9Cf20LYKdZmYwqipdiHRS/WNuMrS1p4b2r+gJ3Afq/1podLH 46cYUoEyfADlmge5Ivv8KBlMnblNFtDnQg392aqwJfH61z6vo8HKw0uuXp3GYI6PscIClGeKuFQv o+bmeX4f+d4+nsojkTJmyyBKeykY4GwY2Kd+o3qHL/7sLfGhRShh7640PhhbbLfOJwJQP5emr+Ak RU7EKZlvy17u1rxT3+IZ1dw5zbBNRlzSJT3AyoXV0NMJourN6CB1X6TgEYKBjZ/5CHcCAwEAAaOC AdYwggHSMB0GA1UdDgQWBBS0j92r9jZFDtjQ2BbiW+oNAWS2fTCBvQYDVR0jBIG1MIGygBRo7L5b yARXiSJ6ymK8nDPhVmqbaKGBjqSBizCBiDELMAkGA1UEBhMCSVQxDzANBgNVBAgTBk1pbGFubzEP MA0GA1UEBxMGTWlsYW5vMRQwEgYDVQQKEwtMMzlBIFMuci5sLjElMCMGA1UEAxMcTDM5QSBDZXJ0 aWZpY2F0aW9uIEF1dGhvcml0eTEaMBgGCSqGSIb3DQEJARYLY2FAbDM5YS5jb22CCQCaZuS1ORKm YTAPBgNVHRMBAf8EBTADAQH/MBEGCWCGSAGG+EIBAQQEAwIBBjAWBgNVHRIEDzANgQtjYUBsMzlh LmNvbTArBglghkgBhvhCAQ0EHhYcVGlueUNBIEdlbmVyYXRlZCBDZXJ0aWZpY2F0ZTAtBglghkgB hvhCAQQEIBYeaHR0cDovL3d3dy5sMzlhLmNvbS9jYS1jcmwucGVtMDEGCWCGSAGG+EIBCAQkFiJo dHRwOi8vd3d3LmwzOWEuY29tL2NhLXBvbGljeS5odG1sMBYGA1UdEQQPMA2BC2NhQGwzOWEuY29t MA4GA1UdDwEB/wQEAwIBBjANBgkqhkiG9w0BAQUFAAOCAgEAa5lu8evYsYln13Xul8DMrv4EQqr5 V0Cy2yPjSGjiqkrn7VaPScgc/SU1jumsLoQ7gzpMYmg2QkzQACQIIKKlPvYn0AnsuKoFg7bBpq2K /z8PJ+13/pJSFcoUaD12viPgQW0baX751HixhNaZLcfvkQDfe+8pKpS/IpI8UjhqU24zBTakVMcL tL84C9ZRiixLtU5CoKb6/irV+s3kDd45AZ/z+ut/n7bNaBpBuqQTly+m2AILH9ghj6d0sC0tduII FU7hK2Cg8tJ8LtkYP4M2a7a0WcClz0H5ZMLCSyrymae1atKc7wu77+DH7xv2EGKmUhXHjCyHHGCK 12uTLQnBc/poz+iTOpMt2+xKFbhAHoWYoYDPZc9zVycJq3zT8qTVO+zxac9lpTCcChjV3fsWMiZT 7yNjxZlXDRA+/0EksL4nSsNzJxbb80Q5EEV3y0NmOQj1hMF94e+NOc/9LjIf5nbhCA9nXVA8tf3k UC8o5ENhslCSE8pmeJh87gRzenP46ymKTkRURfpiFx07WHCoLGr4nYF/H/oUvfoCUsUSY7q7biqP ukIcQAif7PxaqvBJaCIiIFhcnnCHb7kvlA8KtolINmyFwWWfKx0dafZeybcfftDfoYumIO+U3++J B2S+bzMbJ+9n/2MPVPIcCLnBftWuHVoCK+JQ5Uan0KKA3b4xggOoMIIDpAIBATCBpzCBoTELMAkG A1UEBhMCSVQxDzANBgNVBAgTBk1pbGFubzEPMA0GA1UEBxMGTWlsYW5vMRQwEgYDVQQKEwtMMzlB IFMuci5sLjEZMBcGA1UECxMQTmV0d29yayBTZXJ2aWNlczEjMCEGA1UEAxMaTDM5QSBDQSAtIE5l dHdvcmsgU2VydmljZXMxGjAYBgkqhkiG9w0BCQEWC2NhQGwzOWEuY29tAgEFMAkGBSsOAwIaBQCg ggHVMBgGCSqGSIb3DQEJAzELBgkqhkiG9w0BBwEwHAYJKoZIhvcNAQkFMQ8XDTA2MDcxODA3MjU0 MFowIwYJKoZIhvcNAQkEMRYEFC08I0U8FKVecELLGcv+wiqKJp1BMIG4BgkrBgEEAYI3EAQxgaow gacwgaExCzAJBgNVBAYTAklUMQ8wDQYDVQQIEwZNaWxhbm8xDzANBgNVBAcTBk1pbGFubzEUMBIG A1UEChMLTDM5QSBTLnIubC4xGTAXBgNVBAsTEE5ldHdvcmsgU2VydmljZXMxIzAhBgNVBAMTGkwz OUEgQ0EgLSBOZXR3b3JrIFNlcnZpY2VzMRowGAYJKoZIhvcNAQkBFgtjYUBsMzlhLmNvbQIBBTCB ugYLKoZIhvcNAQkQAgsxgaqggacwgaExCzAJBgNVBAYTAklUMQ8wDQYDVQQIEwZNaWxhbm8xDzAN BgNVBAcTBk1pbGFubzEUMBIGA1UEChMLTDM5QSBTLnIubC4xGTAXBgNVBAsTEE5ldHdvcmsgU2Vy dmljZXMxIzAhBgNVBAMTGkwzOUEgQ0EgLSBOZXR3b3JrIFNlcnZpY2VzMRowGAYJKoZIhvcNAQkB FgtjYUBsMzlhLmNvbQIBBTANBgkqhkiG9w0BAQEFAASCAQA7ZbkGGFC9EmFg8vmKqoin+LYiaXuY IJrU63t1+cjQdxDB2GzHeqXzRQyQ1R9f5kpJy6X3THrYabeZCW5BQ1FPEBMG6iTuR45YO3/AfmLS Drp+n5lLn94CMbP/CiYSWD4jeBo+79BExop9SLVwDtAQBR2NBZcGbM4hSkOoF0aTKIVyYq7vV0KD 1b6IOZ7DgV8kka8fWV9D8NfaMSQgkAj6g8RY7HtScE1qZxmc7EqsbxxGAFbR5C+jiX2cNQhQg0uo V1kobHZkw/vDLE/p5LMjV+045yHLYNW19B+EJJblfJe7L3hzZo3Zk2rRRq9mWr6SWgIOvntNZqGC IPvIc8UtAAAAAAAA --=-PXfyne6Da8wGlTjYuCnZ--