Thread: BUG #14437: BTREE Index is not used for queries on citext columns
BUG #14437: BTREE Index is not used for queries on citext columns
From
pawel.kania@moneyhouse.de
Date:
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDQzNwpMb2dnZWQgYnk6ICAg ICAgICAgIFBhd2XFgiBLYW5pYQpFbWFpbCBhZGRyZXNzOiAgICAgIHBhd2Vs LmthbmlhQG1vbmV5aG91c2UuZGUKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjYu MQpPcGVyYXRpbmcgc3lzdGVtOiAgIFVidW50dSAxNi4wNCBMVFMKRGVzY3Jp cHRpb246ICAgICAgICAKCkhpLA0KSSB3YXMgdHJ5aW5nIHRvIGVsaW1pbmF0 ZSAiV0hFUkUgbG93ZXIoY29sdW1uX25hbWUpID0gbG93ZXIoc2VhcmNoX3N0 cmluZykiCmZyb20gcXVlcmllcyBieSBjaGFuZ2luZyBjb2x1bW4gdHlwZSBm cm9tIHRleHQgdG8gY2l0ZXh0IGJ1dCBpbiBzb21lIGNhc2VzCmluZGV4ZXMg YXJlIG5vdCB1c2VkIGZvciBuZXcgcXVlcmllcy4gQWZ0ZXIgc2V0dGluZyBF TkFCTEVfU0VRU0NBTj1vZmY7CmluZGV4ZXMgYXJlIHN0aWxsIG5vdCB1c2Vk LiBJIHByZXBhcmVkIFNRTCBxdWVyaWVzIHRvIHZpc3VhbGl6ZSBwcm9ibGVt Og0KDQotLSBjcmVhdGUgYW5kIGZpbGwgdGFibGUgd2l0aCBkdW1teSBkYXRh DQpDUkVBVEUgVEFCTEUgdGVzdF9jaXRleHQgQVMgU0VMRUNUDQogICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBpZCwNCiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIG1kNShyYW5kb20oKSA6OiBURVhUKSA6OiBjaXRleHQg QVMKbmFtZV9jaXRleHQsDQogICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAocmFuZG9tKCkgKiAxMCkgOjogSU5UICAgICAgICAgIEFTIGZsYWcNCiAg ICAgICAgICAgICAgICAgICAgICAgICAgICBGUk9NIGdlbmVyYXRlX1Nlcmll cygxLCA5NTAwMDAwKSBpZDsNCg0KLS0gY3JlYXRlIGluZGV4ZXMgLSBmaXJz dCBmb3IgY2l0ZXh0IGFuZCBzZWNvbmQgdG8gdXNlIHdpdGggbG93ZXIoKQ0K Q1JFQVRFIElOREVYIGlkeF90ZXN0X2NpdGV4dF9uYW1lX2NpdGV4dA0KICBP TiB0ZXN0X2NpdGV4dA0KICBVU0lORyBCVFJFRQ0KICAobmFtZV9jaXRleHQg dGV4dF9wYXR0ZXJuX29wcyk7DQoNCkNSRUFURSBJTkRFWCBpZHhfdGVzdF9j aXRleHRfbmFtZV9jaXRleHRfbG93ZXINCiAgT04gdGVzdF9jaXRleHQNCiAg VVNJTkcgQlRSRUUNCiAgKGxvd2VyKG5hbWVfY2l0ZXh0KSB0ZXh0X3BhdHRl cm5fb3BzKTsNCg0KQU5BTFlaRSB0ZXN0X2NpdGV4dDsNCg0KU0VUIEVOQUJM RV9TRVFTQ0FOID0gT047DQoNCi0tIGluZGV4ICJpZHhfdGVzdF9jaXRleHRf bmFtZV9jaXRleHQiIGlzIG5vdCB1c2VkDQotLSBBZ2dyZWdhdGUgIChjb3N0 PTIzMTMyNC41Ni4uMjMxMzI0LjU3IHJvd3M9MSB3aWR0aD04KSAoYWN0dWFs CnRpbWU9OTQ2OS43OTMuLjk0NjkuNzkzIHJvd3M9MSBsb29wcz0xKQ0KLS0g ICAtPiAgU2VxIFNjYW4gb24gdGVzdF9jaXRleHQgIChjb3N0PTAuMDAuLjIz MTI4NC45OCByb3dzPTE1ODMzIHdpZHRoPTApCihhY3R1YWwgdGltZT05NDY5 Ljc5MC4uOTQ2OS43OTAgcm93cz0wIGxvb3BzPTEpDQotLSAgICAgICAgIEZp bHRlcjogKChuYW1lX2NpdGV4dCB+fiAna2FudHdlJSc6OmNpdGV4dCkgQU5E IChDT0FMRVNDRShmbGFnLAowKSA8IDIpKQ0KLS0gICAgICAgICBSb3dzIFJl bW92ZWQgYnkgRmlsdGVyOiA5NTAwMDAwDQotLSBQbGFubmluZyB0aW1lOiAw LjA1OCBtcw0KLS0gRXhlY3V0aW9uIHRpbWU6IDk0NjkuODE3IG1zDQpFWFBM QUlOIEFOQUxZWkUgU0VMRUNUIENPVU5UKCopDQogICAgICAgICAgICAgICAg RlJPTSB0ZXN0X2NpdGV4dA0KICAgICAgICAgICAgICAgIFdIRVJFIChuYW1l X2NpdGV4dCBMSUtFICgna2FudHdlJyB8fCAnJScpIDo6IGNpdGV4dCkgQU5E CkNPQUxFU0NFKGZsYWcsIDApIDwgMjsNCg0KLS0gaW5kZXggImlkeF90ZXN0 X2NpdGV4dF9uYW1lX2NpdGV4dF9sb3dlciIgaXMgdXNlZCBhcyBleHBlY3Rl ZA0KLS0gQWdncmVnYXRlICAoY29zdD01LjM4Li41LjM5IHJvd3M9MSB3aWR0 aD04KSAoYWN0dWFsIHRpbWU9MC4wMjQuLjAuMDI0CnJvd3M9MSBsb29wcz0x KQ0KLS0gICAtPiAgSW5kZXggU2NhbiB1c2luZyBpZHhfdGVzdF9jaXRleHRf bmFtZV9jaXRleHRfbG93ZXIgb24gdGVzdF9jaXRleHQgCihjb3N0PTAuNTYu LjQuNTkgcm93cz0zMTcgd2lkdGg9MCkgKGFjdHVhbCB0aW1lPTAuMDEzLi4w LjAxMyByb3dzPTAKbG9vcHM9MSkNCi0tICAgICAgICAgSW5kZXggQ29uZDog KChsb3dlcigobmFtZV9jaXRleHQpOjp0ZXh0KSB+Pj1+ICdrYW50d2UnOjp0 ZXh0KSBBTkQKKGxvd2VyKChuYW1lX2NpdGV4dCk6OnRleHQpIH48fiAna2Fu dHdmJzo6dGV4dCkpDQotLSAgICAgICAgIEZpbHRlcjogKChDT0FMRVNDRShm bGFnLCAwKSA8IDIpIEFORCAobG93ZXIoKG5hbWVfY2l0ZXh0KTo6dGV4dCkK fn4gJ2thbnR3ZSUnOjp0ZXh0KSkNCi0tIFBsYW5uaW5nIHRpbWU6IDAuMTM1 IG1zDQotLSBFeGVjdXRpb24gdGltZTogMC4wNDkgbXMNCkVYUExBSU4gQU5B TFlaRSBTRUxFQ1QgQ09VTlQoKikNCiAgICAgICAgICAgICAgICBGUk9NIHRl c3RfY2l0ZXh0DQogICAgICAgICAgICAgICAgV0hFUkUgKGxvd2VyKG5hbWVf Y2l0ZXh0KSBMSUtFICgna2FudHdlJyB8fCAnJScpKSBBTkQKQ09BTEVTQ0Uo ZmxhZywgMCkgPCAyOw0KDQotLSBpbmRleCAiaWR4X3Rlc3RfY2l0ZXh0X25h bWVfY2l0ZXh0IiBpcyBub3QgdXNlZA0KLS0gQWdncmVnYXRlICAoY29zdD0y MDc2NTMuOTAuLjIwNzY1My45MSByb3dzPTEgd2lkdGg9OCkgKGFjdHVhbAp0 aW1lPTkxNTQuNjMzLi45MTU0LjYzNCByb3dzPTEgbG9vcHM9MSkNCi0tICAg LT4gIFNlcSBTY2FuIG9uIHRlc3RfY2l0ZXh0ICAoY29zdD0wLjAwLi4yMDc1 MzUuMTUgcm93cz00NzUwMCB3aWR0aD0wKQooYWN0dWFsIHRpbWU9OTE1NC42 MjkuLjkxNTQuNjI5IHJvd3M9MCBsb29wcz0xKQ0KLS0gICAgICAgICBGaWx0 ZXI6IChuYW1lX2NpdGV4dCB+fiAna2FudHdlJSc6OnRleHQpDQotLSAgICAg ICAgIFJvd3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDk1MDAwMDANCi0tIFBsYW5u aW5nIHRpbWU6IDAuMDU0IG1zDQotLSBFeGVjdXRpb24gdGltZTogOTE1NC42 NTUgbXMNCkVYUExBSU4gQU5BTFlaRSBTRUxFQ1QgQ09VTlQoKikNCiAgICAg ICAgICAgICAgICBGUk9NIHRlc3RfY2l0ZXh0DQogICAgICAgICAgICAgICAg V0hFUkUgKG5hbWVfY2l0ZXh0IExJS0UgKCdrYW50d2UnIHx8ICclJykpOw0K DQotLSBpbmRleCAidXNpbmcgaWR4X3Rlc3RfY2l0ZXh0X25hbWVfY2l0ZXh0 X2xvd2VyIiBpcyB1c2VkIGFzIGV4cGVjdGVkDQotLSBBZ2dyZWdhdGUgIChj b3N0PTYuOTYuLjYuOTcgcm93cz0xIHdpZHRoPTgpIChhY3R1YWwgdGltZT0w LjAyMS4uMC4wMjEKcm93cz0xIGxvb3BzPTEpDQotLSAgIC0+ICBJbmRleCBT Y2FuIHVzaW5nIGlkeF90ZXN0X2NpdGV4dF9uYW1lX2NpdGV4dF9sb3dlciBv biB0ZXN0X2NpdGV4dCAKKGNvc3Q9MC41Ni4uNC41OCByb3dzPTk1MCB3aWR0 aD0wKSAoYWN0dWFsIHRpbWU9MC4wMTguLjAuMDE4IHJvd3M9MApsb29wcz0x KQ0KLS0gICAgICAgICBJbmRleCBDb25kOiAoKGxvd2VyKChuYW1lX2NpdGV4 dCk6OnRleHQpIH4+PX4gJ2thbnR3ZSc6OnRleHQpIEFORAoobG93ZXIoKG5h bWVfY2l0ZXh0KTo6dGV4dCkgfjx+ICdrYW50d2YnOjp0ZXh0KSkNCi0tICAg ICAgICAgRmlsdGVyOiAobG93ZXIoKG5hbWVfY2l0ZXh0KTo6dGV4dCkgfn4g J2thbnR3ZSUnOjp0ZXh0KQ0KLS0gUGxhbm5pbmcgdGltZTogMC4xNzYgbXMN Ci0tIEV4ZWN1dGlvbiB0aW1lOiAwLjA2NSBtcw0KRVhQTEFJTiBBTkFMWVpF IFNFTEVDVCBDT1VOVCgqKQ0KICAgICAgICAgICAgICAgIEZST00gdGVzdF9j aXRleHQNCiAgICAgICAgICAgICAgICBXSEVSRSAobG93ZXIobmFtZV9jaXRl eHQpIExJS0UgKCdrYW50d2UnIHx8ICclJykpOw0KDQotLSBFTkFCTEVfU0VR U0NBTiA9IG9mZiB0byBzaG93IHRoYXQgcXllcnkgcGxhbm5lciBub3QgZXZl biBjb25zaWRlciB0byB1c2UKaW5kZXggaW4gZmlyc3QgcXVlcnkNClNFVCBF TkFCTEVfU0VRU0NBTiA9IE9GRjsNCg0KLS0gaW5kZXggImlkeF90ZXN0X2Np dGV4dF9uYW1lX2NpdGV4dCIgc3RpbGwgaXMgbm90IHVzZWQgZXZlbiBpZgpF TkFCTEVfU0VRU0NBTiA9IG9mZjsNCi0tIEFnZ3JlZ2F0ZSAgKGNvc3Q9MTAw MDAyMzEzMjQuNTYuLjEwMDAwMjMxMzI0LjU3IHJvd3M9MSB3aWR0aD04KSAo YWN0dWFsCnRpbWU9OTcxMS4yOTEuLjk3MTEuMjkxIHJvd3M9MSBsb29wcz0x KQ0KLS0gICAtPiAgU2VxIFNjYW4gb24gdGVzdF9jaXRleHQgIChjb3N0PTEw MDAwMDAwMDAwLjAwLi4xMDAwMDIzMTI4NC45OApyb3dzPTE1ODMzIHdpZHRo PTApIChhY3R1YWwgdGltZT05NzExLjI4OS4uOTcxMS4yODkgcm93cz0wIGxv b3BzPTEpDQotLSAgICAgICAgIEZpbHRlcjogKChuYW1lX2NpdGV4dCB+fiAn a2FudHdlJSc6OmNpdGV4dCkgQU5EIChDT0FMRVNDRShmbGFnLAowKSA8IDIp KQ0KLS0gICAgICAgICBSb3dzIFJlbW92ZWQgYnkgRmlsdGVyOiA5NTAwMDAw DQotLSBQbGFubmluZyB0aW1lOiAwLjA4OCBtcw0KLS0gRXhlY3V0aW9uIHRp bWU6IDk3MTEuMzI1IG1zDQpFWFBMQUlOIEFOQUxZWkUgU0VMRUNUIENPVU5U KCopDQogICAgICAgICAgICAgICAgRlJPTSB0ZXN0X2NpdGV4dA0KICAgICAg ICAgICAgICAgIFdIRVJFIChuYW1lX2NpdGV4dCBMSUtFICgna2FudHdlJyB8 fCAnJScpIDo6IGNpdGV4dCkgQU5ECkNPQUxFU0NFKGZsYWcsIDApIDwgMjsN Cg0KLS0gaW5kZXggInVzaW5nIGlkeF90ZXN0X2NpdGV4dF9uYW1lX2NpdGV4 dF9sb3dlciIgaXMgdXNlZCBhcyBleHBlY3RlZA0KLS0gQWdncmVnYXRlICAo Y29zdD01LjM4Li41LjM5IHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9 MC4wMTIuLjAuMDEyCnJvd3M9MSBsb29wcz0xKQ0KLS0gICAtPiAgSW5kZXgg U2NhbiB1c2luZyBpZHhfdGVzdF9jaXRleHRfbmFtZV9jaXRleHRfbG93ZXIg b24gdGVzdF9jaXRleHQgCihjb3N0PTAuNTYuLjQuNTkgcm93cz0zMTcgd2lk dGg9MCkgKGFjdHVhbCB0aW1lPTAuMDExLi4wLjAxMSByb3dzPTAKbG9vcHM9 MSkNCi0tICAgICAgICAgSW5kZXggQ29uZDogKChsb3dlcigobmFtZV9jaXRl eHQpOjp0ZXh0KSB+Pj1+ICdrYW50d2UnOjp0ZXh0KSBBTkQKKGxvd2VyKChu YW1lX2NpdGV4dCk6OnRleHQpIH48fiAna2FudHdmJzo6dGV4dCkpDQotLSAg ICAgICAgIEZpbHRlcjogKChDT0FMRVNDRShmbGFnLCAwKSA8IDIpIEFORCAo bG93ZXIoKG5hbWVfY2l0ZXh0KTo6dGV4dCkKfn4gJ2thbnR3ZSUnOjp0ZXh0 KSkNCi0tIFBsYW5uaW5nIHRpbWU6IDAuMTA5IG1zDQotLSBFeGVjdXRpb24g dGltZTogMC4wMzggbXMNCkVYUExBSU4gQU5BTFlaRSBTRUxFQ1QgQ09VTlQo KikNCiAgICAgICAgICAgICAgICBGUk9NIHRlc3RfY2l0ZXh0DQogICAgICAg ICAgICAgICAgV0hFUkUgKGxvd2VyKG5hbWVfY2l0ZXh0KSBMSUtFICgna2Fu dHdlJyB8fCAnJScpKSBBTkQKQ09BTEVTQ0UoZmxhZywgMCkgPCAyOw0KDQot LSBpbmRleCAiaWR4X3Rlc3RfY2l0ZXh0X25hbWVfY2l0ZXh0IiBpcyB1c2Vk IGJ1dCBleGVjdXRpb24gaW4gdW5leHBlY3RlZAp3YXkNCi0tIEFnZ3JlZ2F0 ZSAgKGNvc3Q9MzkyMTMxLjk5Li4zOTIxMzIuMDEgcm93cz0xIHdpZHRoPTgp IChhY3R1YWwKdGltZT0xMTQ5NS41MzUuLjExNDk1LjUzNSByb3dzPTEgbG9v cHM9MSkNCi0tICAgLT4gIEJpdG1hcCBIZWFwIFNjYW4gb24gdGVzdF9jaXRl eHQgIChjb3N0PTE4NDQ3OC4xMC4uMzkyMDEzLjI0CnJvd3M9NDc1MDAgd2lk dGg9MCkgKGFjdHVhbCB0aW1lPTExNDk1LjUzMi4uMTE0OTUuNTMyIHJvd3M9 MCBsb29wcz0xKQ0KLS0gICAgICAgICBGaWx0ZXI6IChuYW1lX2NpdGV4dCB+ fiAna2FudHdlJSc6OnRleHQpDQotLSAgICAgICAgIFJvd3MgUmVtb3ZlZCBi eSBGaWx0ZXI6IDk1MDAwMDANCi0tICAgICAgICAgSGVhcCBCbG9ja3M6IGV4 YWN0PTM1ODc0IGxvc3N5PTUyOTEyDQotLSAgICAgICAgIC0+ICBCaXRtYXAg SW5kZXggU2NhbiBvbiBpZHhfdGVzdF9jaXRleHRfbmFtZV9jaXRleHQgCihj b3N0PTAuMDAuLjE4NDQ2Ni4yMiByb3dzPTk0OTk5MzIgd2lkdGg9MCkgKGFj dHVhbCB0aW1lPTE2MjguNjA3Li4xNjI4LjYwNwpyb3dzPTk1MDAwMDAgbG9v cHM9MSkNCi0tIFBsYW5uaW5nIHRpbWU6IDAuMDU5IG1zDQotLSBFeGVjdXRp b24gdGltZTogMTE0OTUuNTYxIG1zDQpFWFBMQUlOIEFOQUxZWkUgU0VMRUNU IENPVU5UKCopDQogICAgICAgICAgICAgICAgRlJPTSB0ZXN0X2NpdGV4dA0K ICAgICAgICAgICAgICAgIFdIRVJFIChuYW1lX2NpdGV4dCBMSUtFICgna2Fu dHdlJyB8fCAnJScpKTsNCg0KLS0gaW5kZXggImlkeF90ZXN0X2NpdGV4dF9u YW1lX2NpdGV4dF9sb3dlciIgaXMgdXNlZCBhcyBleHBlY3RlZA0KLS0gQWdn cmVnYXRlICAoY29zdD02Ljk2Li42Ljk3IHJvd3M9MSB3aWR0aD04KSAoYWN0 dWFsIHRpbWU9MC4wNTQuLjAuMDU0CnJvd3M9MSBsb29wcz0xKQ0KLS0gICAt PiAgSW5kZXggU2NhbiB1c2luZyBpZHhfdGVzdF9jaXRleHRfbmFtZV9jaXRl eHRfbG93ZXIgb24gdGVzdF9jaXRleHQgCihjb3N0PTAuNTYuLjQuNTggcm93 cz05NTAgd2lkdGg9MCkgKGFjdHVhbCB0aW1lPTAuMDUyLi4wLjA1MiByb3dz PTAKbG9vcHM9MSkNCi0tICAgICAgICAgSW5kZXggQ29uZDogKChsb3dlcigo bmFtZV9jaXRleHQpOjp0ZXh0KSB+Pj1+ICdrYW50d2UnOjp0ZXh0KSBBTkQK KGxvd2VyKChuYW1lX2NpdGV4dCk6OnRleHQpIH48fiAna2FudHdmJzo6dGV4 dCkpDQotLSAgICAgICAgIEZpbHRlcjogKGxvd2VyKChuYW1lX2NpdGV4dCk6 OnRleHQpIH5+ICdrYW50d2UlJzo6dGV4dCkNCi0tIFBsYW5uaW5nIHRpbWU6 IDAuMTA1IG1zDQotLSBFeGVjdXRpb24gdGltZTogMC4wNzkgbXMNCkVYUExB SU4gQU5BTFlaRSBTRUxFQ1QgQ09VTlQoKikNCiAgICAgICAgICAgICAgICBG Uk9NIHRlc3RfY2l0ZXh0DQogICAgICAgICAgICAgICAgV0hFUkUgKGxvd2Vy KG5hbWVfY2l0ZXh0KSBMSUtFICgna2FudHdlJyB8fCAnJScpKTsNCg0KLS0g aW5kZXggImlkeF90ZXN0X2NpdGV4dF9uYW1lX2NpdGV4dCIgaXMgdXNlZA0K LS0gSW5kZXggT25seSBTY2FuIHVzaW5nIGlkeF90ZXN0X2NpdGV4dF9uYW1l X2NpdGV4dCBvbiB0ZXN0X2NpdGV4dCAKKGNvc3Q9MC41Ni4uNC41OCByb3dz PTEgd2lkdGg9MzMpIChhY3R1YWwgdGltZT0wLjAyMC4uMC4wMjAgcm93cz0w Cmxvb3BzPTEpDQotLSAgIEluZGV4IENvbmQ6IChuYW1lX2NpdGV4dCA9ICdr YW50d2UnOjp0ZXh0KQ0KLS0gICBIZWFwIEZldGNoZXM6IDANCi0tIFBsYW5u aW5nIHRpbWU6IDAuMTAwIG1zDQotLSBFeGVjdXRpb24gdGltZTogMC4wNTIg bXMNCkVYUExBSU4gQU5BTFlaRSBTRUxFQ1QgbmFtZV9jaXRleHQNCiAgICAg ICAgICAgICAgICBGUk9NIHRlc3RfY2l0ZXh0DQogICAgICAgICAgICAgICAg V0hFUkUgbmFtZV9jaXRleHQgPSAna2FudHdlJyA6OiBURVhUOw0KDQotLSBp bmRleCAiaWR4X3Rlc3RfY2l0ZXh0X25hbWVfY2l0ZXh0IiBpcyB1c2VkIGJ1 dCBpbiB1bmV4cGVjdGVkIHdheQ0KLS0gQml0bWFwIEhlYXAgU2NhbiBvbiB0 ZXN0X2NpdGV4dCAgKGNvc3Q9MTg0NDY2LjIyLi4zOTIwMDEuMzcgcm93cz0x CndpZHRoPTMzKSAoYWN0dWFsIHRpbWU9MTA4MjIuMzIxLi4xMDgyMi4zMjEg cm93cz0wIGxvb3BzPTEpDQotLSAgIEZpbHRlcjogKG5hbWVfY2l0ZXh0ID0g J2thbnR3ZSc6OmNpdGV4dCkNCi0tICAgUm93cyBSZW1vdmVkIGJ5IEZpbHRl cjogOTUwMDAwMA0KLS0gICBIZWFwIEJsb2NrczogZXhhY3Q9MzU4NzQgbG9z c3k9NTI5MTINCi0tICAgLT4gIEJpdG1hcCBJbmRleCBTY2FuIG9uIGlkeF90 ZXN0X2NpdGV4dF9uYW1lX2NpdGV4dCAKKGNvc3Q9MC4wMC4uMTg0NDY2LjIy IHJvd3M9OTQ5OTkzMiB3aWR0aD0wKSAoYWN0dWFsIHRpbWU9MTU5NS43MTUu LjE1OTUuNzE1CnJvd3M9OTUwMDAwMCBsb29wcz0xKQ0KLS0gUGxhbm5pbmcg dGltZTogMC4wOTggbXMNCi0tIEV4ZWN1dGlvbiB0aW1lOiAxMDgyMi4zNTkg bXMNCkVYUExBSU4gQU5BTFlaRSBTRUxFQ1QgbmFtZV9jaXRleHQNCiAgICAg ICAgICAgICAgICBGUk9NIHRlc3RfY2l0ZXh0DQogICAgICAgICAgICAgICAg V0hFUkUgbmFtZV9jaXRleHQgPSAna2FudHdlJzsNCgoK
pawel.kania@moneyhouse.de writes:
> [ citext LIKE operator isn't indexable ]
Yeah. Don't hold your breath waiting for that to get better :-(.
The problem is that there's no way for extensions to plug into the
"special index operator" mechanism in the planner, so there's no
way to derive indexable range conditions from the citext LIKE operator.
That's probably solvable with enough sweat, but so far no one has
taken an interest in working on it.
Even if that were fixed, I'm not sure that your examples would be
indexable, because the patterns all start with case-foldable characters.
The "special index operator" conversion for ILIKE just throws up its hands
when it sees that, and I doubt citext could do any better.
regards, tom lane