Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0030264FCF8; Fri, 22 Aug 2008 08:31:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 41701-02; Fri, 22 Aug 2008 08:31:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from service-web.de (p15093784.pureserver.info [217.160.106.224]) by postgresql.org (Postfix) with ESMTP id 3C2FD64FCCF; Fri, 22 Aug 2008 08:31:09 -0300 (ADT) Received: from [145.228.94.72] (unknown [145.228.94.72]) by service-web.de (Postfix) with ESMTP id B663AF0839; Fri, 22 Aug 2008 13:31:05 +0200 (CEST) Message-ID: <48AEA379.7080807@wildenhain.de> Date: Fri, 22 Aug 2008 13:31:05 +0200 From: Tino Wildenhain User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: c k Cc: pgsql-general@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [GENERAL] LIKE and SIMILAR TO References: In-Reply-To: Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------ms090100010606060208030405" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/117 X-Sequence-Number: 29962 This is a cryptographically signed message in MIME format. --------------ms090100010606060208030405 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, c k wrote: > Hello all, > As we are migrating our ERP application from MySQL to PostgreSQL we have > some difficulties. One of them is use of Like and Similar to operators. > We often use LIKE to search a string from front-end without case > sensetivity. As postgreSQL's LIKE is case sensitive, we tried ILIKE and > SIMILAR TO, but both are slower than LIKE and we must need case > insensitivity. How can we get this by increases speed. All search > columns are VARCHAR(100)to VARCHAR(250). Currently without index. You could build an index on lower(column) and use lower(column) like ... this would speed up queries with exact match as well as 'foo%' e.g. "start with..." match. Make sure when you create the database cluster (initdb) you used the currect locale, otherwise lower() (and ilike) probably do not work as you might expect. For any more complex searches I'd recommend full text index, for example tsearch2. Regards Tino --------------ms090100010606060208030405 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 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIJBzCC At4wggJHoAMCAQICEFhzOX0yh1CxCcTpCoXqqXkwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UE BhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMT I1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA4MDQzMDE5MzY1NFoX DTA5MDQzMDE5MzY1NFowRDEfMB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEhMB8G CSqGSIb3DQEJARYSdGlub0B3aWxkZW5oYWluLmRlMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A MIIBCgKCAQEAwdJKlg6wlxFdjMOMON992A1RRg7jfZJWPuHXAb0VOAM4htilnwfJVdk9ARnY Jbp6uDbR7Npm3ZqrqfRlI2uPXVVj00L8JHMQmeS36ICknk7aG93yTBYWBf1T2EadP2KFszSM EmfjxV7vc55BgP1Pj9eB0ceJOT8dOWl5c342giavCWOg0xDQAKUd+BpEe3r/TnLuCUo0MIQF DAO8nw8NyKJG82u0Y8aHW5wpLS0S0E+GeVd5RP4cB1GDCiH8Bi7MvOSsFBv7tSYIn/3fJH+Q 979UGd1nhO4H+sK43mjt+gXpR7+5A3ZGy+O/5gsArCTMwGZmLZ4hd3VhPCT7TRtLmQIDAQAB oy8wLTAdBgNVHREEFjAUgRJ0aW5vQHdpbGRlbmhhaW4uZGUwDAYDVR0TAQH/BAIwADANBgkq hkiG9w0BAQUFAAOBgQA3ROUGts7nkRx1uE+rbeuhfckv9bLIL6lz19tRwTeTOYanXrqDzwhn UUMyd7oDFUIZe/lxtLshkUD4p/omXtIzCCx6M5yIWlYMzKTeB4qQpQ14huovXzXQZbWiTOwA 3+ZU+rVC7CX47dEEKuKHwAvpurNUGikHlGuYkMSMCly61zCCAt4wggJHoAMCAQICEFhzOX0y h1CxCcTpCoXqqXkwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRo YXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBG cmVlbWFpbCBJc3N1aW5nIENBMB4XDTA4MDQzMDE5MzY1NFoXDTA5MDQzMDE5MzY1NFowRDEf MB0GA1UEAxMWVGhhd3RlIEZyZWVtYWlsIE1lbWJlcjEhMB8GCSqGSIb3DQEJARYSdGlub0B3 aWxkZW5oYWluLmRlMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAwdJKlg6wlxFd jMOMON992A1RRg7jfZJWPuHXAb0VOAM4htilnwfJVdk9ARnYJbp6uDbR7Npm3ZqrqfRlI2uP XVVj00L8JHMQmeS36ICknk7aG93yTBYWBf1T2EadP2KFszSMEmfjxV7vc55BgP1Pj9eB0ceJ OT8dOWl5c342giavCWOg0xDQAKUd+BpEe3r/TnLuCUo0MIQFDAO8nw8NyKJG82u0Y8aHW5wp LS0S0E+GeVd5RP4cB1GDCiH8Bi7MvOSsFBv7tSYIn/3fJH+Q979UGd1nhO4H+sK43mjt+gXp R7+5A3ZGy+O/5gsArCTMwGZmLZ4hd3VhPCT7TRtLmQIDAQABoy8wLTAdBgNVHREEFjAUgRJ0 aW5vQHdpbGRlbmhhaW4uZGUwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQUFAAOBgQA3ROUG ts7nkRx1uE+rbeuhfckv9bLIL6lz19tRwTeTOYanXrqDzwhnUUMyd7oDFUIZe/lxtLshkUD4 p/omXtIzCCx6M5yIWlYMzKTeB4qQpQ14huovXzXQZbWiTOwA3+ZU+rVC7CX47dEEKuKHwAvp urNUGikHlGuYkMSMCly61zCCAz8wggKooAMCAQICAQ0wDQYJKoZIhvcNAQEFBQAwgdExCzAJ BgNVBAYTAlpBMRUwEwYDVQQIEwxXZXN0ZXJuIENhcGUxEjAQBgNVBAcTCUNhcGUgVG93bjEa MBgGA1UEChMRVGhhd3RlIENvbnN1bHRpbmcxKDAmBgNVBAsTH0NlcnRpZmljYXRpb24gU2Vy dmljZXMgRGl2aXNpb24xJDAiBgNVBAMTG1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBDQTEr MCkGCSqGSIb3DQEJARYccGVyc29uYWwtZnJlZW1haWxAdGhhd3RlLmNvbTAeFw0wMzA3MTcw MDAwMDBaFw0xMzA3MTYyMzU5NTlaMGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUg Q29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgSXNzdWluZyBDQTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAxKY8VXNV+065ypla HmjAdQRwnd/p/6Me7L3N9VvyGna9fww6YfK/Uc4B1OVQCjDXAmNaLIkVcI7dyfArhVqqP3FW y688Cwfn8R+RNiQqE88r1fOCdz0Dviv+uxg+B79AgAJk16emu59l0cUqVIUPSAR/p7bRPGEE QB5kGXJgt/sCAwEAAaOBlDCBkTASBgNVHRMBAf8ECDAGAQH/AgEAMEMGA1UdHwQ8MDowOKA2 oDSGMmh0dHA6Ly9jcmwudGhhd3RlLmNvbS9UaGF3dGVQZXJzb25hbEZyZWVtYWlsQ0EuY3Js MAsGA1UdDwQEAwIBBjApBgNVHREEIjAgpB4wHDEaMBgGA1UEAxMRUHJpdmF0ZUxhYmVsMi0x MzgwDQYJKoZIhvcNAQEFBQADgYEASIzRUIPqCy7MDaNmrGcPf6+svsIXoUOWlJ1/TCG4+DYf qi2fNi/A9BxQIJNwPP2t4WFiw9k6GX6EsZkbAMUaC4J0niVQlGLH2ydxVyWN3amcOY6MIE9l X5Xa9/eH1sYITq726jTlEBpbNU1341YheILcIRk13iSx0x1G/11fZU8xggNkMIIDYAIBATB2 MGIxCzAJBgNVBAYTAlpBMSUwIwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQu MSwwKgYDVQQDEyNUaGF3dGUgUGVyc29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIQWHM5fTKH ULEJxOkKheqpeTAJBgUrDgMCGgUAoIIBwzAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwG CSqGSIb3DQEJBTEPFw0wODA4MjIxMTMxMDVaMCMGCSqGSIb3DQEJBDEWBBRiFfmJgLVZg90E ulj3lwvKvKhWVjBSBgkqhkiG9w0BCQ8xRTBDMAoGCCqGSIb3DQMHMA4GCCqGSIb3DQMCAgIA gDANBggqhkiG9w0DAgIBQDAHBgUrDgMCBzANBggqhkiG9w0DAgIBKDCBhQYJKwYBBAGCNxAE MXgwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkg THRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEFhz OX0yh1CxCcTpCoXqqXkwgYcGCyqGSIb3DQEJEAILMXigdjBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBl cnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECEFhzOX0yh1CxCcTpCoXqqXkwDQYJKoZIhvcN AQEBBQAEggEAKc6MTVJSVNXlTjB30wYZ69KBznuAvzyaO0YISIDDZv3dxYHdRfAttl/Qe7yt OsDM7DVdaoirLrJ49y4XsgiysL116cAMbKzs1l619btyFkN8gexDmeH8gaEQVBsAQwXmyR92 TzC6hTRkZ9g8ipxs7ecPxg2GZfDKSNBj159Un2nSoW1RdEBkQo5cD96AFGf+haBpljskd5KU qOpjJNNmeX+S+wFqCQ8vzvP95RL/O2/00OK9TOaQVY8hsx6msVMxSFwE8IF0rN5Fxo2z/f64 dvU04+w4JPH+n5V8RnROpE5T19M84wx4tBe1T1QpzoB1qsrTLL8Si3C/ZakUYZbbVAAAAAAA AA== --------------ms090100010606060208030405--