|
detect_log 총 로그 수 : 33082123(총 3천3백만개 정도)
detect_log_1201 : 1252413(총 1백만개 정도)
detect_log_1202 : 31829710(총 3천 1백만개 정도)
문제점
1. 두개의 테이블이 합쳐져서 select를 할 때 order by desc가 삭제되면 가져오는데 문제가 없습니다.
실험 TEST (postgresql \timing on 으로 시간 측정)
1. detect_log_1201에 쿼리
1) order by time desc 삭제 후 테스트 => 0.001619 sec
select * from detect_log_1201 where time >= '2012-01-01 00:00:00' and time < '2012-02-01 00:00:00' and client_ip = '10.0.0.23' offset 1000 limit 50;
2) order by time desc 존재 후 테스트 => 0.001855 sec
select * from detect_log_1201 where time >= '2012-01-01 00:00:00' and time < '2012-02-01 00:00:00' and client_ip = '10.0.0.23' order by time desc offset 1000 limit 50;
2. detect_log_1202에 쿼리
1) order by time desc 삭제 후 테스트 => 0.001338 sec
select * from detect_log_1202 where time >= '2012-02-01 00:00:00' and time < '2012-03-01 00:00:00' and client_ip = '10.0.0.23' offset 1000 limit 50;
2) order by time desc 존재 후 테스트 => 0.002126 sec
select * from detect_log_1202 where time >= '2012-02-01 00:00:00' and time < '2012-03-01 00:00:00' and client_ip = '10.0.0.23' order by time desc offset 1000 limit 50;
3. detect_log ==> 부모 테이블
1) order by time desc 삭제 후 테스트 => 0.003998 sec
select * from detect_log where time >= '2012-02-01 00:00:00' and time < '2012-03-01 00:00:00' and client_ip = '10.0.0.23' offset 1000 limit 50;
2) order by time desc 존재 후 테스트 => 104.599390 sec
select * from detect_log where time >= '2012-02-01 00:00:00' and time < '2012-03-01 00:00:00' and client_ip = '10.0.0.23' order by time desc offset 1000 limit 50;
부모테이블 explain 정보입니다.
order by time desc 안했을 경우
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------
Limit (cost=46.07..48.38 rows=50 width=707)
-> Result (cost=0.00..1466486.32 rows=31829711 width=707)
-> Append (cost=0.00..1466486.32 rows=31829711 width=707)
-> Seq Scan on detect_log (cost=0.00..11.40 rows=1 width=946)
Filter: (("time" >= '2012-02-01 00:00:00'::timestamp without time zone) AND ("time" < '2012-03-01 00:00:00'::timestamp without time zone) AND (client_ip = '
10.0.0.23'::inet))
-> Seq Scan on detect_log_1202 detect_log (cost=0.00..1466474.93 rows=31829710 width=707)
Filter: (("time" >= '2012-02-01 00:00:00'::timestamp without time zone) AND ("time" < '2012-03-01 00:00:00'::timestamp without time zone) AND (client_ip = '
10.0.0.23'::inet))
(7 rows)
order by time desc 했을 경우
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------
Limit (cost=3222879.91..3222880.03 rows=50 width=707)
-> Sort (cost=3222877.41..3302451.69 rows=31829711 width=707)
Sort Key: public.detect_log."time"
-> Result (cost=0.00..1466486.32 rows=31829711 width=707)
-> Append (cost=0.00..1466486.32 rows=31829711 width=707)
-> Seq Scan on detect_log (cost=0.00..11.40 rows=1 width=946)
Filter: (("time" >= '2012-02-01 00:00:00'::timestamp without time zone) AND ("time" < '2012-03-01 00:00:00'::timestamp without time zone) AND (client_
ip = '10.0.0.23'::inet))
-> Seq Scan on detect_log_1202 detect_log (cost=0.00..1466474.93 rows=31829710 width=707)
Filter: (("time" >= '2012-02-01 00:00:00'::timestamp without time zone) AND ("time" < '2012-03-01 00:00:00'::timestamp without time zone) AND (client_
ip = '10.0.0.23'::inet))
(9 rows)
여기서 sort가 되어서 그런걸로 문제점을 잡았는데 해결 방법이 보이지가 않습니다.
각각의 자식 테이블은 문제가 되지 않는데 부모테이블에서 소팅에 문제가 되는 이유가 무었일까요?
참고로 자식테이블의 index입니다.
"detect_log_1202_id" btree (id DESC)
"detect_log_1202_index_time" btree ("time" DESC)
"detect_log_1202_index_time_display" btree ("time" DESC, display DESC)
"detect_log_1202_index_time_display_action" btree ("time" DESC, display DESC, action DESC)
"detect_log_1202_index_time_display_client_ip" btree ("time" DESC, display DESC, client_ip DESC)
"detect_log_1202_index_time_display_client_ip_log_code" btree ("time" DESC, display DESC, client_ip DESC, log_code DESC)
"detect_log_1202_index_time_display_client_ip_url" btree ("time" DESC, display DESC, client_ip DESC, http_protocol DESC, host DESC, path DESC)
"detect_log_1202_index_time_display_country_code" btree ("time" DESC, display DESC, country_code DESC)
"detect_log_1202_index_time_display_log_code" btree ("time" DESC, display DESC, log_code DESC)
"detect_log_1202_index_time_display_log_code_client_ip_url" btree ("time" DESC, display DESC, log_code DESC, client_ip DESC, http_protocol DESC, host DESC, path DESC)
"detect_log_1202_index_time_display_log_code_url" btree ("time" DESC, display DESC, log_code DESC, http_protocol DESC, host DESC, path DESC)
"detect_log_1202_index_time_display_server_ip" btree ("time" DESC, display DESC, server_ip DESC)
"detect_log_1202_index_time_display_server_ip_log_code" btree ("time" DESC, display DESC, server_ip DESC, log_code DESC)
"detect_log_1202_index_time_display_severity" btree ("time" DESC, display DESC, severity DESC)
"detect_log_1202_index_time_display_url" btree ("time" DESC, display DESC, http_protocol DESC, host DESC, path DESC)
"detect_log_1202_index_time_log_code" btree ("time" DESC, log_code DESC)
Check constraints:
"detect_log_1202_time_check" CHECK ("time" >= '2012-02-01 00:00:00'::timestamp without time zone AND "time" < '2012-03-01 00:00:00'::timestamp without time zone)
Inherits: detect_log
긴글 읽어주셔서 감사합니다.
|
