X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5E7689DCBB9 for ; Wed, 22 Feb 2006 11:48:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33886-08 for ; Wed, 22 Feb 2006 11:48:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 72F0D9DC838 for ; Wed, 22 Feb 2006 11:48:40 -0400 (AST) Received: from [10.0.1.3] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Wed, 22 Feb 2006 08:48:44 -0700 In-Reply-To: References: <1140538679.5777.3.camel@state.g2switchworks.com> <20060221163416.GC6541@surnet.cl> Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-2--892633001; protocol="application/pkcs7-signature" Message-Id: <78873B6F-1572-467C-8F98-9715FC8AC4AC@clickspace.com> Cc: "Scott Marlowe" , pgsql-performance@postgresql.org From: Brendan Duddridge Subject: Re: LIKE query on indexes Date: Wed, 22 Feb 2006 08:48:43 -0700 To: Ibrahim Tekin X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.105 required=5 tests=[AWL=0.104, HTML_MESSAGE=0.001] X-Spam-Score: 0.105 X-Spam-Level: X-Archive-Number: 200602/342 X-Sequence-Number: 17328 --Apple-Mail-2--892633001 Content-Type: multipart/alternative; boundary=Apple-Mail-1--892633558 --Apple-Mail-1--892633558 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi, Can this technique work with case insensitive ILIKE? It didn't seem to use the index when I used ILIKE instead of LIKE. Thanks, ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Feb 21, 2006, at 1:28 PM, Ibrahim Tekin wrote: > this trick did the job. > thanks. > > On 2/21/06, Alvaro Herrera wrote: > Scott Marlowe wrote: > > On Tue, 2006-02-21 at 09:57, Ibrahim Tekin wrote: > > > hi, > > > i have btree index on a text type field. i want see rows which > starts > > > with certain characters on that field. so i write a query like > this: > > > > > > SELECT * FROM mytable WHERE myfield LIKE 'john%' > > > > > > since this condition is from start of the field, query planner > should > > > use index to find such elements but explain command shows me it > will > > > do a sequential scan. > > > > > > is this lack of a feature or i am wrong somewhere? > > > > This is an artifact of how PostgreSQL handles locales other than > ASCII. > > > > If you want such a query to use an index, you need to back up your > > database, and re-initdb with --locale=C as an argument. > > ... or you can choose to create an index with the text_pattern_ops > operator class, which would be used in a LIKE constraint regardless of > locale. > > http://www.postgresql.org/docs/8.1/static/indexes-opclass.html > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > --Apple-Mail-1--892633558 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1 Hi,

Can this technique work = with case insensitive ILIKE?

It didn't seem to use the = index when I used ILIKE instead of LIKE.

Thanks,

_________________________________________________________= ___________
Brendan Duddridge=A0| CTO | 403-277-5591 x24 | =A0brendan@clickspace.com =

ClickSpace = Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB = =A0T2G 0V9

http://www.clickspace.com=A0
=

On Feb 21, 2006, at 1:28 PM, Ibrahim Tekin = wrote:

this trick did the job.
thanks.

On 2/21/06, Alvaro = Herrera <alvherre@commandprompt.com&= gt; wrote:
Scott Marlowe wrote:
> On Tue, 2006-02-21 at 09:57, Ibrahim = Tekin wrote:
> > hi,
> > i have btree index on a text = type field. i want see rows which starts
> > with certain = characters on that field. so i write a query like this:
> = >
> > SELECT * FROM mytable WHERE myfield LIKE 'john%' =
> >
> > since this condition is from start of the = field, query planner should
> > use index to find such elements = but explain command shows me it will
> > do a sequential = scan.
> >
> > is this lack of a feature or i am wrong = somewhere?
>
> This is an artifact of how PostgreSQL handles = locales other than ASCII.
>
> If you want such a query to = use an index, you need to back up your
> database, and re-initdb = with --locale=3DC as an argument.

... or you can choose to create = an index with the text_pattern_ops
operator class, which would be = used in a LIKE constraint regardless of
locale.

ht= tp://www.postgresql.org/docs/8.1/static/indexes-opclass.html

--=
Alvaro Herrera=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = http://www.CommandPrompt.com/
The PostgreSQL Company - Command = Prompt, = Inc.


<= /HTML>= --Apple-Mail-1--892633558-- --Apple-Mail-2--892633001 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGIDCCAtkw ggJCoAMCAQICAw84ujANBgkqhkiG9w0BAQQFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwHhcNMDUwNzI5MjAxMTQzWhcNMDYwNzI5MjAxMTQzWjBIMR8wHQYDVQQD ExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSUwIwYJKoZIhvcNAQkBFhZicmVuZGFuQGNsaWNrc3Bh Y2UuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqWfMCdFgKzXroGNqJuU3eyDC K/Gxts3mtmIdgcHGmUjRKdlBSfkmcCBUDPJIt4QQc91sY3h8Itg0EAsF+1yFECU6afn/1SEAHSDK 6Q86PclF58/Dux2FYNGpeIAw/lisZ2UCyIYoUiWtEDKCBq5jDuYzcGkFS5Csz+/rEL+BHTXQVqnS nUdUaXu9xdZn4wgjB/n65UEYRqr0LGN53CGsFcA9uC+ViS7WyBtzJcP02LMNT+cAZ7TKHw4Q/ZeG 5ptPgQHLTD2wza3GMbPQ4fYK6aNPA+lVzslLSCjyQpg55gVyQQlV0k/5zU5Q5m48ZtqwOqN+fPYd eIB7H5y1SFLd/wIDAQABozMwMTAhBgNVHREEGjAYgRZicmVuZGFuQGNsaWNrc3BhY2UuY29tMAwG A1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAT+6hGvStHoRO0OCHlJev31BpUlvPSpbYVKJN i5kCMF164sSX7j0IRvcyU6DfUuW7samTluXJbANyuX1ZIpXulGen5EEc2NV7eU/rMz6ExklMShTh ++azcvmMjOzTuqXi3ZsIjwzBBhqCB0U++kYlMlHEYla2qKLFbXY6PiH0a8swggM/MIICqKADAgEC AgENMA0GCSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBl MRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQL Ex9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5j b20wHhcNMDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UE ChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftO ucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9x VsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAe ZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJo dHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8E BAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3 DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9 reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAa WzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIC5zCCAuMCAQEwaTBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNv bmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujAJBgUrDgMCGgUAoIIBUzAYBgkqhkiG9w0BCQMx CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjAyMjIxNTQ4NDRaMCMGCSqGSIb3DQEJBDEW BBRdx9twrcfRI9od+0IxotmDqJvUxTB4BgkrBgEEAYI3EAQxazBpMGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIDDzi6MHoGCyqGSIb3DQEJEAILMWugaTBiMQswCQYD VQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMj VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujANBgkqhkiG9w0BAQEFAASC AQAcEMltD19TKplRE4YhSvYP5RynXaaoqlFKxbqNRZD19cHDsnZjbSl+mqSjbxuUKtX8nyuY1hpI yb4mIoYUIOzkyj1/8g38GrD8WOwzMPGpUDrehI8GaWBQj2hczqv06RpEY6sQrNBc2x3Duiq2R72t YHmCYl7sc49ckvQN7Hzq9Y+kxN0soQ7e9tlbd1W9HdYQMBu/CSYFrXACbgbWbNzlP1m/Q8ce5i2K +RydFaqyRtVFx+XkCba1gDiH2cmnXD5kjMKFRihAdEDSeScCLMX/amq0i9+VGRV4AEcUQyUPKptQ Eq9EL3JX0wJyqxSu1LMFaQuYWYWxmYl3Zn2KykJQAAAAAAAA --Apple-Mail-2--892633001--