Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2B2089FBE15 for ; Thu, 26 Apr 2007 18:36:37 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 04887-09 for ; Thu, 26 Apr 2007 18:36:34 -0300 (ADT) X-Greylist: delayed 00:23:32.040637 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 00B129FB912 for ; Thu, 26 Apr 2007 18:36:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from beast.liquidweb.com (beast.liquidweb.com [64.91.224.2]) by svr4.postgresql.org (Postfix) with ESMTP id 245185AF2CB for ; Thu, 26 Apr 2007 21:13:01 +0000 (GMT) Received: from c-24-11-214-165.hsd1.mi.comcast.net ([24.11.214.165]:62853 helo=[10.2.1.2]) by beast.liquidweb.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.52) id 1HhBGi-0002c2-9K; Thu, 26 Apr 2007 17:12:48 -0400 Message-ID: <463115D9.5040403@intellicon.biz> Date: Thu, 26 Apr 2007 17:12:57 -0400 From: Benjamin Minshall Organization: Intellicon, Inc. User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: zardozrocks Cc: pgsql-performance@postgresql.org Subject: Re: Simple query, 10 million records...MySQL ten times faster References: <1177450006.547698.120920@u32g2000prd.googlegroups.com> In-Reply-To: <1177450006.547698.120920@u32g2000prd.googlegroups.com> Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms080802070405050304060804" X-ClamAntiVirus-Scanner: This mail is clean X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - beast.liquidweb.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - intellicon.biz X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.007 tagged_above=0 required=5 tests=AWL=-1.006, BAYES_50=0.001, BIZ_TLD=2.013 X-Spam-Level: * X-Archive-Number: 200704/441 X-Sequence-Number: 24318 This is a cryptographically signed message in MIME format. --------------ms080802070405050304060804 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit zardozrocks wrote: > lat_radians numeric(6,5) DEFAULT 0.00000 NOT NULL, > long_radians numeric(6,5) DEFAULT 0.00000 NOT NULL Native data types such as integer or real are much faster than numeric. If you need 6 digits, it's better to multiply your coordinates by 10^6 and store as INTEGER. > On my development server (dual proc/dual core Opteron 2.8 Ghz with 4GB > ram) this query averages 1.5 seconds each time it runs after a brief > warmup period. In PostGreSQL it averages about 15 seconds. What hard drive(s) and controller(s) do you have? Please post EXPLAIN ANALYZE output of the problem query and your postgresql.conf also. -- Benjamin Minshall Senior Developer -- Intellicon, Inc. --------------ms080802070405050304060804 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIQODCC BIowggNyoAMCAQICECf06hH0eobEbp27bqkXBwcwDQYJKoZIhvcNAQEFBQAwbzELMAkGA1UE BhMCU0UxFDASBgNVBAoTC0FkZFRydXN0IEFCMSYwJAYDVQQLEx1BZGRUcnVzdCBFeHRlcm5h bCBUVFAgTmV0d29yazEiMCAGA1UEAxMZQWRkVHJ1c3QgRXh0ZXJuYWwgQ0EgUm9vdDAeFw0w NTA2MDcwODA5MTBaFw0yMDA1MzAxMDQ4MzhaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMC VVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5l dHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG 9w0BAQEFAAOCAQ8AMIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVN NRm5pELlzkniii8efNIxB8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQy lbsMTzC9mKALi+VuG6JG+ni8om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXq vgvOdjp6Dpvq/NonWz1zHyLmSGHGTPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6 hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7NlyP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu 9mIwFIws6wIDAQABo4HhMIHeMB8GA1UdIwQYMBaAFK29mHo0tCb3+sQmVO8DveAky1QaMB0G A1UdDgQWBBSJgmd9xJ0mcABLtFBIfN49rgRufTAOBgNVHQ8BAf8EBAMCAQYwDwYDVR0TAQH/ BAUwAwEB/zB7BgNVHR8EdDByMDigNqA0hjJodHRwOi8vY3JsLmNvbW9kb2NhLmNvbS9BZGRU cnVzdEV4dGVybmFsQ0FSb290LmNybDA2oDSgMoYwaHR0cDovL2NybC5jb21vZG8ubmV0L0Fk ZFRydXN0RXh0ZXJuYWxDQVJvb3QuY3JsMA0GCSqGSIb3DQEBBQUAA4IBAQAZ2IkRbyispgCi 54fBm5AD236hEv0e8+LwAamUVEJrmgnEoG3XkJIEA2Z5Q3H8+G+v23ZF4jcaPd3kWQR4rBz0 g0bzes9bhHIt5UbBuhgRKfPLSXmHPLptBZ2kbWhPrXIUNqi5sf2/z3/wpGqUNVCPz4FtVbHd WTBK322gnGQfSXzvNrv042n0+DmPWq1LhTq3Du3Tzw1EovsEv+QvcI4l+1pUBrPQxLxtjftz Mizpm4QkLdZ/kXpoAlAfDj9N6cz1u2fo3BwuO/xOzf4CjuOoEwqlJkRl6RDyTVKnrtw+ymsy XEFs/vVdoOr/0fqbhlhtPZZH5f4ulQTCAMyOofK7MIIF0TCCBLmgAwIBAgIRAO/VhC9jut1L VBHVMdY1Si4wDQYJKoZIhvcNAQEFBQAwga4xCzAJBgNVBAYTAlVTMQswCQYDVQQIEwJVVDEX MBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBVU0VSVFJVU1QgTmV0d29y azEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYwNAYDVQQDEy1VVE4tVVNF UkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwwHhcNMDcwMTAzMDAwMDAw WhcNMDgwMTAzMjM1OTU5WjCB5DE1MDMGA1UECxMsQ29tb2RvIFRydXN0IE5ldHdvcmsgLSBQ RVJTT05BIE5PVCBWQUxJREFURUQxRjBEBgNVBAsTPVRlcm1zIGFuZCBDb25kaXRpb25zIG9m IHVzZTogaHR0cDovL3d3dy5jb21vZG8ubmV0L3JlcG9zaXRvcnkxHzAdBgNVBAsTFihjKTIw MDMgQ29tb2RvIExpbWl0ZWQxGjAYBgNVBAMTEUJlbmphbWluIE1pbnNoYWxsMSYwJAYJKoZI hvcNAQkBFhdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejCBnzANBgkqhkiG9w0BAQEFAAOBjQAw gYkCgYEA4TykjizBp56X8r4qtMHghhYPSqjmfPc2khSEGsVmHcGZ4PvF3PcEzZAH7379blkA MofRC64IssMJnXhh6B4LZwPC6GxOK3TtsU0W76w1KBumVGWOGdz7qKbTCorRMlv2eZe4wQ/L DEdH3COLU14q0AaJPoKsSBp9s4QkRqGPP8kCAwEAAaOCAjQwggIwMB8GA1UdIwQYMBaAFImC Z33EnSZwAEu0UEh83j2uBG59MB0GA1UdDgQWBBTSE99IHnuw6o43lN4Ok9ErH4IjvDAOBgNV HQ8BAf8EBAMCBaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGy MQEDBQIwEQYJYIZIAYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCsw KQYIKwYBBQUHAgEWHWh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0w gZowTKBKoEiGRmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50 QXV0aGVudGljYXRpb25hbmRFbWFpbC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5l dC9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMIGGBggr BgEFBQcBAQR6MHgwOwYIKwYBBQUHMAKGL2h0dHA6Ly9jcnQuY29tb2RvY2EuY29tL1VUTkFk ZFRydXN0Q2xpZW50Q0EuY3J0MDkGCCsGAQUFBzAChi1odHRwOi8vY3J0LmNvbW9kby5uZXQv VVROQWRkVHJ1c3RDbGllbnRDQS5jcnQwIgYDVR0RBBswGYEXbWluc2hhbGxAaW50ZWxsaWNv bi5iaXowDQYJKoZIhvcNAQEFBQADggEBAFpPQSgEbe4qqAluhCVH5VVbS1IAAKEudK/pZB2A 4/wJD7W12XAtSzt4snOYEAecIU9YHrTAPqG592X30+7Vb3EsItTe5YQMUsHIYPM6vV1KYHRs gHTVi0qRnuQDrUsg6QjgSS/N1MaIbwBKXXBegsDQko27YuhTMe5nMr0Fk6MZn2HXIlVES4nu cpovUbNkipMeHNAh8zRC/04/Cs5n5eKarpM+xgedcOgNlsJoKrqzD8n6DRMm417xugLJvSPt ZO1ZpmGvqP4FHV2hu8/T2Xz6vklPHQfkY9ltnbmuyzhG7MfdVN2VVzZxVwpilMmdfpfO0dR/ +TiWRw3M5gl7rtcwggXRMIIEuaADAgECAhEA79WEL2O63UtUEdUx1jVKLjANBgkqhkiG9w0B AQUFADCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2Ug Q2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8v d3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xpZW50IEF1dGhl bnRpY2F0aW9uIGFuZCBFbWFpbDAeFw0wNzAxMDMwMDAwMDBaFw0wODAxMDMyMzU5NTlaMIHk MTUwMwYDVQQLEyxDb21vZG8gVHJ1c3QgTmV0d29yayAtIFBFUlNPTkEgTk9UIFZBTElEQVRF RDFGMEQGA1UECxM9VGVybXMgYW5kIENvbmRpdGlvbnMgb2YgdXNlOiBodHRwOi8vd3d3LmNv bW9kby5uZXQvcmVwb3NpdG9yeTEfMB0GA1UECxMWKGMpMjAwMyBDb21vZG8gTGltaXRlZDEa MBgGA1UEAxMRQmVuamFtaW4gTWluc2hhbGwxJjAkBgkqhkiG9w0BCQEWF21pbnNoYWxsQGlu dGVsbGljb24uYml6MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDhPKSOLMGnnpfyviq0 weCGFg9KqOZ89zaSFIQaxWYdwZng+8Xc9wTNkAfvfv1uWQAyh9ELrgiywwmdeGHoHgtnA8Lo bE4rdO2xTRbvrDUoG6ZUZY4Z3PuoptMKitEyW/Z5l7jBD8sMR0fcI4tTXirQBok+gqxIGn2z hCRGoY8/yQIDAQABo4ICNDCCAjAwHwYDVR0jBBgwFoAUiYJnfcSdJnAAS7RQSHzePa4Ebn0w HQYDVR0OBBYEFNIT30gee7DqjjeU3g6T0SsfgiO8MA4GA1UdDwEB/wQEAwIFoDAMBgNVHRMB Af8EAjAAMCAGA1UdJQQZMBcGCCsGAQUFBwMEBgsrBgEEAbIxAQMFAjARBglghkgBhvhCAQEE BAMCBSAwRgYDVR0gBD8wPTA7BgwrBgEEAbIxAQIBAQEwKzApBggrBgEFBQcCARYdaHR0cHM6 Ly9zZWN1cmUuY29tb2RvLm5ldC9DUFMwgaUGA1UdHwSBnTCBmjBMoEqgSIZGaHR0cDovL2Ny bC5jb21vZG9jYS5jb20vVVROLVVTRVJGaXJzdC1DbGllbnRBdXRoZW50aWNhdGlvbmFuZEVt YWlsLmNybDBKoEigRoZEaHR0cDovL2NybC5jb21vZG8ubmV0L1VUTi1VU0VSRmlyc3QtQ2xp ZW50QXV0aGVudGljYXRpb25hbmRFbWFpbC5jcmwwgYYGCCsGAQUFBwEBBHoweDA7BggrBgEF BQcwAoYvaHR0cDovL2NydC5jb21vZG9jYS5jb20vVVROQWRkVHJ1c3RDbGllbnRDQS5jcnQw OQYIKwYBBQUHMAKGLWh0dHA6Ly9jcnQuY29tb2RvLm5ldC9VVE5BZGRUcnVzdENsaWVudENB LmNydDAiBgNVHREEGzAZgRdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejANBgkqhkiG9w0BAQUF AAOCAQEAWk9BKARt7iqoCW6EJUflVVtLUgAAoS50r+lkHYDj/AkPtbXZcC1LO3iyc5gQB5wh T1getMA+obn3ZffT7tVvcSwi1N7lhAxSwchg8zq9XUpgdGyAdNWLSpGe5AOtSyDpCOBJL83U xohvAEpdcF6CwNCSjbti6FMx7mcyvQWToxmfYdciVURLie5ymi9Rs2SKkx4c0CHzNEL/Tj8K zmfl4pqukz7GB51w6A2WwmgqurMPyfoNEybjXvG6Asm9I+1k7VmmYa+o/gUdXaG7z9PZfPq+ SU8dB+Rj2W2dua7LOEbsx91U3ZVXNnFXCmKUyZ1+l87R1H/5OJZHDczmCXuu1zGCA9IwggPO AgEBMIHEMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFr ZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6 Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0 aGVudGljYXRpb24gYW5kIEVtYWlsAhEA79WEL2O63UtUEdUx1jVKLjAJBgUrDgMCGgUAoIIC YzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNzA0MjYyMTEy NTdaMCMGCSqGSIb3DQEJBDEWBBSvT3qwTYYv2P2cyAS7Y7TBo201djBSBgkqhkiG9w0BCQ8x RTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIAgDANBggqhkiG9w0DAgIBQDAHBgUrDgMC BzANBggqhkiG9w0DAgIBKDCB1QYJKwYBBAGCNxAEMYHHMIHEMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNF UlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQG A1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhEA 79WEL2O63UtUEdUx1jVKLjCB1wYLKoZIhvcNAQkQAgsxgceggcQwga4xCzAJBgNVBAYTAlVT MQswCQYDVQQIEwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYw NAYDVQQDEy1VVE4tVVNFUkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwC EQDv1YQvY7rdS1QR1THWNUouMA0GCSqGSIb3DQEBAQUABIGAOUCuvALUxFRi2CztZheUxtiL 0ABN2FpcFdfv2BD6T6WG/y1B+eVEke8XaNmCeb0twnvXv9Vh2ig3x4h4C4AABAhUuJqwoxUE 0vwpSgZEvK1XuMgNs2HMN6HyYereHUtHpQIdRtU5X11GReC+DsoGia97FisIegZGcuV0MXoN rj0AAAAAAAA= --------------ms080802070405050304060804--