X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C9DB69F9F49 for ; Fri, 15 Sep 2006 17:04:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 01850-01 for ; Fri, 15 Sep 2006 20:04:31 +0000 (UTC) X-Greylist: delayed 00:52:18.698167 by SQLgrey- Received: from beast.liquidweb.com (beast.liquidweb.com [64.91.224.2]) by postgresql.org (Postfix) with ESMTP id 085EE9F9FB2 for ; Fri, 15 Sep 2006 17:04:30 -0300 (ADT) Received: from c-67-167-182-52.hsd1.mi.comcast.net ([67.167.182.52]:63754 helo=[10.2.1.2]) by beast.liquidweb.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.52) id 1GOJ6V-0006zn-1X for pgsql-performance@postgresql.org; Fri, 15 Sep 2006 15:11:59 -0400 Message-ID: <450AFB07.5050600@intellicon.biz> Date: Fri, 15 Sep 2006 15:12:07 -0400 From: Benjamin Minshall Organization: Intellicon, Inc. User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Performance of IN (...) vs. = ANY array[...] Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms060906090300080405070004" 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=2.013 tagged_above=0 required=5 tests=BIZ_TLD X-Spam-Level: ** X-Archive-Number: 200609/212 X-Sequence-Number: 20898 This is a cryptographically signed message in MIME format. --------------ms060906090300080405070004 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Greetings: I'm running 8.1.4, and have noticed major differences in execution time for plpgsql functions running queries that differ only in use of an array such as: slower_function( vals integer[] ) [query] WHERE id = ANY vals; faster_function( vals integer[] ) vals_text := array_to_string( vals, ',' ) EXECUTE '[query] WHERE id IN (' || vals_text || ')'; In general, there are about 10 integers in the lookup set on average and 50 max. What are the advantages or disadvantages of using arrays in this situation? The = ANY array method makes plpgsql development cleaner, but seems to really lack performance in certain cases. What do you recommend as the preferred method? Thanks for your comments. -- Benjamin Minshall Senior Developer -- Intellicon, Inc. http://www.intellicon.biz --------------ms060906090300080405070004 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 BSowggSToAMCAQICBAIAApowDQYJKoZIhvcNAQEFBQAwdTELMAkGA1UEBhMCVVMxGDAWBgNV BAoTD0dURSBDb3Jwb3JhdGlvbjEnMCUGA1UECxMeR1RFIEN5YmVyVHJ1c3QgU29sdXRpb25z LCBJbmMuMSMwIQYDVQQDExpHVEUgQ3liZXJUcnVzdCBHbG9iYWwgUm9vdDAeFw0wMjA4Mjcx OTAyMDBaFw0xMjA4MjcyMzU5MDBaMIHcMQswCQYDVQQGEwJHQjEXMBUGA1UEChMOQ29tb2Rv IExpbWl0ZWQxHTAbBgNVBAsTFENvbW9kbyBUcnVzdCBOZXR3b3JrMUYwRAYDVQQLEz1UZXJt cyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0 b3J5MR8wHQYDVQQLExYoYykyMDAyIENvbW9kbyBMaW1pdGVkMSwwKgYDVQQDEyNDb21vZG8g Q2xhc3MgMyBTZWN1cml0eSBTZXJ2aWNlcyBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC AQoCggEBAPIMts+HhOoKcRRSLehXXElSOs/D8EJtYvL+puk+PHhkAw4gfmtraYeJVDgF9NrH BUAFGHd+/GuLDhyigsyshZs56ctLSBhIayjkwtFbrgOpfpgvTBNx37ApDE6qpjcmOLnQRFKC kD5wSNZ6mwkHvJTk9FUs8rcqvhyRHGqLg001bBDhc9oh2WLY8Rmw8ecgjbWXRyNLaoE1G69I 4DaokExNTOz1iM7GsdAv/3mQAdhRZOcvsQiNXc+t+rKAlqusi7VMtHTCRxPt6CMx9Cq5YRu9 zqRbNzG95Zyc+7IP18U7v8xaqw/IhkCJIqaQINOzbL4S8+MgLLvBrkNa2hZ26B8CAwEAAaOC AdkwggHVMEUGA1UdHwQ+MDwwOqA4oDaGNGh0dHA6Ly93d3cucHVibGljLXRydXN0LmNvbS9j Z2ktYmluL0NSTC8yMDE4L2NkcC5jcmwwHQYDVR0OBBYEFDbg6HxtnUWR7pnlQnZNcLNQMKxe MIGSBgNVHSAEgYowgYcwSQYKKoZIhvhjAQIBBTA7MDkGCCsGAQUFBwIBFi1odHRwOi8vd3d3 LnB1YmxpYy10cnVzdC5jb20vQ1BTL09tbmlSb290Lmh0bWwwOgYMKwYBBAGyMQECAQMBMCow KAYIKwYBBQUHAgEWHGh0dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1AwgYkGA1UdIwSBgTB/ oXmkdzB1MQswCQYDVQQGEwJVUzEYMBYGA1UEChMPR1RFIENvcnBvcmF0aW9uMScwJQYDVQQL Ex5HVEUgQ3liZXJUcnVzdCBTb2x1dGlvbnMsIEluYy4xIzAhBgNVBAMTGkdURSBDeWJlclRy dXN0IEdsb2JhbCBSb290ggIBpTArBgNVHRAEJDAigA8yMDAyMDgyNzE5MDIyNFqBDzIwMDkw ODI3MjM1OTAwWjAOBgNVHQ8BAf8EBAMCAeYwDwYDVR0TBAgwBgEB/wIBADANBgkqhkiG9w0B AQUFAAOBgQA6HiRv2ts2bP46czndzWocaaEAH2/UrtUf1IgppSHCV/ZVezDn0mTWDsSqcDx2 dOIqrsdGZzKSyUvxp9fgVrz2chCdf7B11p1XtXGFqsQ6p0u47A/m0vj/tc3URSWs6ganirXN MiLkIZIb7840rjBEGu6bf/CUEYaKhxkBKDrPszCCBYEwggRpoAMCAQICEQCua0kAMgJFjQuB CBGh+JL1MA0GCSqGSIb3DQEBBQUAMIHcMQswCQYDVQQGEwJHQjEXMBUGA1UEChMOQ29tb2Rv IExpbWl0ZWQxHTAbBgNVBAsTFENvbW9kbyBUcnVzdCBOZXR3b3JrMUYwRAYDVQQLEz1UZXJt cyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0 b3J5MR8wHQYDVQQLExYoYykyMDAyIENvbW9kbyBMaW1pdGVkMSwwKgYDVQQDEyNDb21vZG8g Q2xhc3MgMyBTZWN1cml0eSBTZXJ2aWNlcyBDQTAeFw0wNTA5MzAwMDAwMDBaFw0wNjA5MzAy MzU5NTlaMIHkMTUwMwYDVQQLEyxDb21vZG8gVHJ1c3QgTmV0d29yayAtIFBFUlNPTkEgTk9U IFZBTElEQVRFRDFGMEQGA1UECxM9VGVybXMgYW5kIENvbmRpdGlvbnMgb2YgdXNlOiBodHRw Oi8vd3d3LmNvbW9kby5uZXQvcmVwb3NpdG9yeTEfMB0GA1UECxMWKGMpMjAwMyBDb21vZG8g TGltaXRlZDEaMBgGA1UEAxMRQmVuamFtaW4gTWluc2hhbGwxJjAkBgkqhkiG9w0BCQEWF21p bnNoYWxsQGludGVsbGljb24uYml6MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDHA1I6 khCfCpdlS5hfYDphEpXgukOFQM338vDzSPGxmmGlY5Tlx8Xlx7C1QZViB+uMMboIasYeVA3m 2nPd+H2Tupr5Q6wSwc0sCFUga4btCo9qwSG68RQsrFutj0bGDyrtxGlAfebJdBsZ26WWnVvC wKk6dKGu85k0GW3hKlIj/QIDAQABo4IBtjCCAbIwHwYDVR0jBBgwFoAUNuDofG2dRZHumeVC dk1ws1AwrF4wHQYDVR0OBBYEFNUPqdLI+uTTagtWR5eVL4OH1I2oMA4GA1UdDwEB/wQEAwIF oDAMBgNVHRMBAf8EAjAAMCAGA1UdJQQZMBcGCCsGAQUFBwMEBgsrBgEEAbIxAQMFAjARBglg hkgBhvhCAQEEBAMCBSAwRgYDVR0gBD8wPTA7BgwrBgEEAbIxAQIBAQEwKzApBggrBgEFBQcC ARYdaHR0cHM6Ly9zZWN1cmUuY29tb2RvLm5ldC9DUFMwgbAGA1UdHwSBqDCBpTA4oDagNIYy aHR0cDovL2NybC5jb21vZG8ubmV0L0NsYXNzM1NlY3VyaXR5U2VydmljZXNfMy5jcmwwOqA4 oDaGNGh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0NsYXNzM1NlY3VyaXR5U2VydmljZXNfMy5j cmwwLaAroCmBJ0NsYXNzM1NlY3VyaXR5U2VydmljZXNfM0BjcmwuY29tb2RvLm5ldDAiBgNV HREEGzAZgRdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejANBgkqhkiG9w0BAQUFAAOCAQEAAe9E 1oELy0+Eo7QnVIDJ9fUGh61OPGhiVTawrn9xtoZVt1fJ6g+6ottQo58nNJz19BUBxj2VWlo6 xsOUeL0vannefZ8iPs9wl7M1r31jxv4iw36/a+F/1f9fsEIskXWzn61TqbMpL+7pPlVbboyo lKm11ENKf6P+k9WP6HS/5TaNRI3hDdCxZQWkFKLyq6i3irPdHNN/PSeCITfHcbo7PEtJP214 EvcfQco8F5Wj8ge73SFY93oCItoqDpNIP5lZFenKLPPa4jBdlmXlIurVtDuT8M3xmf2+oBbW rXC71Jr+ueZzuX8JEDVFlhT3f+m5y7JgqkFmdr6R2GUtQ4AH/zCCBYEwggRpoAMCAQICEQCu a0kAMgJFjQuBCBGh+JL1MA0GCSqGSIb3DQEBBQUAMIHcMQswCQYDVQQGEwJHQjEXMBUGA1UE ChMOQ29tb2RvIExpbWl0ZWQxHTAbBgNVBAsTFENvbW9kbyBUcnVzdCBOZXR3b3JrMUYwRAYD VQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5l dC9yZXBvc2l0b3J5MR8wHQYDVQQLExYoYykyMDAyIENvbW9kbyBMaW1pdGVkMSwwKgYDVQQD EyNDb21vZG8gQ2xhc3MgMyBTZWN1cml0eSBTZXJ2aWNlcyBDQTAeFw0wNTA5MzAwMDAwMDBa Fw0wNjA5MzAyMzU5NTlaMIHkMTUwMwYDVQQLEyxDb21vZG8gVHJ1c3QgTmV0d29yayAtIFBF UlNPTkEgTk9UIFZBTElEQVRFRDFGMEQGA1UECxM9VGVybXMgYW5kIENvbmRpdGlvbnMgb2Yg dXNlOiBodHRwOi8vd3d3LmNvbW9kby5uZXQvcmVwb3NpdG9yeTEfMB0GA1UECxMWKGMpMjAw MyBDb21vZG8gTGltaXRlZDEaMBgGA1UEAxMRQmVuamFtaW4gTWluc2hhbGwxJjAkBgkqhkiG 9w0BCQEWF21pbnNoYWxsQGludGVsbGljb24uYml6MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCB iQKBgQDHA1I6khCfCpdlS5hfYDphEpXgukOFQM338vDzSPGxmmGlY5Tlx8Xlx7C1QZViB+uM MboIasYeVA3m2nPd+H2Tupr5Q6wSwc0sCFUga4btCo9qwSG68RQsrFutj0bGDyrtxGlAfebJ dBsZ26WWnVvCwKk6dKGu85k0GW3hKlIj/QIDAQABo4IBtjCCAbIwHwYDVR0jBBgwFoAUNuDo fG2dRZHumeVCdk1ws1AwrF4wHQYDVR0OBBYEFNUPqdLI+uTTagtWR5eVL4OH1I2oMA4GA1Ud DwEB/wQEAwIFoDAMBgNVHRMBAf8EAjAAMCAGA1UdJQQZMBcGCCsGAQUFBwMEBgsrBgEEAbIx AQMFAjARBglghkgBhvhCAQEEBAMCBSAwRgYDVR0gBD8wPTA7BgwrBgEEAbIxAQIBAQEwKzAp BggrBgEFBQcCARYdaHR0cHM6Ly9zZWN1cmUuY29tb2RvLm5ldC9DUFMwgbAGA1UdHwSBqDCB pTA4oDagNIYyaHR0cDovL2NybC5jb21vZG8ubmV0L0NsYXNzM1NlY3VyaXR5U2VydmljZXNf My5jcmwwOqA4oDaGNGh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0NsYXNzM1NlY3VyaXR5U2Vy dmljZXNfMy5jcmwwLaAroCmBJ0NsYXNzM1NlY3VyaXR5U2VydmljZXNfM0BjcmwuY29tb2Rv Lm5ldDAiBgNVHREEGzAZgRdtaW5zaGFsbEBpbnRlbGxpY29uLmJpejANBgkqhkiG9w0BAQUF AAOCAQEAAe9E1oELy0+Eo7QnVIDJ9fUGh61OPGhiVTawrn9xtoZVt1fJ6g+6ottQo58nNJz1 9BUBxj2VWlo6xsOUeL0vannefZ8iPs9wl7M1r31jxv4iw36/a+F/1f9fsEIskXWzn61TqbMp L+7pPlVbboyolKm11ENKf6P+k9WP6HS/5TaNRI3hDdCxZQWkFKLyq6i3irPdHNN/PSeCITfH cbo7PEtJP214EvcfQco8F5Wj8ge73SFY93oCItoqDpNIP5lZFenKLPPa4jBdlmXlIurVtDuT 8M3xmf2+oBbWrXC71Jr+ueZzuX8JEDVFlhT3f+m5y7JgqkFmdr6R2GUtQ4AH/zGCBF4wggRa AgEBMIHyMIHcMQswCQYDVQQGEwJHQjEXMBUGA1UEChMOQ29tb2RvIExpbWl0ZWQxHTAbBgNV BAsTFENvbW9kbyBUcnVzdCBOZXR3b3JrMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9u cyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYo YykyMDAyIENvbW9kbyBMaW1pdGVkMSwwKgYDVQQDEyNDb21vZG8gQ2xhc3MgMyBTZWN1cml0 eSBTZXJ2aWNlcyBDQQIRAK5rSQAyAkWNC4EIEaH4kvUwCQYFKw4DAhoFAKCCAsEwGAYJKoZI hvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwOTE1MTkxMjA3WjAjBgkq hkiG9w0BCQQxFgQUhJS3W/AGVOJkfwKOEDA1/71WGo8wUgYJKoZIhvcNAQkPMUUwQzAKBggq hkiG9w0DBzAOBggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZI hvcNAwICASgwggEDBgkrBgEEAYI3EAQxgfUwgfIwgdwxCzAJBgNVBAYTAkdCMRcwFQYDVQQK Ew5Db21vZG8gTGltaXRlZDEdMBsGA1UECxMUQ29tb2RvIFRydXN0IE5ldHdvcmsxRjBEBgNV BAsTPVRlcm1zIGFuZCBDb25kaXRpb25zIG9mIHVzZTogaHR0cDovL3d3dy5jb21vZG8ubmV0 L3JlcG9zaXRvcnkxHzAdBgNVBAsTFihjKTIwMDIgQ29tb2RvIExpbWl0ZWQxLDAqBgNVBAMT I0NvbW9kbyBDbGFzcyAzIFNlY3VyaXR5IFNlcnZpY2VzIENBAhEArmtJADICRY0LgQgRofiS 9TCCAQUGCyqGSIb3DQEJEAILMYH1oIHyMIHcMQswCQYDVQQGEwJHQjEXMBUGA1UEChMOQ29t b2RvIExpbWl0ZWQxHTAbBgNVBAsTFENvbW9kbyBUcnVzdCBOZXR3b3JrMUYwRAYDVQQLEz1U ZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBv c2l0b3J5MR8wHQYDVQQLExYoYykyMDAyIENvbW9kbyBMaW1pdGVkMSwwKgYDVQQDEyNDb21v ZG8gQ2xhc3MgMyBTZWN1cml0eSBTZXJ2aWNlcyBDQQIRAK5rSQAyAkWNC4EIEaH4kvUwDQYJ KoZIhvcNAQEBBQAEgYAQyCJrxqPI4Ay7IaUM/rg+CtV5ehsoz7YY5xGI+oZ1SODuYzX4ZxZ+ B0ZtEKn0aCDD3wRAvSlK6zKT33CQyTnmbhA4jgiHto9FTC72yjcUItctoprFbQJcGfqdJS2Y +Po1ctA32Hb+R/phvza1r43+CCFoo4huae2lSmtE2i2duAAAAAAAAA== --------------ms060906090300080405070004--