|From:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|To:||Morris de Oryx <morrisdeoryx(at)gmail(dot)com>|
|Subject:||Re: citext LIKE search bug|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
Morris de Oryx <morrisdeoryx(at)gmail(dot)com> writes:
> I'm using citext fields, and am working through how to build indexes that
> the planner recognizes. I found this paragraph in the PG 11 release notes:
> "Allow creation of indexes that can be used by LIKE comparisons on citext
> columns (Alexey Chernyshov)
> To do this, the index must be created using the citext_pattern_ops operator
> As far as I can tell, this doesn't work, or at least not in my case with an
> expression index.
Hm. I found the original submission in the archives , and Alexey
explicitly *didn't* claim that that patch was sufficient to enable LIKE
index optimizations on citext. Indeed it obviously isn't, since it just
created some weirdly-named operators without connecting them up to the
LIKE plumbing in any way. So this seems like a case of the REL11 release
notes author (probably Bruce) not reading too closely, which doesn't seem
like entirely his fault since the commit message was totally content-free
about what the point of the patch was . But anyway, right now it seems
to me that citext_pattern_ops has exactly zero value, which makes me
wonder why we committed it in advance of some use-case getting filled in.
It's not such a large patch that it had to get in to reduce its maintenance
While I'm looking at it, I notice that the patch failed to honor the
scalarltsel-vs-scalarlesel, scalargtsel-vs-scalargesel distinction
that had been created not too long before. Sigh.
Anyway it seems like the only near-term to-do item here is to correct
the v11 release notes to not claim that citext_pattern_ops does anything
useful. Maybe we should just remove the entry altogether.
regards, tom lane
|Next Message||Morris de Oryx||2019-09-19 04:30:15||Re: citext LIKE search bug|
|Previous Message||Tom Lane||2019-09-19 03:37:19||Re: BUG #16013: Unexpected results from bit field query|