20200327 postgresql unnest

1.Postgresql unnest

전에 postgres의 executemany에 관해 포스트한 적이 있는데, ( with psycopg2.connect(host=~~~~) as conn: with conn.cursor() as cur: 에서 cur.executemany()...) 알고보니 exectuemany가 query를 한번에 날리는 것이 아닌 connection을 한번만 할 뿐 query는 똑같이 여러번 한다고 한다. 그럼 더 빠른 query 실행문은 뭘까? 바로 unnest를 활용한 실행문이다. 다음을 보자.

def pg_cur(auto_commit=False):
    with pg2.connect(database=settings.POSTGRES_VECTOR_DB,
                     user=settings.POSTGRES_VECTOR_USER,
                     password=settings.POSTGRES_VECTOR_PASS,
                     host=settings.POSTGRES_VECTOR_HOST,
                     port=settings.POSTGRES_VECTOR_PORT) as conn:
        with conn.cursor() as cur:
            yield cur

        if auto_commit:
            conn.commit()

우선 위와 같은 함수로 postgres에 connection을 설정한다.

내가 회사에서 적용했던 executemany는 다음과 같다.

    rows = [(project_id, str(phrase_id), str(phrase_no), text, vs) \
            for phrase_id, phrase_no, text, vs in zip(phr_ids, phr_nos, texts, vec_str_list)]
 
    with pg_cur(auto_commit=True) as cur:
        _delete_many_phrases(cur, project_id, phr_ids)
        cur.executemany("""
            insert table_name (prj_id, phr_id, s_idx, sentence, vector)
            values (%s, %s, %s, %s, %s::cube)
        """, tuple(rows))

하지만 unnest를 활용한다면 다음과 같이 적용할 수 있다.

    rows = [(project_id, str(phrase_id), str(phrase_no), text, vs) \
            for phrase_id, phrase_no, text, vs in zip(phr_ids, phr_nos, texts, vec_str_list)]
 	prj_ids = [project_id for phr_id in phr_ids]
    with pg_cur(auto_commit=True) as cur:
        cur.execute("""
            insert into table_name (prj_id, phr_id, s_idx, sentence, vector)
            select unnest(ARRAY '%(prj_ids)s'), unnest(ARRAY '%(phr_ids)s'), unnest(ARRAY '%(phr_nos)s'), unnest(ARRAY '%(texts)s'), unnest(ARRAY '%(vec_str_list)s::cube'))
        """, {'prj_ids':prj_ids,'phr_ids':phr_ids,'phr_nos':phr_nos,'texts':texts)

참고: https://trvrm.github.io/bulk-psycopg2-inserts.html

trvrm.github.io

https://charsyam.wordpress.com/2018/05/03/%EC%9E%85-%EA%B0%9C%EB%B0%9C-%EC%8B%A0%EB%AC%98%ED%95%9C-python-locals-%EC%9D%98-%EC%84%B8%EA%B3%84/

[입 개발] 신묘한 Python locals() 의 세계