Ich bin rati

ImHere 프로젝트

Avoid Unnecessary Implicit Joins when using QueryDSL

by rati

Background

I got advice from gemini3 when I was developing a feature. That feature allows users to accept or reject friend requests.

Gemini3 recommended using ID when querying friend candidates. But from the adapter layer to the application layer, the using user email is an easier method. ( I love @AuthenticationPrincipal from Spring Framework !)

So I decided to compare the performance between using email and using id when querying that.

Test

Target Method

@Repository
class SpringQueryDSLUserRepository(private val queryFactory: JPAQueryFactory) {

    private val user = QUserJpaEntity.userJpaEntity

    // 1. Email-based (Nested subquery - relatively slow)
    fun searchByEmail(userEmail: String, keyword: String): List<UserJpaEntity> {
        return queryFactory.selectFrom(user)
            .where(
                isNicknameMatching(keyword).or(isEmailMatching(keyword)),
                isActive(),
                user.email.ne(userEmail),
                isNotAlreadyFriendEmail(userEmail),
                isNotUnderRequestEmail(userEmail),
                isNotRestrictedEmail(userEmail)
            ).fetch()
    }

    // 2. ID-based (Using constant - relatively fast)
    fun searchById(currentUserId: UUID, keyword: String): List<UserJpaEntity> {
        return queryFactory.selectFrom(user)
            .where(
                isNicknameMatching(keyword).or(isEmailMatching(keyword)),
                isActive(),
                user.id.ne(currentUserId),
                // Directly pass the already extracted ID (constant)
                isNotAlreadyFriend(currentUserId),
                isNotUnderRequest(currentUserId),
                isNotRestricted(currentUserId)
            ).fetch()
    }

    // Email-based (for subquery)
    private fun isNotAlreadyFriendEmail(userEmail: String): BooleanExpression {
        val relationships = QFriendRelationshipsJpaEntity.friendRelationshipsJpaEntity
        return user.email.notIn(
            JPAExpressions
                .select(relationships.friendUser.email)
                .from(relationships)
                .where(
                    relationships.ownerUser.email.eq(userEmail)
                )
        )
    }

    // ID-based (for constant)
    private fun isNotAlreadyFriend(currentUserId: UUID): BooleanExpression {
        val relationships = QFriendRelationshipsJpaEntity.friendRelationshipsJpaEntity
        return user.id.notIn(
            JPAExpressions.select(relationships.friendUser.id)
                .from(relationships)
                .where(relationships.ownerUser.id.eq(currentUserId))
        )
    }

    // Email-based (for subquery)
    private fun isNotUnderRequestEmail(userEmail: String): BooleanExpression {
        val requests = QFriendRequestJpaEntity.friendRequestJpaEntity
        return user.email.notIn(
            JPAExpressions.select(requests.receiver.email).from(requests)
                .where(requests.requester.email.eq(userEmail))
        ).and(
            user.email.notIn(
                JPAExpressions.select(requests.requester.email).from(requests)
                    .where(requests.receiver.email.eq(userEmail))
            )
        )
    }

    // ID-based (for constant)
    private fun isNotUnderRequest(currentUserId: UUID): BooleanExpression {
        val requests = QFriendRequestJpaEntity.friendRequestJpaEntity
        return user.id.notIn(
            JPAExpressions.select(requests.receiver.id).from(requests).where(requests.requester.id.eq(currentUserId))
        ).and(
            user.id.notIn(
                JPAExpressions.select(requests.requester.id).from(requests)
                    .where(requests.receiver.id.eq(currentUserId))
            )
        )
    }

    // Email-based (for subquery)
    private fun isNotRestrictedEmail(userEmail: String): BooleanExpression {
        val restrictions = QFriendRestrictionJpaEntity.friendRestrictionJpaEntity
        return user.email.notIn(
            JPAExpressions.select(restrictions.target.email).from(restrictions)
                .where(restrictions.actor.email.eq(userEmail))
        ).and(
            user.email.notIn(
                JPAExpressions.select(restrictions.actor.email).from(restrictions)
                    .where(restrictions.target.email.eq(userEmail))
            )
        )
    }

    // ID-based (for constant)
    private fun isNotRestricted(currentUserId: UUID): BooleanExpression {
        val restrictions = QFriendRestrictionJpaEntity.friendRestrictionJpaEntity
        return user.id.notIn(
            JPAExpressions.select(restrictions.target.id).from(restrictions)
                .where(restrictions.actor.id.eq(currentUserId))
        ).and(
            user.id.notIn(
                JPAExpressions.select(restrictions.actor.id).from(restrictions)
                    .where(restrictions.target.id.eq(currentUserId))
            )
        )
    }

    private fun isIdMatching(id: UUID): BooleanExpression = user.id.eq(id)

    private fun isEmailMatching(keyword: String): BooleanExpression = user.email.eq(keyword)

    private fun isNicknameMatching(keyword: String): BooleanExpression = user.nickname.eq(keyword)

    private fun isActive(): BooleanExpression = user.status.eq(UserStatus.ACTIVE)

}s.ACTIVE)

}

Test Logic

@SpringBootTest
@Transactional
class PerformanceComparisonTest {

    @Autowired
    lateinit var userRepository: SpringDataUserRepository

    @Autowired
    lateinit var userRepositoryImpl: SpringQueryDSLUserRepository

    private lateinit var targetUserEmail: String
    private lateinit var targetUserId: UUID

    @BeforeEach
    fun setUp() {
        val me = userRepository.save(
            UserJpaEntity(
                "me@test.com", "나", UserRole.NORMAL,
                OAuth2Provider.KAKAO, UserStatus.ACTIVE
            )
        )
        targetUserEmail = me.email
        targetUserId = me.id!!

        (1..100_000).forEach { i ->
            userRepository.save(
                UserJpaEntity(
                    "user$i@test.com",
                    "친구$i",
                    UserRole.NORMAL,
                    OAuth2Provider.KAKAO,
                    UserStatus.ACTIVE
                )
            )
        }
    }

    @Test
    @DisplayName("Email 기반 vs ID 기반 검색 성능 비교")
    fun comparePerformance() {
        // given
        val keyword = "친구"
        val stopWatch = org.springframework.util.StopWatch()

        // when
        stopWatch.start("Email Based (Subquery)")
        val result1 = userRepositoryImpl.searchByEmail(targetUserEmail, keyword)
        stopWatch.stop()

        stopWatch.start("ID Based (Constant)")
        val currentUserId = userRepository.findByEmail(targetUserEmail)?.id!!
        val result2 = userRepositoryImpl.searchById(currentUserId, keyword)
        stopWatch.stop()

        println(stopWatch.prettyPrint())

        // then
        assertThat(result1.size).isEqualTo(result2.size)

        val emailBasedTime = stopWatch.taskInfo[0].timeMillis
        val idBasedTime = stopWatch.taskInfo[1].timeMillis

        println("Email Based: ${emailBasedTime}ms")
        println("ID Based: ${idBasedTime}ms")

        assertThat(idBasedTime).isLessThanOrEqualTo(emailBasedTime)
    }
  }
}

Test Result

================================================================================
[Query 1] Email-Based Search (took 130ms)
================================================================================

2026-02-13T21:58:59.157+09:00  INFO 53924 --- [IMHERE-USER-SERVICE] [    Test worker] p6spy
#1770987539157 | took 130ms | statement | connection 4| url jdbc:h2:mem:1cea8f7b-11ce-42b1-ab96-e9c9bde72501

-- Parameterized Query:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where (uje1_0.nickname=? or uje1_0.email=?) 
  and uje1_0.status=? 
  and uje1_0.email<>? 
  and uje1_0.email not in (
    select fu1_0.email 
    from friend_relationships frje1_0 
    join users fu1_0 on fu1_0.id=frje1_0.friend_user_id 
    join users ou1_0 on ou1_0.id=frje1_0.owner_user_id 
    where ou1_0.email=?
  ) 
  and (uje1_0.email not in (
    select r1_0.email 
    from friend_request frje2_0 
    join users r1_0 on r1_0.id=frje2_0.receiver_id 
    join users r2_0 on r2_0.id=frje2_0.requester_id 
    where r2_0.email=?
  ) and uje1_0.email not in (
    select r3_0.email 
    from friend_request frje3_0 
    join users r3_0 on r3_0.id=frje3_0.requester_id 
    join users r4_0 on r4_0.id=frje3_0.receiver_id 
    where r4_0.email=?
  )) 
  and (uje1_0.email not in (
    select t1_0.email 
    from friend_restrictions frje4_0 
    join users t1_0 on t1_0.id=frje4_0.target_id 
    join users a1_0 on a1_0.id=frje4_0.actor_id 
    where a1_0.email=?
  ) and uje1_0.email not in (
    select a2_0.email 
    from friend_restrictions frje5_0 
    join users a2_0 on a2_0.id=frje5_0.actor_id 
    join users t2_0 on t2_0.id=frje5_0.target_id 
    where t2_0.email=?
  ))

-- Actual Values:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where (uje1_0.nickname='친구' or uje1_0.email='친구') 
  and uje1_0.status='ACTIVE' 
  and uje1_0.email<>'me@test.com' 
  and uje1_0.email not in (
    select fu1_0.email 
    from friend_relationships frje1_0 
    join users fu1_0 on fu1_0.id=frje1_0.friend_user_id 
    join users ou1_0 on ou1_0.id=frje1_0.owner_user_id 
    where ou1_0.email='me@test.com'
  ) 
  and (uje1_0.email not in (
    select r1_0.email 
    from friend_request frje2_0 
    join users r1_0 on r1_0.id=frje2_0.receiver_id 
    join users r2_0 on r2_0.id=frje2_0.requester_id 
    where r2_0.email='me@test.com'
  ) and uje1_0.email not in (
    select r3_0.email 
    from friend_request frje3_0 
    join users r3_0 on r3_0.id=frje3_0.requester_id 
    join users r4_0 on r4_0.id=frje3_0.receiver_id 
    where r4_0.email='me@test.com'
  )) 
  and (uje1_0.email not in (
    select t1_0.email 
    from friend_restrictions frje4_0 
    join users t1_0 on t1_0.id=frje4_0.target_id 
    join users a1_0 on a1_0.id=frje4_0.actor_id 
    where a1_0.email='me@test.com'
  ) and uje1_0.email not in (
    select a2_0.email 
    from friend_restrictions frje5_0 
    join users a2_0 on a2_0.id=frje5_0.actor_id 
    join users t2_0 on t2_0.id=frje5_0.target_id 
    where t2_0.email='me@test.com'
  ))

================================================================================
[Query 2] User Lookup (took 0ms)
================================================================================

2026-02-13T21:58:59.280+09:00  INFO 53924 --- [IMHERE-USER-SERVICE] [    Test worker] p6spy
#1770987539280 | took 0ms | statement | connection 4| url jdbc:h2:mem:1cea8f7b-11ce-42b1-ab96-e9c9bde72501

-- Parameterized Query:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where uje1_0.email=?

-- Actual Values:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where uje1_0.email='me@test.com'

================================================================================
[Query 3] ID-Based Search (took 38ms)
================================================================================

2026-02-13T21:58:59.390+09:00  INFO 53924 --- [IMHERE-USER-SERVICE] [    Test worker] p6spy
#1770987539390 | took 38ms | statement | connection 4| url jdbc:h2:mem:1cea8f7b-11ce-42b1-ab96-e9c9bde72501

-- Parameterized Query:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where (uje1_0.nickname=? or uje1_0.email=?) 
  and uje1_0.status=? 
  and uje1_0.id<>? 
  and uje1_0.id not in (
    select frje1_0.friend_user_id 
    from friend_relationships frje1_0 
    where frje1_0.owner_user_id=?
  ) 
  and (uje1_0.id not in (
    select frje2_0.receiver_id 
    from friend_request frje2_0 
    where frje2_0.requester_id=?
  ) and uje1_0.id not in (
    select frje3_0.requester_id 
    from friend_request frje3_0 
    where frje3_0.receiver_id=?
  )) 
  and (uje1_0.id not in (
    select frje4_0.target_id 
    from friend_restrictions frje4_0 
    where frje4_0.actor_id=?
  ) and uje1_0.id not in (
    select frje5_0.actor_id 
    from friend_restrictions frje5_0 
    where frje5_0.target_id=?
  ))

-- Actual Values:
select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where (uje1_0.nickname='친구' or uje1_0.email='친구') 
  and uje1_0.status='ACTIVE' 
  and uje1_0.id<>'abdfed91-cadb-480d-b2c1-7b592f57de1a' 
  and uje1_0.id not in (
    select frje1_0.friend_user_id 
    from friend_relationships frje1_0 
    where frje1_0.owner_user_id='abdfed91-cadb-480d-b2c1-7b592f57de1a'
  ) 
  and (uje1_0.id not in (
    select frje2_0.receiver_id 
    from friend_request frje2_0 
    where frje2_0.requester_id='abdfed91-cadb-480d-b2c1-7b592f57de1a'
  ) and uje1_0.id not in (
    select frje3_0.requester_id 
    from friend_request frje3_0 
    where frje3_0.receiver_id='abdfed91-cadb-480d-b2c1-7b592f57de1a'
  )) 
  and (uje1_0.id not in (
    select frje4_0.target_id 
    from friend_restrictions frje4_0 
    where frje4_0.actor_id='abdfed91-cadb-480d-b2c1-7b592f57de1a'
  ) and uje1_0.id not in (
    select frje5_0.actor_id 
    from friend_restrictions frje5_0 
    where frje5_0.target_id='abdfed91-cadb-480d-b2c1-7b592f57de1a'
  ))

================================================================================
Performance Results
================================================================================

StopWatch '': 9.4650058 seconds
----------------------------------------
Seconds       %       Task name
----------------------------------------
9.2751745     98%     Email Based (Subquery)
0.1898313     02%     ID Based (Constant)

Email Based: 9275ms
ID Based: 189ms

Summary

  • Email-based query
    • 9,275ms (98% of total time)
    • Uses subqueries with multiple JOIN operations on the users table, resulting in nested loop joins
  • ID-based query
    • 189ms (2% of total time)
    • Uses direct ID constants in subqueries, eliminating unnecessary table joins
  • Root cause
    • The email-based approach requires email information to compare entities, which causes multiple joins to resolve email-to-ID mappings. Additionally, each subquery operates independently without sharing memory or intermediate results.
    • But ID-based approach don’t have to multiple join to resolve email.

To understand this case, I have to be reminded about database.

Database

Required Knowledge 1: How Database Engines Find Targets

Index Seek

Databases jump immediately using indexes. We call this index seek.

Then, how?

B-Tree Architecture

erDiagram
    INTERNAL_PAGE ||--o{ LEAF_PAGE_P00 : "Seek Path"
    INTERNAL_PAGE ||--o{ LEAF_PAGE_P01 : "Seek Path"
    INTERNAL_PAGE ||--o{ LEAF_PAGE_P02 : "Seek Path"
    INTERNAL_PAGE ||--o{ LEAF_PAGE_P10 : "Seek Path"
    INTERNAL_PAGE ||--o{ LEAF_PAGE_P11 : "Seek Path"

    %% Horizontal Scan Path (Linked List)
0    LEAF_PAGE_P00 ||--|| LEAF_PAGE_P1 : "Next"
    LEAF_PAGE_P01 ||--|| LEAF_PAGE_P02 : "Next"
    LEAF_PAGE_P02 ||--|| LEAF_PAGE_P10 : "Next"
    LEAF_PAGE_P10 ||--|| LEAF_PAGE_P11 : "Next"

    LEAF_PAGE_P00 {
        string keys "1 ~ 100"
        pointer_to_data row_1_to_100 "RID/PK"
    }
    LEAF_PAGE_P01 {
        string keys "101 ~ 200"
        pointer_to_data row_101_to_200 "RID/PK"
    }
    LEAF_PAGE_P02 {
        string keys "201 ~ 500"
        pointer_to_data row_201_to_500 "RID/PK"
    }
    LEAF_PAGE_P10 {
        string keys "501 ~ 700"
        pointer_to_data row_501_to_700 "RID/PK"
    }
    LEAF_PAGE_P11 {
        string keys "701 ~ 1000"
        pointer_to_data row_701_to_1000 "RID/PK"
    }

    %% Data Mapping
    LEAF_PAGE_P11 ||--o{ USERS_TABLE : "Fetch actual data"
    USERS_TABLE {
        UUID user_id PK
        string email
        string nickname
    }

Key Characteristics of B-Tree Structure

  • Root Node: The topmost node that manages the entire key range
  • Branch Nodes: Intermediate nodes that further partition the key space into smaller ranges
  • Leaf Nodes: Bottom-level nodes containing actual data pointers (RIDs or primary keys), linked sequentially for efficient range scans
  • Balanced Tree Property: All leaf nodes are at the same depth, guaranteeing predictable O(log n) search performance

Index Seek Example: Query execution for WHERE id = 150

  1. Start at Root Node → Compare key: "150 falls within range 1~500"
  2. Traverse to Branch1 → Narrow down: "150 falls within range 101~200"
  3. Reach Leaf2 → Retrieve data pointer for key 150
  4. Total of 3 node accesses to locate the target record among tens of thousands of rows

Index Scan

When the database cannot use Index Seek, it falls back to Index Scan.

Index Scan Example: Query execution for WHERE status = 'ACTIVE'

  1. Start at the leftmost Leaf Node (beginning of the linked list)
  2. Read through each leaf node sequentially, checking every entry's status value
  3. Continue traversing via "Next" pointers until reaching the end
  4. Result: All leaf nodes are read, though still faster than a full table scan since index pages are smaller and sequential

Key Differences: Seek vs Scan

Aspect Index Seek Index Scan
Access Pattern Direct jump via B-Tree navigation Sequential read through leaf nodes
Pages Read 3-4 nodes (Root → Branch → Leaf) All leaf nodes in range
Performance O(log n) O(n)
When Used Equality/range on indexed columns Non-selective filters, missing WHERE clause

Required Knowledge 2: How Database Engines Deal with JOIN Commands

Database Engine (i.e., Optimizer) normally uses 3 algorithms when performing JOIN operations. These are Nested Loop Join, Hash Join, and Sort Merge Join.

1. Inner And Outer Tables

Above all, it's crucial to understand the concepts of inner and outer tables. However, we don't necessarily need to dictate their roles when writing SQL, as the Optimizer determines the most efficient execution plan.

To be efficient, before performing JOIN operations, the Optimizer takes some actions.

STEP 1: Selection of the Outer (Driving) Table

  • The Optimizer reduces the target dataset size by applying filtering conditions (via WHERE clauses).
  • The table with the smallest result set after filtering ⇒ Outer (Driving) Table.
  • Purpose : To minimize the total number of iterations required for subsequent join operations.

STEP 2: Selection of the Inner (Driven) Table

  • The Optimizer actively prefers designating a table as the Inner (Driven) Table if it has a usable index on the join key.
    • ⇒ To be efficient via index seek

STEP 3: Algorithm Selection

The Optimizer normally considers 4 elements to select algorithm between Nested Loop Join Hash Join Sort Merge Join.

  • Cardinality & Selectivity: Estimating the number of rows after filtering to decide the join order.
  • Index Presence: Checking if an Index Seek is possible for the driven table.
  • System Resources: Evaluating the available memory for Hash Table creation or Sorting operations.
  • Cost Aggregation: Calculating the sum of Disk I/O and CPU cycles to find the path with the minimum "Total Cost."

2. Nested Loop Join

  • When

    • Cardinality & Selectivity: The outer table has a small result set after filtering (low cardinality), minimizing the number of iterations.

    • Index Presence: The inner table has an index on the join key, enabling fast index seeks for each outer row.

    • System Resources: Limited memory available; no need to build large hash tables or sort datasets.

    • Cost Aggregation

      $$
      \text{Total Cost} = \sum_{i=1}^{n_{\text{outer}}} \left( C_{\text{seek}} + C_{\text{cpu}} \right)
      $$

      $$
      \text{where } C_{\text{seek}} = \log_b(N_{\text{inner}}) \text{ (B-Tree depth)}
      $$

      $$
      \text{Efficient when: } n_{\text{outer}} \times \log(N_{\text{inner}}) < \text{cost}{\text{hash}} + \text{cost}{\text{sort}}
      $$

  • Mechanism

    • Time Complexity: O(n_outer × log(n_inner)) - Number of outer rows × B-Tree search cost

      • It must use index seek
      // Outer Table (Driving Table)
for each row_outer in outer_table:

  // Inner Table (Driven Table) - Index Seek 수행
  matching_rows = index_seek(inner_table, join_key = row_outer.key)

  for each row_inner in matching_rows:
      // 조인 조건이 만족하면 결과에 추가
      if join_condition(row_outer, row_inner):
          result.add(merge(row_outer, row_inner))

3. Hash Join

  • When

    • Cardinality & Selectivity: Both tables are large, and neither can be significantly reduced through filtering.

    • Index Presence: No suitable index exists on the join key, or using the index would be less efficient than sequential scans.

    • System Resources: Sufficient memory is available to build a hash table from the smaller table (or partition).

    • Cost Aggregation

      $$
      \text{Total Cost} = C_{\text{build}} + C_{\text{probe}}
      $$

      $$
      \text{where } C_{\text{build}} = n_{\text{smaller}} \text{ (sequential scan)}
      $$

      $$
      \text{and } C_{\text{probe}} = n_{\text{larger}} \times O(1) \text{ (hash lookup)}
      $$

      $$
      \text{Efficient when: } n_{\text{smaller}} + n_{\text{larger}} < \text{cost}{\text{nested}} + \text{cost}{\text{sort}}
      $$

  • Mechanism

    • Time Complexity: O(n_smaller + n_larger) - Linear scan with hash lookups

      • No index required; uses sequential I/O
      // Phase 1: Build Hash Table from Smaller Table
hash_table = {}
for each row in smaller_table:
  key = row.join_key
  hash_table[key] = row  // O(1) insertion

// Phase 2: Probe Hash Table with Larger Table
for each row_outer in larger_table:
  key = row_outer.join_key

  // Hash lookup - O(1) average case
  if key in hash_table:
      row_inner = hash_table[key]
      if join_condition(row_outer, row_inner):
          result.add(merge(row_outer, row_inner))

4. Sort Merge Join

  • When

    • Cardinality & Selectivity: Both tables are large, but hash join would require too much memory.

    • Index Presence: Data is already sorted (e.g., via clustered index), or the join condition involves range comparisons rather than equality.

    • System Resources: Insufficient memory for hash tables, but disk space is available for sort operations.

    • Cost Aggregation

      $$
      \text{Total Cost} = C_{\text{sort1}} + C_{\text{sort2}} + C_{\text{merge}}
      $$

      $$
      \text{where } C_{\text{sort}} = n \log(n) \text{ (if not pre-sorted)}
      $$

      $$
      \text{and } C_{\text{merge}} = n_{\text{table1}} + n_{\text{table2}} \text{ (linear scan)}
      $$

      $$
      \text{Efficient when: } 2n\log(n) + (n_1 + n_2) < \text{cost}{\text{nested}} + \text{cost}{\text{hash}}
      $$

  • Mechanism

    • Time Complexity: O(n log n + m log m) - Sorting both tables, then O(n + m) for merge

      • Pre-sorted data (via index) eliminates sorting cost
      // Phase 1: Sort Both Tables (if not already sorted via index)
sorted_table1 = sort(table1, join_key)  // O(n log n)
sorted_table2 = sort(table2, join_key)  // O(m log m)

// Phase 2: Merge Sorted Tables
pointer1 = 0
pointer2 = 0

while pointer1 &lt; len(sorted_table1) and pointer2 &lt; len(sorted_table2):
  row1 = sorted_table1[pointer1]
  row2 = sorted_table2[pointer2]

  if row1.join_key == row2.join_key:
      // Match found
      if join_condition(row1, row2):
          result.add(merge(row1, row2))
      pointer1++
      pointer2++
  else if row1.join_key &lt; row2.join_key:
      pointer1++  // Advance smaller key
  else:
      pointer2++  // Advance smaller key

Reason of Gemini3’s Advice

Entity Relationship Diagram

It is my service’s ERD.

erDiagram
    USERS {
        UUID user_id PK "사용자 id"
        VARCHAR(255) email "이메일"
        VARCHAR(255) nickname "닉네임"
        ENUM role "역할"
        ENUM provider "OAuth2 제공자"
        ENUM status "상태(PENDING, ACTIVE)"
        TIMESTAMP created_at "가입일"
        TIMESTAMP updated_at "마지막 업데이트"
    }

    FRIEND_REQUESTS {
        BIGINT id PK "친구 요청 id"
        UUID requester_id FK "신청자 UUID"
        UUID receiver_id FK "수신자 UUID"
        VARCHAR(255) message "요청 메시지"
        TIMESTAMP created_at "신청 일자"
    }

    FRIEND_RELATIONSHIPS {
        BIGINT id PK "확정 관계 id"
        UUID owner_user_id FK "기준 사용자 UUID"
        UUID friend_user_id FK "친구 UUID"
        VARCHAR(255) friend_alias "내가 설정한 친구 이름"
        TIMESTAMP created_at "친구 관계 수립 일시"
    }

    FRIEND_RESTRICTIONS {
        BIGINT id PK "거절/차단 id"
        UUID actor_id FK "차단/거절을 수행한 사용자"
        UUID target_id FK "차단/거절 대상 UUID"
        ENUM type "타입(BLOCK, REJECT)"
        TIMESTAMP created_at "차단/거절 시점"
    }

    USERS ||--o{ FRIEND_REQUESTS : "requests(sent)"
    USERS ||--o{ FRIEND_REQUESTS : "requests(received)"
    USERS ||--o{ FRIEND_RELATIONSHIPS : "friends(owner)"
    USERS ||--o{ FRIEND_RELATIONSHIPS : "friends(friend)"
    USERS ||--o{ FRIEND_RESTRICTIONS : "restrictions(actor)"
    USERS ||--o{ FRIEND_RESTRICTIONS : "restrictions(target)"

In this structure, I needed to find users who could become friends based on the current user's email and the target user's email or nickname.

The conditions for users who could become friends were set as follows:

  • Exclude self
  • No pending friend requests
  • Not currently friends
  • No blocks or rejections

Email based Query’s Problem

select uje1_0.id, uje1_0.created_at, uje1_0.email, uje1_0.nickname, 
       uje1_0.provider, uje1_0.role, uje1_0.status, uje1_0.updated_at 
from users uje1_0 
where (uje1_0.nickname='친구' or uje1_0.email='친구') 
  and uje1_0.status='ACTIVE' 
  and uje1_0.email<>'me@test.com' 
  and uje1_0.email not in (
    select fu1_0.email 
    from friend_relationships frje1_0 
    join users fu1_0 on fu1_0.id=frje1_0.friend_user_id 
    join users ou1_0 on ou1_0.id=frje1_0.owner_user_id 
    where ou1_0.email='me@test.com'
  ) 
  and (uje1_0.email not in (
    select r1_0.email 
    from friend_request frje2_0 
    join users r1_0 on r1_0.id=frje2_0.receiver_id 
    join users r2_0 on r2_0.id=frje2_0.requester_id 
    where r2_0.email='me@test.com'
  ) and uje1_0.email not in (
    select r3_0.email 
    from friend_request frje3_0 
    join users r3_0 on r3_0.id=frje3_0.requester_id 
    join users r4_0 on r4_0.id=frje3_0.receiver_id 
    where r4_0.email='me@test.com'
  )) 
  and (uje1_0.email not in (
    select t1_0.email 
    from friend_restrictions frje4_0 
    join users t1_0 on t1_0.id=frje4_0.target_id 
    join users a1_0 on a1_0.id=frje4_0.actor_id 
    where a1_0.email='me@test.com'
  ) and uje1_0.email not in (
    select a2_0.email 
    from friend_restrictions frje5_0 
    join users a2_0 on a2_0.id=frje5_0.actor_id 
    join users t2_0 on t2_0.id=frje5_0.target_id 
    where t2_0.email='me@test.com'
  )
)

1. Cannot Use Index Seek

  • It maybe use index scan or full table scan. Not Index Seek . Because of email is not a ID.

2. Requires 6 Additional JOINs to Resolve Email Values

  • The email-based query creates 6 times implicit join pattern to resolve email addresses in subqueries.
  • HOWEVER, The ID-based approach eliminates these 6 JOINs entirely by using direct UUID comparisons like WHERE owner_user_id = {constant UUID}
    • The Optimizer can pre-compute and cache subquery results(reduce from WHERE clause, before JOIN operation.)

'ImHere 프로젝트' 카테고리의 다른 글

Jackson 3 변경으로 인한 Redis 역직렬화 장애 분석  (0) 2026.03.19

티스토리툴바