postgresql pg_bigm, ts_vector, explain (analyse), and clause

alembic + sqlalchemy + postgresql 검색 만들기

from sqlalchemy.dialects.postgresql import TSVECTOR
 
class Post(CreatedMixin, Base):
    __tablename__ = "post"
 
    id: Mapped[str] = mapped_column(primary_key=True)
    search_keyword: Mapped[Optional[str]]
    search_vector: Mapped[Optional[TSVECTOR]] = mapped_column(TSVECTOR)
    __table_args__ = (
    # Index("ix_post_search_vector", "search_vector", postgresql_using="gin"),
		)

TSVECTOR 타입으로 column 생성

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("post", sa.Column("search_keyword", sa.String(), nullable=True))
    op.add_column(
        "post", sa.Column("search_vector", postgresql.TSVECTOR(), nullable=True)
    )
    op.create_index(
        "ix_post_search_vector",
        "post",
        ["search_vector"],
        unique=False,
        postgresql_using="gin",
    )
    # 트리거 함수 생성
    op.execute("""
    CREATE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
    BEGIN
        NEW.search_vector := to_tsvector('simple', NEW.search_keyword);
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
    """)
 
    # 트리거 생성
    op.execute("""
    CREATE TRIGGER update_search_vector_trigger
    BEFORE INSERT OR UPDATE ON post
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();
    """)
    # ### end Alembic commands ###
 
 
def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index("ix_post_search_vector", table_name="post", postgresql_using="gin")
    op.drop_column("post", "search_vector")
    op.drop_column("post", "search_keyword")
    # 트리거 삭제
    op.execute("DROP TRIGGER IF EXISTS update_search_vector_trigger ON post;")
 
    # 트리거 함수 삭제
    op.execute("DROP FUNCTION IF EXISTS update_search_vector;")
    # ### end Alembic commands ###
 

alembic migration 생성 DDL로 트리거랑 트리거함수를 만들어서 search_keyword가 들어오면 트리거 발동 후 search_vector로 변환하도록 수행 search_vector은 to_tsvector(’simple’) 사용. aws postgresql은 한국어 사전을 지원하지 않아서 preprocessing 해주고 simple로 토큰화만 수행해줌. 로컬에서 작동 안할수도 있는데 그거 locale문제다.

    async def search_post_by_query(
        self, search_query: str
    ) -> list[Post]:
        search_query = search_query.replace(" ", " | ")
        query = (
            select(Post)
            .where(Post.search_vector.bool_op("@@")(func.to_tsquery("simple", search_query)))
            .order_by(func.ts_rank_cd(Post.search_vector, func.to_tsquery("simple", search_query)).desc())
            .limit(20)
        )
        result = await self.session.scalars(query)
        return result.all()
 

sqlalchemy를 통한 검색은 위와 같이 수행

근데 “서울대입구” 를 검색할때 “서울대” 로 검색하면 나오지 않는 문제 발생. 이는 n-gram을 통해 해결해줘야함. 그래서 pg_bigm을 도입하기로 결정. pg_bigm은 pg_trgm과 다르게 한글을 지원하고 2글자 검색부터 지원함. 다만 용량이 걱정되어서 미리 테스트하고 적용하기로 함

pg_bigm extension이 없다면?

select * from pg_available_extensions

일단 이걸로 가능한 extension 목록을 확인하자. pg_bigm이 없다면?

# 도커 컨테이너 접속
docker exec -it {container name} /bin/bash
apt-get update
apt-get install gcc make
apt-get install postgresql-server-dev-{pg_version}
# 컨테이너 외부로 exit
exit
# pg_bigm github 들어가서 zip 파일 받고 압축 해제
docker cp ~/Downloads/pg_bigm-REL1_2_STABLE/ local_postgis:/
# 도커 컨테이너 접속
docker exec -it {container name} /bin/bash
cd /
cd {pg_bigm 폴더}
make USE_PGXS=1 PG_CONFIG={pg_config path}#export 해서 postgresql bin 폴더 위치 확인. 그 안에 있음
make USE_PGXS=1 PG_CONFIG={pg_config path} install
sudo apt-get install nano
nano {postgresql.conf path} # 아래 사진 참조해서 library load
#컨테이너 재시작

SHOW config_file

다 완료하면

select * from pg_available_extensions

여기에 pg_bigm이 뜰것

sqlalchemy에서 explain 하기

from sqlalchemy import and_, text
from sqlalchemy.dialects import postgresql
 
 
async with async_session() as session:
    query = (
        select(Post)
        .where(and_(Post.search_keyword.like(func.likequery("똠양궁")), Post.search_keyword.like(func.likequery("맛집"))))
    )
    raw = query.compile(compile_kwargs={"literal_binds": True}, dialect=postgresql.dialect())
    result = await session.execute(text(f"EXPLAIN (ANALYSE) {raw}"))
    for row in result:
        print(row)
 

compile을 통해 날쿼리로 바꿔주고 text형으로 바꿔준 다음 execute해야함

결과

('Bitmap Heap Scan on post  (cost=16.00..18.02 rows=1 width=1144) (actual time=0.092..0.092 rows=1 loops=1)',)
("  Recheck Cond: (((search_keyword)::text ~~ '%똠양궁%'::text) AND ((search_keyword)::text ~~ '%맛집%'::text))",)
('  Heap Blocks: exact=1',)
('  ->  Bitmap Index Scan on ix_post_search_keyword  (cost=0.00..16.00 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)',)
("        Index Cond: (((search_keyword)::text ~~ '%똠양궁%'::text) AND ((search_keyword)::text ~~ '%맛집%'::text))",)
('Planning Time: 0.373 ms',)
('Execution Time: 0.159 ms',)

and, or clause

from sqlalchemy import and_
 
query.where(and_(Post.search_keyword.like(func.likequery("똠양궁")), Post.search_keyword.like(func.likequery("맛집"))))

or, and 마찬가지

bigm index 만들기 (sqlalchemy + alembic)

__table_args__ = (
    Index("ix_post_search_keyword", "search_keyword", postgresql_using="gin", postgresql_ops={"search_keyword": "gin_bigm_ops"}),
)