PostgreSQL 속도 문제 원인 및 해결 방법

1. 인덱스 미사용
   • EXPLAIN ANALYZE를 사용하여 쿼리 실행 계획을 확인하고 인덱스를 적절히 추가
   • B-Tree, GIN, GiST, BRIN 등의 적절한 인덱스를 고려.
   • pg_stat_user_indexes를 확인하여 사용되지 않는 인덱스 제거를 검토.
2. 불필요한 시퀀셜 스캔
   • SET enable_seqscan = OFF; 실행 후 EXPLAIN ANALYZE로 쿼리를 테스트.
   • 인덱스를 추가해도 시퀀셜 스캔이 발생하면 ANALYZE 및 VACUUM을 실행.
3. 트랜잭션 누락 및 불필요한 락
   • COMMIT을 하지 않는 긴 트랜잭션은 pg_stat_activity에서 확인하고 적절히 종료.
   • SELECT ... FOR UPDATE 같은 쿼리는 필요하지 않은 경우 제거.
4. 쿼리 최적화 부족
   • 조인 연산이 많으면 EXPLAIN ANALYZE를 통해 조인 방식(Nested Loop, Hash Join, Merge Join)을 확인.
   • 필요하지 않은 ORDER BY, DISTINCT, GROUP BY를 제거.
5. 파라미터 튜닝 (postgresql.conf 설정 변경)
   • 작업량이 많다면 work_mem 증가

     SET work_mem = '64MB';  -- 기본값보다 증가

   • 자동 커밋을 줄이려면 synchronous_commit 조정

     SET synchronous_commit = OFF;

   • 단일 커넥션 성능 개선 (max_connections 조정)
     • 너무 높으면 성능이 저하되므로, 커넥션 풀링을 고려.
6. VACUUM 및 ANALYZE 미실행
   • 대량의 INSERT, UPDATE, DELETE 후 VACUUM을 실행하여 테이블을 정리.

     VACUUM ANALYZE;

7. 대량 데이터 처리 최적화
   • BULK INSERT 사용:

     COPY my_table FROM '/path/to/file.csv' DELIMITER ',' CSV;
     

   • PARTITIONING 사용: 대량 데이터를 처리할 때 테이블을 파티셔닝하면 검색 속도가 빨라질 수 있다.
8. 서버 리소스 문제
   • CPU 사용률, 메모리, 디스크 I/O를 확인.
   • pg_stat_statements를 활성화하여 가장 느린 쿼리를 확인 하고 조치한다.

     SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;