date 가 timestamp 이구요.. 그중에 월 단위로 id로 group by 하고 싶으시단 거죠?

nonun=# CREATE TABLE test(id varchar(100), date timestamp);

CREATE TABLE

nonun=# INSERT INTO test VALUES ('aaa', now());

INSERT 0 1

nonun=# SELECT * from test ;

id      |  date

-----+----------------------------

aaa   |  2006-05-24 16:57:47.945679

(1 row)

nonun=# CREATE INDEX index_test_date ON test ((date_trunc('month', date)::date));

CREATE INDEX

nonun=# \d test

Table "public.test"

Column  | Type                                      | Modifiers

--------+-----------------------------+-----------

id          | character varying(100)             | date | timestamp without time zone |

Indexes:

"index_test_date" btree ((date_trunc('month'::text, date)::date))

nonun=# EXPLAIN SELECT date_trunc('month', date)::date from test where date_trunc('month', date)::date='2006-05-01'::date;

QUERY PLAN

------------------------------------------------------------------------------

Index Scan using index_test_date on test (cost=0.00..4.69 rows=1 width=8)

  Index Cond: ((date_trunc('month'::text, date))::date = '2006-05-01'::date)

(2 rows)

이렇게 인덱스를 만드시면 되겠습니다. 인덱스 잘 타네요~ 검색하실 때는 위의 쿼리처럼 뒤에 '2006-05-01' 부분만 어플리케이션에서 바꿔주시면 되겠습니다.

문제는 ID로 group하여 count를 하면 속도가 느리다는 겁니다..

ㅠ_ㅠ

날짜로 조건 걸었을 때랑 안걸었을 때랑 별 차이 없거든요..

정말 속도 개선할 수 있는 방법이 없는 걸까요;;;

음.. 하기 인덱스를 건다고 해서 항상 빨라지는건 아닙니다. 데이터의 분포가 중요합니다.

어렇게 인덱스를 거시고 나서 vacuum analyze 해보셨나요? 아니면 vacuum full 을 한번 해보시는 것도 도움이 됩니다.

인덱스를

CREATE INDEX index_test_date ON test ((date_trunc('month', date)::date), id);

로 걸어보세요. 성능이 향상될 수도 있습니다. 역시 인덱스 거시고 위의 작업을 한번 해봐주세요.

그리고 쿼리문에서 count(id) 가 아니라 count(*) 로도 한번 바꿔보시고요. 별 차이 없을것 같지만;;

잘 되시면 explain analyze SELECT .... 의 결과를 한번 올려봐 주세요

ps. 아; 날자별로 검색할 필요가 없다면 그냥 id 컬럼에 인덱스만 걸어보세요.

으악. 함수 결과를 인덱스로 잡는 다는 것이

바로 이 말씀이셨군요.

CREATE INDEX index_test_date ON test ((date_trunc('month', date)::date));

다시 한번 pgsql에 홀딱 반하는 군요. *.*

고맙습니다. 신기배님