yourjin blog

노크인 양방향 차단 여부 확인 쿼리 실행계획

10 min read

1. 게시물만 조회

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rb1_0.created_at
from roommate_board rb1_0
where rb1_0.is_deleted = false
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

2. 방형태, 지역 JOIN

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
where rb1_0.is_deleted = false
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

3. 키워드 검색 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

4. member JOIN 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

5. ACTIVE EXISTS 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

6. 입주일 조건 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
and (
    rb1_0.comeable_date_negotiable = true
    or rb1_0.comeable_date >= '2026-09-30 01:39:04.6046101'
)
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

7. 차단 NOT EXISTS 추가

여기서 병목 발생, 평균 5ms → 400ms로 급증했다.
BLOCK 테이블에서 풀스캔을 돌고 있었다.

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
and (
    rb1_0.comeable_date_negotiable = true
    or rb1_0.comeable_date >= '2026-09-30 01:39:04.6046101'
)
and not exists (
    select 1
    from block b1_0
    where b1_0.is_deleted = false
      and (
          (
              b1_0.blocker_id = 5
              and b1_0.blocked_id = rb1_0.member_id
          )
          or
          (
              b1_0.blocker_id = rb1_0.member_id
              and b1_0.blocked_id = 5
          )
      )
)
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

8. 최신 basic_information 조회 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    bi1_0.name,
    bi1_0.birth,
    bi1_0.gender,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
left join basic_information bi1_0
    on bi1_0.member_id = m1_0.id
   and bi1_0.id = (
       select max(bi2_0.id)
       from basic_information bi2_0
       where bi2_0.member_id = m1_0.id
   )
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
and (
    rb1_0.comeable_date_negotiable = true
    or rb1_0.comeable_date >= '2026-09-30 01:39:04.6046101'
)
and not exists (
    select 1
    from block b1_0
    where b1_0.is_deleted = false
      and (
          (
              b1_0.blocker_id = 5
              and b1_0.blocked_id = rb1_0.member_id
          )
          or
          (
              b1_0.blocker_id = rb1_0.member_id
              and b1_0.blocked_id = 5
          )
      )
)
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

9. basic_information_file까지 추가

EXPLAIN ANALYZE
select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    bi1_0.name,
    bi1_0.birth,
    bi1_0.gender,
    rb1_0.created_at
from roommate_board rb1_0
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
join region r1_0
    on r1_0.id = rb1_0.region_id
left join region p1_0
    on p1_0.id = r1_0.parents_id
left join region p2_0
    on p2_0.id = p1_0.parents_id
join member m1_0
    on m1_0.id = rb1_0.member_id
left join basic_information bi1_0
    on bi1_0.member_id = m1_0.id
   and bi1_0.id = (
       select max(bi2_0.id)
       from basic_information bi2_0
       where bi2_0.member_id = m1_0.id
   )
left join basic_information_file bif1_0
    on bif1_0.id = (
        select max(bif2_0.id)
        from basic_information_file bif2_0
        where bif2_0.basic_information_id = bi1_0.id
    )
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
and rb1_0.is_deleted = false
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
and (
    rb1_0.comeable_date_negotiable = true
    or rb1_0.comeable_date >= '2026-09-30 01:39:04.6046101'
)
and not exists (
    select 1
    from block b1_0
    where b1_0.is_deleted = false
      and (
          (
              b1_0.blocker_id = 5
              and b1_0.blocked_id = rb1_0.member_id
          )
          or
          (
              b1_0.blocker_id = rb1_0.member_id
              and b1_0.blocked_id = 5
          )
      )
)
order by
    rb1_0.created_at desc,
    rb1_0.id desc
offset 0 rows
fetch first 21 rows only;

최종 SQL 쿼리

46, 52, 54, 40, 40

select
    rb1_0.id,
    rb1_0.title,
    rb1_0.deposit,
    rb1_0.monthly_rent,
    rb1_0.management_cost,
    rb1_0.comeable_date,
    rb1_0.hits,
    rt1_0.name,
    r1_0.name,
    p1_0.name,
    p2_0.name,
    m1_0.id,
    bi1_0.name,
    f1_0.saved_file_name,
    bi1_0.birth,
    bi1_0.gender,
    rb1_0.created_at
from roommate_board rb1_0
 
join room_type rt1_0
    on rt1_0.id = rb1_0.room_type_id
 
join region r1_0
    on r1_0.id = rb1_0.region_id
 
left join region p1_0
    on p1_0.id = r1_0.parents_id
 
left join region p2_0
    on p2_0.id = p1_0.parents_id
 
join member m1_0
    on m1_0.id = rb1_0.member_id
 
left join basic_information bi1_0
    on bi1_0.member_id = m1_0.id
   and bi1_0.id = (
        select max(bi2_0.id)
        from basic_information bi2_0
        where bi2_0.member_id = m1_0.id
   )
 
left join basic_information_file bif1_0
    on bif1_0.id = (
        select max(bif2_0.id)
        from basic_information_file bif2_0
        where bif2_0.basic_information_id = bi1_0.id
   )
 
left join file f1_0
    on f1_0.id = bif1_0.file_id
   and f1_0.is_deleted = false
 
where (
       lower(rb1_0.title) like '%부하 테스트 게시글%' escape '!'
    or lower(rt1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(r1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p1_0.name) like '%부하 테스트 게시글%' escape '!'
    or lower(p2_0.name) like '%부하 테스트 게시글%' escape '!'
)
 
and rb1_0.is_deleted = false
 
and exists (
    select 1
    from state s1_0
    where s1_0.member_id = rb1_0.member_id
      and s1_0.state = 'ACTIVE'
)
 
and (
       rb1_0.comeable_date_negotiable = true
    or rb1_0.comeable_date >= '2026-09-30 18:51:48.1908491'
)
 
-- 상대방이 나(5)를 차단했는지
and not exists (
    select 1
    from block b1_0
    where b1_0.is_deleted = false
      and b1_0.blocked_id = 5
      and b1_0.blocker_id = rb1_0.member_id
)
 
-- 나(5)가 상대방을 차단했는지
and not exists (
    select 1
    from block b4_0
    where b4_0.is_deleted = false
      and b4_0.blocker_id = 5
      and b4_0.blocked_id = rb1_0.member_id
)
 
order by
    rb1_0.created_at desc,
    rb1_0.id desc
 
offset 0 rows
fetch first 21 rows only;

Graph View

Backlinks