A recent question on a Naver Cafe posed an interesting question on "Why PostgreSQL planner doesn't access the index when we use LIKE operator in the where clause?" The following URLs are the question and the answers which prompted me to write this note.
https://cafe.naver.com/dbian/2914
https://cafe.naver.com/dbian/4360
https://cafe.naver.com/dbian/2873
I will not provide the test script and the result here due to this bulletin board's volume limitation. But you can get the demonstration script in the URLs above.
When I read the article I thought the writer must have found a PostgreSQL bug.
However, when I did the same test on my laptop computer, unlike what the original post showed us, the optimizer accessed the index without any problem.
So I thought the differences between the writer's result and mine might be due to the PostgreSQL's version. So I repeated the test on PostgreSQL version 13, 12, 11 and 10. Interestingly all my test result showed me that the optimizer decided to access the index.
What's wrong with the writer's experimentation? Am I doing the test in the right way? I was at a loss for a while.
But when I thought about the process I did when I installed PostgreSQL, the LC_COLLATE setting crossed my mind. When I make a database, I set the LC_COLLATE to C. Some experts in Korea argue that when you make a database you should set it to C. Please refer to the answers here.
https://www.facebook.com/hashtag/%ED%95%9C%EA%B8%80%EC%A0%95%EB%A0%AC?source=feed_text.
So I set the LC_COLLATE to en_US.UTF-8 which is a default value and did the same test again. And the optimizer didn't access the index which is in agreement with the writer's test result.
Conclusion
When you make a database, set the LC_COLLATE value to C.
