Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQdNx-0001OG-Ew for pgsql-performance@arkaria.postgresql.org; Thu, 20 Mar 2014 13:51:53 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WQdNw-00071D-Lt for pgsql-performance@arkaria.postgresql.org; Thu, 20 Mar 2014 13:51:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQdNv-000717-Rr for pgsql-performance@postgresql.org; Thu, 20 Mar 2014 13:51:51 +0000 Received: from smtp07.smtpout.orange.fr ([80.12.242.129] helo=smtp.smtpout.orange.fr) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WQdNr-0004Yl-NI for pgsql-performance@postgresql.org; Thu, 20 Mar 2014 13:51:51 +0000 Received: from axege.com ([82.127.232.162]) by mwinf5d30 with ME id fprl1n00h3WtTCJ03prlGZ; Thu, 20 Mar 2014 14:51:46 +0100 Received: from localhost (localhost [127.0.0.1]) by axege.com (Postfix) with ESMTP id A16E8500F4 for ; Thu, 20 Mar 2014 14:51:45 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at myhostname="axege.com." Received: from axege.com ([127.0.0.1]) by localhost (axege.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ltKrplcmr9jd for ; Thu, 20 Mar 2014 14:51:45 +0100 (CET) Received: from [10.0.0.80] (unknown [10.0.0.80]) (using TLSv1 with cipher DHE-RSA-AES128-SHA (128/128 bits)) (No client certificate requested) by axege.com (Postfix) with ESMTPSA id 37F4C4FF14 for ; Thu, 20 Mar 2014 14:51:45 +0100 (CET) Message-ID: <532AF271.4040903@axege.com> Date: Thu, 20 Mar 2014 14:51:45 +0100 From: Franck Routier User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: long lasting select, no io nor cpu usage ? Content-Type: multipart/signed; protocol="application/pkcs7-signature"; micalg=sha1; boundary="------------ms000200050500050307040308" X-Pg-Spam-Score: -1.1 (-) 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 This is a cryptographically signed message in MIME format. --------------ms000200050500050307040308 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I have a table (sanact) with 23.125.525 rows (and a hundred columns). I am doing a select, that did not finish after some 15 hours... Select is as follows: select * from sanact where sanact___rfovsnide =3D 'MYVERSION' order by sanactcsu; There is an index on sanact___rfovsnide and doing EXPLAIN shows it is use= d. Resulting dataset should be 1626000 rows. iostat shows 99.5% idle disks, almost no activity. top shows almost no cpu usage. Where should I be looking for a problem ? Thanks in advance, Franck --------------ms000200050500050307040308 Content-Type: application/pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: Signature cryptographique S/MIME MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIMjTCC BjQwggQcoAMCAQICAR4wDQYJKoZIhvcNAQEFBQAwfTELMAkGA1UEBhMCSUwxFjAUBgNVBAoT DVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3VyZSBEaWdpdGFsIENlcnRpZmljYXRlIFNp Z25pbmcxKTAnBgNVBAMTIFN0YXJ0Q29tIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTA3 MTAyNDIxMDE1NVoXDTE3MTAyNDIxMDE1NVowgYwxCzAJBgNVBAYTAklMMRYwFAYDVQQKEw1T dGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRhbCBDZXJ0aWZpY2F0ZSBTaWdu aW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1hcnkgSW50ZXJtZWRpYXRlIENs aWVudCBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMcJg8zOLdgasSmkLhOr lr6KMoOMpohBllVHrdRvEg/q6r8jR+EK75xCGhR8ToREoqe7zM9/UnC6TS2y9UKTpT1v7RSM zR0t6ndl0TWBuUr/UXBhPk+Kmy7bI4yW4urC+y7P3/1/X7U8ocb8VpH/Clt+4iq7nirMcNh6 qJR+xjOhV+VHzQMALuGYn5KZmc1NbJQYclsGkDxDz2UbFqE2+6vIZoL+jb9x4Pa5gNf1TwSD kOkikZB1xtB4ZqtXThaABSONdfmv/Z1pua3FYxnCFmdr/+N2JLKutIxMYqQOJebr/f/h5t95 m4JgrM3Y/w7YX9d7YAL9jvN4SydHsU6n65cCAwEAAaOCAa0wggGpMA8GA1UdEwEB/wQFMAMB Af8wDgYDVR0PAQH/BAQDAgEGMB0GA1UdDgQWBBRTcu2SnODaywFcfH6WNU7y1LhRgjAfBgNV HSMEGDAWgBROC+8apEBbpRdphzDKNGhD0EGu8jBmBggrBgEFBQcBAQRaMFgwJwYIKwYBBQUH MAGGG2h0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9jYTAtBggrBgEFBQcwAoYhaHR0cDovL3d3 dy5zdGFydHNzbC5jb20vc2ZzY2EuY3J0MFsGA1UdHwRUMFIwJ6AloCOGIWh0dHA6Ly93d3cu c3RhcnRzc2wuY29tL3Nmc2NhLmNybDAnoCWgI4YhaHR0cDovL2NybC5zdGFydHNzbC5jb20v c2ZzY2EuY3JsMIGABgNVHSAEeTB3MHUGCysGAQQBgbU3AQIBMGYwLgYIKwYBBQUHAgEWImh0 dHA6Ly93d3cuc3RhcnRzc2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93 d3cuc3RhcnRzc2wuY29tL2ludGVybWVkaWF0ZS5wZGYwDQYJKoZIhvcNAQEFBQADggIBAAqD CH14qywGXLhjjF6uHLkjd02hcdh9hrw+VUsv+q1eeQWB21jWj3kJ96AUlPCoEGZ/ynJNScWy 6QMVQjbbMXltUfO4n4bGGdKo3awPWp61tjAFgraLJgDk+DsSvUD6EowjMTNx25GQgyYJ5RPI zKKR9tQW8gGK+2+RHxkUCTbYFnL6kl8Ch507rUdPPipJ9CgJFws3kDS3gOS5WFMxcjO5DwKf KSETEPrHh7p5shuuNktvsv6hxHTLhiMKX893gxdT3XLS9OKmCv87vkINQcNEcIIoFWbP9HOR z9v3vQwR4e3ksLc2JZOAFK+ssS5XMEoznzpihEP0PLc4dCBYjbvSD7kxgDwZ+Aj8Q9PkbvE9 sIPP7ON0fz095HdThKjiVJe6vofq+n6b1NBc8XdrQvBmunwxD5nvtTW4vtN6VY7mUCmxsCie uoBJ9OlqmsVWQvifIYf40dJPZkk9YgGTzWLpXDSfLSplbY2LL9C9U0ptvjcDjefLTvqSFc7t w1sEhF0n/qpA2r0GpvkLRDmcSwVyPvmjFBGqUp/pNy8ZuPGQmHwFi2/14+xeSUDG2bwnsYJQ G2EdJCB6luQ57GEnTA/yKZSTKI8dDQa8Sd3zfXb19mOgSF0bBdXbuKhEpuP9wirslFe6fQ1t 5j5R0xi72MZ8ikMu1RQZKCyDbMwazlHiMIIGUTCCBTmgAwIBAgIDB2ZsMA0GCSqGSIb3DQEB BQUAMIGMMQswCQYDVQQGEwJJTDEWMBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMi U2VjdXJlIERpZ2l0YWwgQ2VydGlmaWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20g Q2xhc3MgMSBQcmltYXJ5IEludGVybWVkaWF0ZSBDbGllbnQgQ0EwHhcNMTMwODI2MDQ1MjU5 WhcNMTQwODI2MTMyOTU4WjBnMRkwFwYDVQQNExBSaHc5cjVYRDFDczBwQ2UxMSEwHwYDVQQD DBhmcmFuY2sucm91dGllckBheGVnZS5jb20xJzAlBgkqhkiG9w0BCQEWGGZyYW5jay5yb3V0 aWVyQGF4ZWdlLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMBtxdyRa590 G0Dsbp/xnbLa+4kSmdL8M7Mn1XvQoXCcHyJ8rSNWFkTMdx4HRwloApWwcZQjsTLyH8Tib/ph tpUt8PmFwvO9xAPJJVwjpk7/FbzLKxJUkESBeBd51BGeLpijWMgGsxSnF2GRbnSa2uATIMwv DG6MkD5QxfnfjSnt+zvVgkNr1Uvzm6tSQ94kJKwsUb8CHyAden21l/vSqFrFrQT0YNYZsIOu I3JM0FmtTGsPZqcvVq0hECSBugUPU/Culf+jyQIKtYkUwErxfi5Zd77pUnMnoRdb193HD5h1 vKa5HGp7ZfTUbuS6GSruAkh94dFJopVzq1AcvclQTg0CAwEAAaOCAt4wggLaMAkGA1UdEwQC MAAwCwYDVR0PBAQDAgSwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAdBgNVHQ4E FgQU7ef8G/eVksbd8wI+Uc0mWZNZsxgwHwYDVR0jBBgwFoAUU3Ltkpzg2ssBXHx+ljVO8tS4 UYIwIwYDVR0RBBwwGoEYZnJhbmNrLnJvdXRpZXJAYXhlZ2UuY29tMIIBTAYDVR0gBIIBQzCC AT8wggE7BgsrBgEEAYG1NwECAzCCASowLgYIKwYBBQUHAgEWImh0dHA6Ly93d3cuc3RhcnRz c2wuY29tL3BvbGljeS5wZGYwgfcGCCsGAQUFBwICMIHqMCcWIFN0YXJ0Q29tIENlcnRpZmlj YXRpb24gQXV0aG9yaXR5MAMCAQEagb5UaGlzIGNlcnRpZmljYXRlIHdhcyBpc3N1ZWQgYWNj b3JkaW5nIHRvIHRoZSBDbGFzcyAxIFZhbGlkYXRpb24gcmVxdWlyZW1lbnRzIG9mIHRoZSBT dGFydENvbSBDQSBwb2xpY3ksIHJlbGlhbmNlIG9ubHkgZm9yIHRoZSBpbnRlbmRlZCBwdXJw b3NlIGluIGNvbXBsaWFuY2Ugb2YgdGhlIHJlbHlpbmcgcGFydHkgb2JsaWdhdGlvbnMuMDYG A1UdHwQvMC0wK6ApoCeGJWh0dHA6Ly9jcmwuc3RhcnRzc2wuY29tL2NydHUxLWNybC5jcmww gY4GCCsGAQUFBwEBBIGBMH8wOQYIKwYBBQUHMAGGLWh0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNv bS9zdWIvY2xhc3MxL2NsaWVudC9jYTBCBggrBgEFBQcwAoY2aHR0cDovL2FpYS5zdGFydHNz bC5jb20vY2VydHMvc3ViLmNsYXNzMS5jbGllbnQuY2EuY3J0MCMGA1UdEgQcMBqGGGh0dHA6 Ly93d3cuc3RhcnRzc2wuY29tLzANBgkqhkiG9w0BAQUFAAOCAQEAxjsGc89xSZj1umSkHlIo nuyuYM+bUNmNIQ1OU9bPl56e+fDvNLrunMA7r/5mw2HBu3+19m56XLCLhubYtHSALsRujrpu Nu/AstKgPBwqMk70j1KfdZoULvlrgkZSotisS/nnTF7C8Gb/OwOEY914vDy1aFd0AKFOG2xG 1VCmNeDR/mmadwqwTUU0OU+mxeRtkqIhAaEuh+A/7yyr1vM4+j30k670d+5knfsz7wBIMtOB IoL9XFBxsnlWRGBABM/0wJq+DBwzbJgiCwI7ScKVwcLzSwbh5TDZbc84DE5UEDPw9ksoEElh lG0bAMlS+DMH1/RDjtyrrENlqIwjABRTETGCA90wggPZAgEBMIGUMIGMMQswCQYDVQQGEwJJ TDEWMBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMiU2VjdXJlIERpZ2l0YWwgQ2Vy dGlmaWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20gQ2xhc3MgMSBQcmltYXJ5IElu dGVybWVkaWF0ZSBDbGllbnQgQ0ECAwdmbDAJBgUrDgMCGgUAoIICHTAYBgkqhkiG9w0BCQMx CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xNDAzMjAxMzUxNDVaMCMGCSqGSIb3DQEJ BDEWBBT/7QjIyns2/NPYr/y8GJlhAtiAQjBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQB KjALBglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMC AgFAMAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGlBgkrBgEEAYI3EAQxgZcwgZQwgYwxCzAJ BgNVBAYTAklMMRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGln aXRhbCBDZXJ0aWZpY2F0ZSBTaWduaW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFBy aW1hcnkgSW50ZXJtZWRpYXRlIENsaWVudCBDQQIDB2ZsMIGnBgsqhkiG9w0BCRACCzGBl6CB lDCBjDELMAkGA1UEBhMCSUwxFjAUBgNVBAoTDVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNl Y3VyZSBEaWdpdGFsIENlcnRpZmljYXRlIFNpZ25pbmcxODA2BgNVBAMTL1N0YXJ0Q29tIENs YXNzIDEgUHJpbWFyeSBJbnRlcm1lZGlhdGUgQ2xpZW50IENBAgMHZmwwDQYJKoZIhvcNAQEB BQAEggEAr+l1BjDzA2gHAfZxS52tQzNl+xq/gwToi6fSP9GslzCW9og7aDH0gJ4YwA/o7YF7 IbK3IsZUTPgxp6QHt+PBXsP0aDYnxlZOlGkWjjgPcMM3jb+Df/gxLLC63/s6zVcKitUoFfll V9ImMPo4q9T9wic9LxyMrLx7ZUE7ypIuz7kcl0zPJShUBLx1D/VhXlNSKgBpRFyb7ess03uG icAnF/ZajlbXb6YEH+O73cxLxzoFsx4xyFcPhSpad7VnZkOOAXMzclHwxB5gDH+tQLTyYDhe O3GxcNlc6lTGpsP2PB0ZHRpnMARUV01Cy3/l6PF+KL/JbbJIq04AJjLiSUlSowAAAAAAAA== --------------ms000200050500050307040308--