Spring Boot API压测时SQL查询变慢问题排查求助

我正在对基于Spring Boot + Spring Data JPA、连接PostgreSQL数据库的应用做压测。其中一个查询指定时间段样本详情的REST API，单请求响应约500ms，但压测时响应时间大幅上升。

数据查询Repository代码 #

@Query(value = """
        select
          to2.sample_number as sampleNumber,
          ta.profile_id    as fromAgentProfileId,
          ta.full_name     as fromAgentName,
          ta2.profile_id   as toAgentProfileId,
          ta2.full_name    as toAgentName,
          to2.sample_type   as sampleTypeId,
          tp.id            as brandId,
          tp.name          as brandName,
          tod.price        as price,
          to2.created_date as createdDate
        FROM tbl_sample_detail tod
          JOIN tbl_sample to2 ON tod.sample_id = to2.id
          JOIN tbl_user ta ON to2.from_user_id = ta.id
          JOIN tbl_user ta2 ON to2.to_user_id = ta2.id
          JOIN tbl_brand tp on tod.brand_id = tp.id
        WHERE
          (ta.profile_id = :profileId OR ta2.profile_id = :profileId)
          AND (-1 = :userId OR to2.from_user_id = :userId OR to2.to_user_id = :userId)
          AND (-1 IN :sampleTypeList OR to2.sample_type IN :sampleTypeList)
          AND (-1 IN :brandIds OR tod.brand_id IN :brandIds)
          AND (-1 IN :brandCategoryIds OR tp.brand_category_id IN :brandCategoryIds)
          AND to2.created_date between :fromTime and :toTime
        ORDER BY to2.created_date DESC
        """, nativeQuery = true, countQuery = """
        SELECT COUNT(*)
        FROM tbl_sample_detail tod
          JOIN tbl_sample to2 ON tod.sample_id = to2.id
          JOIN tbl_user ta ON to2.from_user_id = ta.id
          JOIN tbl_user ta2 ON to2.to_user_id = ta2.id
          JOIN tbl_brand tp on tod.brand_id = tp.id
        WHERE
          (ta.profile_id = :profileId OR ta2.profile_id = :profileId)
          AND (-1 = :userId OR to2.from_user_id = :userId OR to2.to_user_id = :userId)
          AND (-1 IN :sampleTypeList OR to2.sample_type IN :sampleTypeList)
          AND (-1 IN :brandIds OR tod.brand_id IN :brandIds)
          AND (-1 IN :brandCategoryIds OR tp.brand_category_id IN :brandCategoryIds)
          AND to2.created_date between :fromTime and :toTime
        """)
Page<SampleHistoryView> getSampleDetailHistory(
        @Param("userId") long userId, @Param("profileId") long profileId,
        @Param("sampleTypeList") List<Integer> sampleTypeList, @Param("brandIds") List<Short> brandIds,
        @Param("brandCategoryIds") List<Short> brandCategoryIds, @Param("fromTime") LocalDateTime fromTime,
        @Param("toTime") LocalDateTime toTime, Pageable page);

压测发现的问题 #

压测时通过以下查询发现count语句耗时3-4秒：

SELECT pid,client_addr,(now() - xact_start) AS TIME,state,query FROM  pg_stat_activity WHERE state IN ('active') ORDER BY TIME DESC;

数据库实例为db.t3.xlarge，存储500GB。

执行计划详情 #

Limit  (cost=1.59..431.88 rows=20 width=113) (actual time=83.950..83.993 rows=20 loops=1)
      ->  Nested Loop  (cost=1.59..67427.27 rows=3134 width=113) (actual time=83.949..83.990 rows=20 loops=1)
            ->  Nested Loop  (cost=1.29..67341.70 rows=3134 width=97) (actual time=83.937..83.962 rows=20 loops=1)
                  ->  Nested Loop  (cost=0.87..66607.30 rows=357 width=69) (actual time=83.925..83.936 rows=4 loops=1)
                        Join Filter: ((ta.profile_id = 4) OR (ta2.profile_id = 4))
                        Rows Removed by Join Filter: 34665
                        ->  Nested Loop  (cost=0.58..31828.79 rows=108554 width=56) (actual time=0.028..35.022 rows=34669 loops=1)
                              ->  Index Scan Backward using idx_06_25_tbl_sample_created_date on tbl_sample to2  (cost=0.29..29111.03 rows=108554 width=43) (actual time=0.015..17.248 rows=34669 loops=1)
                                    Index Cond: ((created_date >= '2023-06-17 19:30:12'::timestamp without time zone) AND (created_date <= '2025-07-18 19:30:12'::timestamp without time zone))
                              ->  Memoize  (cost=0.29..0.32 rows=1 width=29) (actual time=0.000..0.000 rows=1 loops=34669)
                                    Cache Key: to2.from_user_id
                                    Cache Mode: logical
                                    Hits: 34664  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                                    ->  Index Scan using tbl_user_pkey on tbl_user ta  (cost=0.28..0.31 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=5)
                                          Index Cond: (id = to2.from_user_id)
                        ->  Index Scan using tbl_user_pkey on tbl_user ta2  (cost=0.28..0.31 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=34669)
                              Index Cond: (id = to2.to_user_id)
                  ->  Index Scan using idx_sample_detail_sample on tbl_sample_detail tod  (cost=0.42..1.73 rows=33 width=44) (actual time=0.004..0.005 rows=5 loops=4)
                        Index Cond: (sample_id = to2.id)
            ->  Memoize  (cost=0.30..0.34 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=20)
                  Cache Key: tod.brand_id
                  Cache Mode: logical
                  Hits: 15  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                  ->  Index Scan using tbl_brand_pkey on tbl_brand tp  (cost=0.29..0.33 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=5)
                        Index Cond: (id = tod.brand_id)
    Planning Time: 1.426 ms
    Execution Time: 84.059 ms

UPDATE：开启track_io_timing = on后的执行计划 #

Limit  (cost=1.59..431.88 rows=20 width=113) (actual time=92.846..92.890 rows=20 loops=1)
  Buffers: shared hit=129868
  ->  Nested Loop  (cost=1.59..67427.27 rows=3134 width=113) (actual time=92.845..92.886 rows=20 loops=1)
        Buffers: shared hit=129868
        ->  Nested Loop  (cost=1.29..67341.70 rows=3134 width=97) (actual time=92.834..92.859 rows=20 loops=1)
              Buffers: shared hit=129853
              ->  Nested Loop  (cost=0.87..66607.30 rows=357 width=69) (actual time=92.821..92.832 rows=4 loops=1)
                    Join Filter: ((ta.profile_id = 4) OR (ta2.profile_id = 4))
                    Rows Removed by Join Filter: 34665
                    Buffers: shared hit=129837
                    ->  Nested Loop  (cost=0.58..31828.79 rows=108554 width=56) (actual time=0.019..38.105 rows=34669 loops=1)
                          Buffers: shared hit=25830
                          ->  Index Scan Backward using idx_06_25_tbl_sample_created_date on tbl_sample to2  (cost=0.29..29111.03 rows=108554 width=43) (actual time=0.009..16.956 rows=34669 loops=1)
                                Index Cond: ((created_date >= '2023-06-17 19:30:12'::timestamp without time zone) AND (created_date <= '2025-07-18 19:30:12'::timestamp without time zone))
                                Buffers: shared hit=25815
                          ->  Memoize  (cost=0.29..0.32 rows=1 width=29) (actual time=0.000..0.000 rows=1 loops=34669)
                                Cache Key: to2.from_user_id
                                Cache Mode: logical
                                Hits: 34664  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                                Buffers: shared hit=15
                                ->  Index Scan using tbl_user_pkey on tbl_user ta  (cost=0.28..0.31 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=5)
                                      Index Cond: (id = to2.from_user_id)
                                      Buffers: shared hit=15
                    ->  Index Scan using tbl_user_pkey on tbl_user ta2  (cost=0.28..0.31 rows=1 width=29) (actual time=0.001..0.001 rows=1 loops=34669)
                          Index Cond: (id = to2.to_user_id)
                          Buffers: shared hit=104007
              ->  Index Scan using idx_sample_detail_sample on tbl_sample_detail tod  (cost=0.42..1.73 rows=33 width=44) (actual time=0.004..0.005 rows=5 loops=4)
                    Index Cond: (sample_id = to2.id)
                    Buffers: shared hit=16
        ->  Memoize  (cost=0.30..0.34 rows=1 width=18) (actual time=0.001..0.001 rows=1 loops=20)
              Cache Key: tod.brand_id
              Cache Mode: logical
              Hits: 15  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
              Buffers: shared hit=15
              ->  Index Scan using tbl_brand_pkey on tbl_brand tp  (cost=0.29..0.33 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=5)
                    Index Cond: (id = tod.brand_id)
                    Buffers: shared hit=15
Planning:
  Buffers: shared hit=86
Planning Time: 1.337 ms
Execution Time: 92.950 ms

SECOND UPDATE：压测时CPU使用率达到100% #

恳请协助排查该问题的原因。

内容的提问来源于stack exchange，提问作者iuhettiarachchi