Re: citext LIKE search bug - Mailing list pgsql-bugs

Thanks for the answer and clarification. Yes, I'm using trigram index now, and it's working well. I love n-grams for fuzzy matching, and the Postgres implementation is a thing of beauty. It is a bit funny in that you have to use LIKE to hint the planner to use the index on a = search.

I've been working with Postgres for a couple of years now, coming from another environment. In our case, Postgres is not a front-line system, it's a secondary system for aggregation, analysis and pushing things through to a visualization platform. Postgres is just awesome but, whenever you're shifting to a new context, it's easy to come in with bad priors. That's me and citext, all the way down.

Postgres has case-sensitive text searches, as you all know. I guess that becomes a bit more optional in PG 12 with case and/or diacritical-blind collations...but we're on PG 11 deployed on RDS, so we're unlikely to see PG 12 a lot before ~March 2020. We started on 9.5 or 9.6. What I was used to are case-blind searches with B-trees of up to the first 1024 bytes of character data (it's all in UTF-16) that work on = <> and starts_with% without any functions or castings. So, Bad Priors. In Postgres:

-- Searches on text are case-sensitive (I figured that out early.)

-- The planner only uses a function index if the query uses *exactly* the same query. This I found out *last week*.

It's that second bit that was the killer, and that makes me question the point of citext at all. For background, I dug through my notes and assembled a chronological list of days when users asked me for case-sensitive search in their database over the past 30+ years:

....

Exactly never. My goal was to make case-blind searches automatic and transparent...no special goo in the query:

But, as it turns out, you lose the indexes unless you do exactly something like that with citext. Like if you use the clever (not my clever) idea of B-treeing a hash of the text:

That works perfectly for = searches, but there's a whole lot of complexity in the query syntax for someone used to this to do the same thing:

 

Is there, or could there be, any way for the query planner to guess that an expression-based index should be searched other than recapitulating the entire expression in the query? For example, my sample data has about 8M rows with a citext field with old values (a sort of audit-log thing). The length of these values are typically very small. 6 records in the entire data set have long values. If I index the first 128 characters, I think that gives me the original value for 99.9999+% of the rows. So, something like this - or the same for text: 

For the index to be used today, the search needs to be

But here's the thing, Postgres _knows_ that the expression index is on the first 128 characters of the field. If the query value is within that value on an = search, then the B-tree of the substring could be used, and would return 100% accurate results.

Is that sort of query planner optimization conceivable in Postgres? I'd offer to help but, well, I'm absolutely unqualified. I knew a guy who gave up his PhD on mapping five-dimensional math into four-dimensional space. He said "I was smart enough to ask the question, but not even close to smart enough to answer it." That's me on my query planner idea/question.