[쿼리1]
select * from log where _date >= '20050901' and _date <=
'20050923' and to_addr ~* 'xxxxx' order
by _date limit 80 offset 0
[실행내역]
Limit (cost=0.00..4.31 rows=1 width=257) (actual
time=394.491..395.844 rows=80 loops=1)
-> Index Scan using log_idx_4 on log (cost=0.00..4.31 rows=1
width=257) (actual time=394.485..395.733 rows=80 loops=1)
Index Cond: (((_date)::text >= '20050901'::text) AND
((_date)::text <= '20050923'::text))
Filter: ((to_addr)::text ~* 'xxxxx'::text)
Total runtime: 395.972
ms ------------------------------------------------------------------------------------------
[쿼리2]
select * from log where _date >= '20050901' and _date <=
'20050923' and to_addr ~* 'xxxxx' order by
_date,time limit 80 offset 0
[실행내역]
Limit (cost=4.32..4.33 rows=1 width=257) (actual
time=259634.412..259634.563 rows=80 loops=1)
-> Sort (cost=4.32..4.33 rows=1 width=257) (actual
time=259634.406..259634.458 rows=80 loops=1)
Sort Key: _date, "time"
-> Index Scan using log_idx_4 on log (cost=0.00..4.31 rows=1
width=257) (actual time=387.380..259552.765 rows=7473
loops=1)
Index Cond: (((_date)::text >= '20050901'::text) AND
((_date)::text <= '20050923'::text))
Filter: ((to_addr)::text ~* 'xxxxx'::text)
Total runtime: 259649.920 ms
쿼리1/쿼리2하고 다른점은 order by 에서 쿼리1은 _date만 있고,
쿼리2는 _date,time 이 있습니다.
어쩔수 없이 _date,time을 해야 하는지라.. 고수님들 답변
부탁드립니다.
수고하세요
|