database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 8836 게시물 읽기
No. 8836
비효율적인 실행계획
작성자
지우개
작성일
2011-08-03 17:29
조회수
9,088

 복잡한 쿼리가 아닌데 속도가 느려 실행계획을 보니 좀 이해할 수 없는 실행계획이 나와서요.

아래와 같이 가수, 앨범, 곡 3개의 테이블이 1:N 의 fk 를 갖고 있습니다.

ARTIST (

  id int4 pk,

  name varchar(255)

)


ALBUM (

  id int4 pk,

  title varchar(255),

  artist_id int4 fk,

  reg_date timestamp (index)

)


SONG (

  id int4 pk,

  title varchar(255),

  album_id int4 fk

)

 

최근 등록된 앨범 10개의 앨범이름, 가수이름, 수록곡수를 갖고오는 간단한 쿼리입니다.

SELECT

  ARTIST.name,

  ALBUM.title,

  (SELECT COUNT(id) FROM SONG WHERE album_id = ALBUM.id) AS track_num

FROM

  ALBUM, ARTIST

WHERE

  ALBUM.artist_id = ARITST.id

ORDER BY

  ALBUM.reg_date DESC 

LIMIT 10;

 

저는 당연히 ALBUM.reg_date 인덱스를 타고 10개를 갖고온후

ALBUM.artist_id = ARTIST.id 를 index scan 10번하고 track_num 을 구하기 위해 10번 서브쿼리가 돌아갈것을 기대했습니다.

 

그런데 실제 실행계획은 모든 앨범에 대해서 track_num 을 구하고 (그나마 다행히  SONG_IDX_ALBUM_ID를 사용해서) 

ARTIST 도  pk 가 아닌 seq scan 을 해서 갖고오고 있습니다.

cost도 300만을 훌쩍 넘기고요 (물론 실제 수행시간은 0.3 초정도로 기지는 않습니다.)

 

postgresql , 너가 알아서 했겠지,, 라고 넘어가려다 

 

 SELECT

    ARTIST.name,

   (SELECT COUNT(id) FROM SONG WHERE album_id = A.id) AS track_num

  FROM

    (SELECT id, title FROM ALBUM ORDER BY reg_date DESC LIMIT 10) AS A,

   ARTIST

WHERE

  ALBUM.artist_id = ARTIST.id

 

와 같이 FROM 안에 A 라는 서브쿼리로 먼저 뽑아내도록 했더니 1000 이하의 낮은 cost가 계산되고 

수행시간도 0.04초 이하로 팍 떨어집니다.

검색등으로 인해 쿼리가 동적으로 점점 복잡해지기때문에 후자처럼 사용하기가 힘든데

방법이 없을까요?

제가 더 공부해야하는지, 나름 맹신하는 postgresql 이 아직 더 성숙해야하는지.

제가 부족한거면 좋겠다는 생각으로 고수님들의 조언을 구합니다.

 

 

이 글에 대한 댓글이 총 1건 있습니다.

 쿼리의 작업 진행 순서가, 

from 다음의 객체들 (테이블, 뷰, 인라인뷰....)을 파악하는 일부터 시작하겠죠. 

윗 쿼리에서 인라인뷰를 쓰고 있다면, 그 인라인뷰의 작업비용부터 따집니다. 

윗 쿼리라면, SONG 테이블의 조건절에 N관계를 가지는 fk의 조건이기 때문에, 

그 결과가 얼마나 생길지 알 수가 없기 때문에, 그렇게 처리 할 것 같네요. 

이 경우는 song.album_id 칼럼에 인덱스가 있고, stat 수집기가 이 키에 해당 하는 row가 평균 몇개다 이런식의 정보가 수집된 상태라면 상황이 달라질지도 모르겠네요. 

9.x 대에서는 확인을 안해봤지만, 8.x 대에서는 경험상으로 보면, 

인라인뷰를 위와 같이 사용할 경우는 거의 인덱스를 사용하지 않습니다. 

where 절 뒤로 뺄 수 있다면, 그 방식을 사용해 보세요.

 

그런데, 윗 경우의 자료구조라면, 제가 설계한다면, 그냥 album 테이블에, track 수를 집계하고 있는 칼럼을 하나 만들어 둘 것 같네요. 

 

예제겠죠? 음반 설계 장난이 아닌데. 

김상기(ioseph)님이 2011-08-04 11:11에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
8839데이터 복원 시 duplicate key value violates unique constraint 문제 해결 방안? [1]
김희승
2011-08-23
9239
8838function 생성좀 봐주세요. [1]
이기자
2011-08-11
9163
8837timestamp 사용시 index를 걸어두어도 cost 개선없는 이유? [2]
김현진
2011-08-09
8913
8836비효율적인 실행계획 [1]
지우개
2011-08-03
9088
8835Postgresql 필드 속성이 자동 변경된경우
김범영
2011-08-03
8218
8834요일 구하기 [2]
iyob
2011-08-01
9804
8833숫자인 데이터만 뽑을수 있나요? [1]
강민정
2011-07-26
7722
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다