|
검색 순서를 제가 원하는 방향으로 가고 싶은데 그리 싶지가 않네요.
방법이 없을까요?
다음에 두가지 쿼리가 있습니다.
explain select a.accountnid, a.accountsid, a.charnid, a.charsid, scharnid, tcharnid, tcharacter.charsid, date_info
from
((select account.accountnid, account.accountsid, character.charnid, character.charsid
from account, character
where charsid = 'I천둥I' and account.accountnid = character.accountnid) as character
join itemlog on
character.charnid = itemlog.scharnid or character.charnid = itemlog.tcharnid) as a
join action on
a.actionid = action.actionid
left join character as tcharacter
on tcharacter.charnid = a.tcharnid
where date_info between 70330000000 and 70331000000
explain select a.accountnid, a.accountsid, a.charnid, a.charsid, scharnid, tcharnid, tcharacter.charsid, date_info
from
((select account.accountnid, account.accountsid, character.charnid, character.charsid
from account, character
where charsid = 'I천둥I' and account.accountnid = character.accountnid) as character
join itemlog on
character.charnid = itemlog.scharnid or character.charnid = itemlog.tcharnid) as a
join action on
a.actionid = action.actionid
left join character as tcharacter
on tcharacter.charnid = a.tcharnid
두 쿼리의 차이는 마지막 where 절인데요.
첫번째 쿼리의 explain은
"Nested Loop Left Join (cost=121591.61..370236.15 rows=252 width=282)"
" -> Nested Loop (cost=121591.61..368563.05 rows=252 width=184)"
" Join Filter: ((public."character".charnid = itemlog.scharnid) OR (public."character".charnid = itemlog.tcharnid))"
" -> Hash Join (cost=120088.71..303553.87 rows=25151 width=16)"
" Hash Cond: (itemlog.actionid = "action".actionid)"
" -> Seq Scan on itemlog (cost=0.00..92663.17 rows=5030317 width=16)"
" -> Hash (cost=119586.38..119586.38 rows=27307 width=16)"
" -> Seq Scan on "action" (cost=0.00..119586.38 rows=27307 width=16)"
" Filter: ((date_info >= 70330000000::bigint) AND (date_info <= 70331000000::bigint))"
" -> Materialize (cost=1502.90..1503.91 rows=101 width=168)"
" -> Hash Join (cost=991.11..1502.80 rows=101 width=168)"
" Hash Cond: (account.accountnid = public."character".accountnid)"
" -> Seq Scan on account (cost=0.00..276.87 rows=15587 width=66)"
" -> Hash (cost=989.85..989.85 rows=101 width=110)"
" -> Seq Scan on "character" (cost=0.00..989.85 rows=101 width=110)"
" Filter: ((charsid)::text = 'I천둥I'::text)"
" -> Index Scan using pk_user on "character" tcharacter (cost=0.00..6.63 rows=1 width=102)"
" Index Cond: (tcharacter.charnid = itemlog.tcharnid)"
두번째 쿼리의 explain은
"Hash Left Join (cost=194940.70..4728172.47 rows=50432 width=282)"
" Hash Cond: (itemlog.tcharnid = tcharacter.charnid)"
" -> Hash Join (cost=193414.37..4722705.54 rows=50432 width=184)"
" Hash Cond: (itemlog.actionid = "action".actionid)"
" -> Nested Loop (cost=866.31..4494550.88 rows=50432 width=184)"
" -> Nested Loop (cost=0.00..1678.37 rows=101 width=168)"
" -> Seq Scan on "character" (cost=0.00..989.85 rows=101 width=110)"
" Filter: ((charsid)::text = 'I천둥I'::text)"
" -> Index Scan using pk_account on account (cost=0.00..6.80 rows=1 width=66)"
" Index Cond: (account.accountnid = public."character".accountnid)"
" -> Bitmap Heap Scan on itemlog (cost=866.31..43729.34 rows=50303 width=16)"
" Recheck Cond: ((public."character".charnid = itemlog.scharnid) OR (public."character".charnid = itemlog.tcharnid))"
" -> BitmapOr (cost=866.31..866.31 rows=50303 width=0)"
" -> Bitmap Index Scan on idx_itemlog_scharnid (cost=0.00..426.87 rows=25152 width=0)"
" Index Cond: (public."character".charnid = itemlog.scharnid)"
" -> Bitmap Index Scan on idx_itemlog_tcharnid (cost=0.00..426.87 rows=25152 width=0)"
" Index Cond: (public."character".charnid = itemlog.tcharnid)"
" -> Hash (cost=92279.25..92279.25 rows=5461425 width=16)"
" -> Seq Scan on "action" (cost=0.00..92279.25 rows=5461425 width=16)"
" -> Hash (cost=939.48..939.48 rows=20148 width=102)"
" -> Seq Scan on "character" tcharacter (cost=0.00..939.48 rows=20148 width=102)"
이렇게 나옵니다. where 조건절은 특정 시간을 표시하는거지만 동일 시간대에 많은 데이터가 쌓이기 때문에 첫번째 explain은 옳바른 방법이 아니라고 생각합니다.
그래서 첫번째 쿼리와 같은 결과를 유도하면서 두번째 explain을 나오게 하고 싶습니다.
temp 테이블을 써서 where 조건절만 나중에 할까도 생각했지만 썩 좋은 방법 같지는 않아서요.
쿼리 한번으로 해결할수 있는 좋은 방법이 없을까요?
select * from (...) as a where date_info between 70330000000 and 70331000000
이렇게도 해보고 join도 써보고 별 방법을 다 써봤지만 날짜 조건이 들어가면 그것부터 검색하게 되네요.
어떻게 하면 좋을까요?
좋은 정보를 주시면 감사하겠습니다. 부탁드립니다.
|
