Fedora Core3 에 PostgreSQL beta5를 사용중입니다.

데이터가 약 6백만건정도 있는 테이블에서 select 시 인덱스를 타게 조건을 주고

limit xxx offset yyy 를 주고 쿼리를 던졌습니다.

그런데 offset값이 적을 경우는 금방 결과를 가져오는데 offset 값이 커질수록 점점 느려지더니

나중에는 테이블을 seq scan한 경우보다 결과를 느리게 가져오더군요..

쩝. 이게 무슨 문제인지 아니면 제가 뭔가를 잘못알고있는건지 ...

테이블은 시간별 발생하는 로그를 쌓는 테이블이라 update, delete는 거의 발생하지않고

대부분 insert와 select만 있습니다.

테이블 정보는 다음과 같습니다.

test=> \d test_tab
Column | Type |

col1 | character varying(20) |
col2 | character varying(20) |
col3 | character varying(20) |
col4 | character varying(20) |
col5 | character varying(20) |
col6 | timestamp without time zone |
col7 | character varying(256) |
col8 | timestamp without time zone |
col9 | numeric |
col10 | numeric |
col11 | numeric |
col12 | timestamp without time zone |
col13 | timestamp without time zone |
col14 | character varying(128) |
col15 | character varying(10) |
col16 | character varying(20) |
col17 | character varying(20) |
Indexes:
"test_idx" btree (col8, col4)

우선 인덱스를 타게 조건을 주고 offset 값을 적게 준 경우입니다.

test => explain ANALYZE select * from test_tab where col8 >= '2003-12-01 00:00:00'::timestamp and col8 <= '2003-12-31 23:59:59'::timestamp limit 25 offset 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..99.88 rows=25 width=538) (actual time=107.588..108.672 rows=25 loops=1)
-> Index Scan using test_idx on test_tab (cost=0.00..113558.62 rows=28423 width=538) (actual time=107.576..108.520 rows=25 loops=1)
Index Cond: ((col8>= '2003-12-01 00:00:00'::timestamp without time zone) AND (col8<= '2003-12-31 23:59:59'::timestamp without time zone))
Total runtime: 108.884 ms
(4 rows)

Time: 110.783 ms

다음은 인덱스를 타게 조건을 주고 offset 값을 크게 준 경우입니다.

lgsec=> explain ANALYZE select * from test_tab where col8 >= '2003-12-01 00:00:00'::timestamp and col8 <= '2003-12-31 23:59:59'::timestamp limit 25 offset 1000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=113558.62..113558.62 rows=1 width=538) (actual time=108988.257..109004.914 rows=25 loops=1)
-> Index Scan using test_idx on test_tab (cost=0.00..113558.62 rows=28423 width=538) (actual time=0.054..106409.983 rows=1000025 loops=1)
Index Cond: ((col8>= '2003-12-01 00:00:00'::timestamp without time zone) AND (col8 <= '2003-12-31 23:59:59'::timestamp without time zone))
Total runtime: 109005.121 ms
(4 rows)

Time: 109007.054 ms

다음은 인덱스를 안타게하고 offset 값을 크게 준 경우입니다.

test=> explain ANALYZE select * from test_tab where col8::text >= '2003-12-01 00:00:00' and col8::text <= '2003-12-31 23:59:59' limit 25 offset 1000000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=269863.62..269863.62 rows=1 width=538) (actual time=44063.355..44063.974 rows=25 loops=1)
-> Seq Scan on test_Tab (cost=0.00..269863.62 rows=28423 width=538) (actual time=22.532..41591.367 rows=1000025 loops=1)
Filter: (((col8)::text >= '2003-12-01 00:00:00'::text) AND ((col8)::text <= '2003-12-31 23:59:59'::text))
Total runtime: 44064.164 ms
(4 rows)

Time: 44065.987 ms

결과를 보시면 offset이 큰경우 index scan 보다 seq scan이 cost는 크지만 결과는 훨씬 빨리 나오네요..
휴~~ 데이터베이스는 정말 오묘하네요. OTZ

그럼 감사합니다.