audit_time에 인덱스를 주었구요
나머지 필드에서 복합 인덱스로(4~5)정도로 2개 주었습니다.
왜 union order by가 있을 경우에만 속도가 늦어 질까요?
하나의 테이블만 하면 괜찮습니다.
아래는 쿼리 실행 결과 입니다.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
쿼리문 order by를 줬을 경우: explain analyze select a.audit_time
from (
select *
from audit_log_2008071718
where audit_time >= '2008-07-01 00:00:00' and
audit_time <= '2008-07-30 24:00:00'
union all
select *
from audit_log_2008071717
where audit_time >= '2008-07-01 00:00:00' and
audit_time <= '2008-07-30 24:00:00'
) as a
order by a.audit_time limit 200;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=167213.09..167213.59 rows=200 width=8) (actual time=98810.181..98810.514 rows=200 loops=1)
-> Sort (cost=167213.09..167361.26 rows=59266 width=8) (actual time=98810.179..98810.288 rows=200 loops=1)
Sort Key: a.audit_time
-> Result (cost=833.33..162514.80 rows=59266 width=8) (actual time=5746.875..82797.287 rows=11853199 loops=1)
-> Append (cost=833.33..162514.80 rows=59266 width=8) (actual time=5746.873..68909.234 rows=11853199 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=833.33..106970.15 rows=39046 width=8) (actual time=5746.872..37592.558 rows=7809272 loops=1)
-> Bitmap Heap Scan on audit_log_2008071718 (cost=833.33..106579.69 rows=39046 width=648) (actual time=5746.868..27718.774 rows=7809272 loops=1)
Recheck Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on time_20080718 (cost=0.00..823.57 rows=39046 width=0) (actual time=5596.823..5596.823 rows=7809272 loops=1)
Index Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
-> Subquery Scan "*SELECT* 2" (cost=431.95..55544.66 rows=20220 width=8) (actual time=1652.335..18149.394 rows=4043927 loops=1)
-> Bitmap Heap Scan on audit_log_2008071717 (cost=431.95..55342.46 rows=20220 width=648) (actual time=1652.331..13091.470 rows=4043927 loops=1)
Recheck Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on time_2008071717 (cost=0.00..426.89 rows=20220 width=0) (actual time=1512.550..1512.550 rows=4043927 loops=1)
Index Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
Total runtime: 98863.019 ms
(16 rows)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
쿼리문 order by 를 주지 않았을 경우
explain analyze select a.audit_time
from (
select *
from audit_log_2008071718
where audit_time >= '2008-07-01 00:00:00' and
audit_time <= '2008-07-30 24:00:00'
union all
select *
from audit_log_2008071717
where audit_time >= '2008-07-01 00:00:00' and
audit_time <= '2008-07-30 24:00:00'
) as a
limit 200;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..766.27 rows=200 width=8) (actual time=45.695..70.891 rows=200 loops=1)
-> Result (cost=0.00..227067.78 rows=59266 width=8) (actual time=45.692..70.670 rows=200 loops=1)
-> Append (cost=0.00..227067.78 rows=59266 width=8) (actual time=45.690..70.439 rows=200 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..150668.49 rows=39046 width=8) (actual time=45.689..70.222 rows=200 loops=1)
-> Index Scan using time_20080718 on audit_log_2008071718 (cost=0.00..150278.03 rows=39046 width=648) (actual time=45.686..69.974 rows=200 loops=1)
Index Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
-> Subquery Scan "*SELECT* 2" (cost=0.00..76399.29 rows=20220 width=8) (never executed)
-> Index Scan using time_2008071717 on audit_log_2008071717 (cost=0.00..76197.09 rows=20220 width=648) (never executed)
Index Cond: ((audit_time >= '2008-07-01 00:00:00'::timestamp without time zone) AND (audit_time <= '2008-07-31 00:00:00'::timestamp without time zone))
Total runtime: 71.083 ms
(10 rows)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|