Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E4B132E3671 for ; Mon, 10 Dec 2007 09:28:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 68626-06-3 for ; Mon, 10 Dec 2007 09:28:03 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 7BE652E36D2 for ; Mon, 10 Dec 2007 09:08:00 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so1441506ugf for ; Mon, 10 Dec 2007 05:07:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=NoEZA1jFieu34H4fyDCqDBEMsfcgQX3RpoaVzQJRblM=; b=MvqTjQmAQ7TGbLE9Dbh7kE8TeuLSzr2CeasQEtR0QZlv1BerGe3Zahc4jiViy4tldskP2n3s1XcGk/YuYG/4VulR7h5LaxSy4tlNXBdjrsNp2jUyPfr+4y2PSyrIDGYdyympwsu0+w6/TpMW2kScTgyilNA95PWy2i7ez6RjiKI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=knaD2V+AXYJHozSiP785m1GoCuOKvUL4yvvL3/DST26geWmRdyTjEalvxR+14DSfU4WV6sV+OrToBi7HkKG4PbsbIKyApcwvzHAA4aAheROD2tUMYji2WTrx9Dua2I33xJVK0zrKqmhxe1qEFLmwe7Q53t4FaDP1uArlDCj3ujo= Received: by 10.78.129.16 with SMTP id b16mr7003792hud.1197292078571; Mon, 10 Dec 2007 05:07:58 -0800 (PST) Received: by 10.78.35.7 with HTTP; Mon, 10 Dec 2007 05:07:58 -0800 (PST) Message-ID: <162867790712100507w5d42fe96j705baa57171c2b85@mail.gmail.com> Date: Mon, 10 Dec 2007 14:07:58 +0100 From: "Pavel Stehule" To: "=?UTF-8?Q?Piotr_Gasid=C5=82o?=" Subject: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure. Cc: pgsql-performance@postgresql.org In-Reply-To: <475D37D9.3000208@barbara.eu.org> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: base64 Content-Disposition: inline References: <475D37D9.3000208@barbara.eu.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/116 X-Sequence-Number: 27932 SGVsbG8KCnRoaXMgaXMga25vd24gcHJvYmxlbSBvZiBwcmVwYXJlZCBzdGF0ZW1lbnRzLiBQcmVw YXJlZCBzdGF0ZW1lbnQgaGFzCnBsYW4gYnVpbHQgd2l0aG91dCBrbm93bGVkZ2UgYW55IHZhbHVl cyBhbmQgc2hvdWxkIG5vdCBiZSBvcHRpbWFsLgoKdHJ5IHVzZSBkeW5hbWljIHF1ZXJ5IGFuZCBz dGF0ZW1lbnQgRVhFQ1VURSBJTlRPCgpSZWdhcmRzClBhdmVsIFN0ZWh1bGUKCgoKT24gMTAvMTIv MjAwNywgUGlvdHIgR2FzaWTFgm8gPHF1YWtlckBiYXJiYXJhLmV1Lm9yZz4gd3JvdGU6Cj4gSGVs bG8sCj4KPiBJJ3ZlIGNyZWF0ZWQgdGFibGU6Cj4KPiBxdWFrZXI9PiBcZCB1c2Vycwo+ICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgIFRhYmxlICJwdWJsaWMudXNlcnMiCj4gICAgQ29s dW1uICAgfCAgICAgICBUeXBlICAgICAgICB8ICAgICAgICAgICAgICAgICAgICAgTW9kaWZpZXJz Cj4KPiAtLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0tLS0tLS0tKy0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KPiAgIGlkICAgICAgICB8IGludGVnZXIg ICAgICAgICAgIHwgbm90IG51bGwgZGVmYXVsdAo+IG5leHR2YWwoJ3VzZXJzX2lkX3NlcSc6OnJl Z2NsYXNzKQo+ICAgdXNlcl9uYW1lIHwgY2hhcmFjdGVyIHZhcnlpbmcgfCBub3QgbnVsbAo+ICAg ZXh0cmEgICAgIHwgaW50ZWdlciAgICAgICAgICAgfAo+IEluZGV4ZXM6Cj4gICAgICAidXNlcnNf cGtleSIgUFJJTUFSWSBLRVksIGJ0cmVlIChpZCkKPiAgICAgICJ1c2Vyc191c2VyX25hbWVfdW5p cXVlX3RleHRfb3BzIiBVTklRVUUsIGJ0cmVlICh1c2VyX25hbWUgdGV4dF9vcHMpCj4gICAgICAi dXNlcnNfdXNlcl9uYW1lX3VuaXF1ZV90ZXh0X3BhdHRlcm5fb3BzIiBidHJlZSAodXNlcl9uYW1l Cj4gdGV4dF9wYXR0ZXJuX29wcykKPgo+IEZpbGxlZCB3aXRoIHJhbmRvbSBkYXRhICgxMDBrIHJl Y29yZHMpLgo+Cj4gSSBkbyBzaW1wbGUgcXVlcmllcyB1c2luZyBhYm92ZSBpbmRleGVzIChhc2tp bmcgZm9yIGV4aXN0aW5nIHJlY29yZCkuCj4KPiBleHBsYWluIGFuYWx5emUgc2VsZWN0IGlkIGZy b20gdXNlcnMgd2hlcmUgdXNlcl9uYW1lID0gJ3F1YWtlcic7Cj4gICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgUVVFUlkKPiBQTEFO Cj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCj4gICBJbmRleCBTY2FuIHVzaW5nIHVzZXJzX3VzZXJf bmFtZV91bmlxdWVfdGV4dF9vcHMgb24gdXNlcnMKPiAoY29zdD0wLjAwLi44LjI4IHJvd3M9MSB3 aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wNDAuLjAuMDQzIHJvd3M9MSBsb29wcz0xKQo+ICAgICBJ bmRleCBDb25kOiAoKHVzZXJfbmFtZSk6OnRleHQgPSAncXVha2VyJzo6dGV4dCkKPiAgIFRvdGFs IHJ1bnRpbWU6IDAuMDg0IG1zCj4gKDMgcm93cykKPgo+IGV4cGxhaW4gYW5hbHl6ZSBzZWxlY3Qg aWQgZnJvbSB1c2VycyB3aGVyZSB1c2VyX25hbWUgbGlrZSAncXVha2VyJzsKPiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg UVVFUlkKPiBQTEFOCj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KPiAgIEluZGV4IFNj YW4gdXNpbmcgdXNlcnNfdXNlcl9uYW1lX3VuaXF1ZV90ZXh0X3BhdHRlcm5fb3BzIG9uIHVzZXJz Cj4gKGNvc3Q9MC4wMC4uOC4yOCByb3dzPTEgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDIyLi4w LjAyNCByb3dzPTEgbG9vcHM9MSkKPiAgICAgSW5kZXggQ29uZDogKCh1c2VyX25hbWUpOjp0ZXh0 IH49fiAncXVha2VyJzo6dGV4dCkKPiAgICAgRmlsdGVyOiAoKHVzZXJfbmFtZSk6OnRleHQgfn4g J3F1YWtlcic6OnRleHQpCj4gICBUb3RhbCBydW50aW1lOiAwLjA1MCBtcwo+ICg0IHJvd3MpCj4K PiBFdmVyeXRoaW5nIGxvb2tzIGZpbmUuCj4KPiBOb3csIEkndmUgY3JlYXRlZCBQTC9QR1NRTCBm dW5jdGlvbjoKPgo+IGNyZWF0ZSBvciByZXBsYWNlIGZ1bmN0aW9uIHVzZXJfbG9naW4oCj4gICAg X3ZfdXNlcl9uYW1lIHZhcmNoYXIKPiApIHJldHVybnMgaW50ZWdlciBhcyAkJAo+IGRlY2xhcmUK PiAgICBfaV91c2VyX2lkIGludGVnZXI7Cj4gYmVnaW4KPiAgICBzZWxlY3QgaWQgaW50byBfaV91 c2VyX2lkIGZyb20gdXNlcnMgd2hlcmUgdXNlcl9uYW1lID0gX3ZfdXNlcl9uYW1lCj4gbGltaXQg MTsKPiAgICBpZiBmb3VuZCB0aGVuCj4gICAgICByZXR1cm4gX2lfdXNlcl9pZDsKPiAgICBlbmQg aWY7Cj4gICAgcmV0dXJuIC0xOwo+IGVuZDsKPiAkJCBsYW5ndWFnZSBwbHBnc3FsIHNlY3VyaXR5 IGRlZmluZXI7Cj4KPiBBcyBzaG93biBhYm92ZSwgSSB1c2UgIj0iIG9wZXJhdG9yLCB3aGljaCBz aG91bGQgdXNlCj4gdXNlcnNfdXNlcl9uYW1lX3VuaXF1ZV90ZXh0X29wcyBpbmRleDoKPgo+IGV4 cGxhaW4gYW5hbHl6ZSBzZWxlY3QgdXNlcl9sb2dpbigncXVha2VyJyk7Cj4gICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOCj4KPiAtLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0KPiAgIFJlc3VsdCAgKGNvc3Q9MC4wMC4uMC4wMSByb3dzPTEgd2lkdGg9MCkg KGFjdHVhbCB0aW1lPTAuMzIwLi4wLjMyMgo+IHJvd3M9MSBsb29wcz0xKQo+ICAgVG90YWwgcnVu dGltZTogMC4zNDAgbXMKPiAoMiByb3dzKQo+Cj4KPiBTb21lIHBlcmZvcm1hbmNlIGxvc3MsIGJ1 dCBPSy4gTm93IEkndmUgY2hhbmdlZCAiPSIgaW50byAiTElLRSIgdG8gdXNlCj4gdXNlcnNfdXNl cl9uYW1lX3VuaXF1ZV90ZXh0X3BhdHRlcm5fb3BzIGluZGV4IGFuZCByZXJ1biBxdWVyeToKPgo+ IGV4cGxhaW4gYW5hbHl6ZSBzZWxlY3QgdXNlcl9sb2dpbigncXVha2VyJyk7Cj4KPiAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOCj4KPiAtLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLQo+ICAgUmVzdWx0ICAoY29zdD0wLjAwLi4wLjAxIHJvd3M9MSB3 aWR0aD0wKSAoYWN0dWFsIHRpbWU9NDEuNjA2Li40MS42MDgKPiByb3dzPTEgbG9vcHM9MSkKPiAg IFRvdGFsIHJ1bnRpbWU6IDQxLjYyOSBtcwo+ICgyIHJvd3MpCj4KPiBTZWNvbmQgcnVuIGdpdmUg NjEuMDYxIG1zLiBTbyBubyBpbXByb3ZlbWVudHMuCj4KPiBXaHkgUEwvUEdTUUwgaXMgdW5hYmxl IHRvIHByb3BlciB1dGlsaXplCj4gdXNlcnNfdXNlcl9uYW1lX3VuaXF1ZV90ZXh0X3BhdHRlcm5f b3BzPwo+Cj4gcXVha2VyPT4gc2VsZWN0IHZlcnNpb24oKTsKPiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIHZlcnNpb24KPgo+IC0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KPiAg IFBvc3RncmVTUUwgOC4yLjUgb24gaTQ4Ni1wYy1saW51eC1nbnUsIGNvbXBpbGVkIGJ5IEdDQyBj YyAoR0NDKSA0LjEuMwo+IDIwMDcwODMxIChwcmVyZWxlYXNlKSAoVWJ1bnR1IDQuMS4yLTE2dWJ1 bnR1MSkKPiAoMSByb3cpCj4KPiAtLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0oZW5kIG9mIGJy b2FkY2FzdCktLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KPiBUSVAgMTogaWYgcG9zdGluZy9y ZWFkaW5nIHRocm91Z2ggVXNlbmV0LCBwbGVhc2Ugc2VuZCBhbiBhcHByb3ByaWF0ZQo+ICAgICAg ICBzdWJzY3JpYmUtbm9tYWlsIGNvbW1hbmQgdG8gbWFqb3Jkb21vQHBvc3RncmVzcWwub3JnIHNv IHRoYXQgeW91cgo+ICAgICAgICBtZXNzYWdlIGNhbiBnZXQgdGhyb3VnaCB0byB0aGUgbWFpbGlu ZyBsaXN0IGNsZWFubHkKPgo=