postgres ilike

문제

- 너무 느림

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE. There are also !~~ and !~~* operators that represent NOT LIKE and NOT ILIKE, respectively. All of these operators are PostgreSQL-specific.

해결 방안 모색중

 

 

- 기존 쿼리

@Query(value = "select * from expedia_region_union where (region_name_full ILIKE  '%' || :whatEverRegionName || '%'  or region_name_full_kr ILIKE '%' || :whatEverRegionName || '%') ORDER BY region_id ASC limit :limitValue offset :offsetValue", nativeQuery = true)
    List<ExpediaRegionUnion> findByRegionNameFullOrRegionNameFullKrWithoutType(@Param("whatEverRegionName") String whatEverRegionName,
                                                                               @Param("limitValue") int limitValue,
                                                                               @Param("offsetValue") int offsetValue);

    @Query(value = "select count(*) from expedia_region_union where region_type = :regionType and (region_name_full ILIKE '%' || :whatEverRegionName || '%'  or region_name_full_kr ILIKE '%' || :whatEverRegionName || '%')", nativeQuery = true)
    Long findByRegionNameFullOrRegionNameFullKrCnt(@Param("whatEverRegionName") String whatEverRegionName,
                                                   @Param("regionType") String regionType);

    @Query(value = "select count(*) from expedia_region_union where  (region_name_full ILIKE '%' || :whatEverRegionName || '%'  or region_name_full_kr ILIKE '%' || :whatEverRegionName || '%')", nativeQuery = true)
    Long findByRegionNameFullOrRegionNameFullKrCntWithoutType(@Param("whatEverRegionName") String whatEverRegionName);

파훼법

- 내 경우엔 대소문자 구별하여 검색 할 필요없음.

- lower() 연산자로 다 소문자로 만들어서 검색하기로 하고 그에 맞게 인덱스를 생성.

--create 인덱스이름 on 테이블 ((lower(컬럼이름)));
create index idx_lower_region_name_full_kr on expedia_region_union ((lower(region_name_full_kr)));

결론

 

-> 인덱스생성으로 빨라졌음(기분탓인가?!!!!!!!!)

--create index idx_lower_region_name_full_kr on expedia_region_union ((lower(region_name_full_kr)));

--create index idx_lower_region_name_full_region_type on expedia_region_union ((lower(region_name_full)),(lower(region_type)));


create index idx_lower_region_name_full_n_kr_region_type on expedia_region_union ((lower(region_name_full)),(lower(region_name_full_kr)), (lower(region_type)));

습득 지식

- 얼마나 빨라졌는지를 체크할 수 있도록 항상 맨 처음 문제 상태를 수치로 기록하자

---

참고 링크

- 아래와 같다.

https://www.postgresql.org/docs/current/functions-matching.html

 

PostgreSQL: Documentation: 12: 9.7. Pattern Matching

 

https://www.google.com/search?newwindow=1&sxsrf=ALeKk01Bhjbxt6CFopHkjsc_G17Q7Pe4Iw:1585821600401&q=postgres+ilike+index&sa=X&ved=2ahUKEwjw3qzpvcnoAhWaZt4KHeavAxUQ1QIoBHoECAsQBQ&biw=1440&bih=766

postgres ilike index - Google 검색

 

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

 

https://postgresql.kr/docs/11/sql-createindex.html

CREATE INDEX